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)