# Power node mysql after class exercise 34 questions

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)

Keywords: MySQL

Added by ggseven on Wed, 09 Feb 2022 13:17:53 +0200