Database file
https://download.csdn.net/download/yougcave/18897401
Database screenshot
emp employee form
dept Department
salgrade wage scale
1. Name of the person who gets the highest salary in each department
The first step is to find out the maximum salary of each department
select deptno, max(sal) maxsal from emp group by deptno
The second step is to find the data equal to deptno and maxsal in the emp employee table and the above temporary table
select e.ename,e.deptno,e.sal from emp e join (select deptno, max(sal) maxsal from emp group by deptno)t on e.deptno = t.deptno and e.sal = t.maxsal
2. Who is paid above the average salary of the Department
The first step is to find out the average salary
select deptno, avg(sal) avgsal from emp group by deptno;
The second step is to use the above table as a temporary table and connect it with the employee table emp. Just check the data that the Department is the same and the salary is greater than avgsal
SELECT e.ename,e.sal,e.deptno from emp e JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON e.deptno = t.deptno AND e.sal>t.avgsal
3. Get the average salary grade (of everyone) in the Department
The first step is to find out everyone's salary grade
select e.ename,e.deptno,s.grade from emp e JOIN salgrade s ON e.sal>=s.losal and e.sal<=s.hisal
The second step is to take the table found in the first step as a temporary table to calculate the average level in groups
select t.deptno, avg(t.grade) FROM (select e.ename,e.deptno,s.grade from emp e JOIN salgrade s ON e.sal>=s.losal and e.sal<=s.hisal)t group by t.deptno
4. Do not use group function (Max) to get the highest salary
Let's see how max works first
select max(sal) from emp
It's done in one sentence
Instead of max, the first value is sorted by sal from large to small
select sal from emp order by sal desc limit 1
5. Department number of the Department with the highest average salary
The first step is to calculate the average salary according to the idea of question 4, and then sort and take the first data
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1
The second step is to take out deptno from the small table above and finish it
select t.deptno FROM (select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1)t
6. Department name of the Department with the highest average salary
The first step, like the first step in question 5, is to find the Department with the highest average salary
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
The second step is to connect with dept department table and check the Department name with the same department number as the condition
select d.dname FROM dept d JOIN (select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1)t on t.deptno=d.deptno
7. The Department name of the Department with the lowest level of average salary
The first step is to calculate the average salary
select deptno, avg(sal) from emp group by deptno
Step 2: connect the salary grade table salgrade to check the grade
select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal
There are two levels with the same level. Although we are looking for the lowest level 3, there is only one, but we can't rule out the situation of the same level
The third step is to find the lowest level in the above table
select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal order by s.grade limit 1
In step 4, there may be tables of the same level, so the tables in step 3 and step 2 should be connected and queried
select t.deptno FROM (select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal)t JOIN (select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal order by s.grade limit 1)t1 ON t.grade = t1.grade and t.avgsal=t1.avgsal
The principle is very simple, the code is very long, and the data to be found has not been found
The fifth step is to connect with dept and find the name
The basic framework is like this
select d.dname from dept d JOIN ()t ON d.deptno=t.deptno
Copy the code of step 4 into the temporary table
select d.dname from dept d JOIN (select t.deptno FROM (select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal)t JOIN (select s.grade,t.deptno,t.avgsal from salgrade s JOIN (select deptno, avg(sal) avgsal from emp group by deptno)t ON t.avgsal<=hisal and t.avgsal>=losal order by s.grade limit 1)t1 ON t.grade = t1.grade and t.avgsal=t1.avgsal)t ON d.deptno=t.deptno;
This code is completely unreadable
8. Get a leader's name that is higher than the maximum salary of an ordinary employee (the employee code does not appear in the mgr field)