动力节点mysql课后练习34题

数据库文件

https://download.csdn.net/download/yougcave/18897401

数据库截图

emp 员工表

dept 部门表

salgrade 工资等级表

 

1、取得每个部门最高薪水的人员名称

第一步，先查出每个部门的最高工资是多少

select deptno, max(sal) maxsal from emp group by deptno

第二步，再找emp员工表和上面这个临时表里deptno和maxsal相等的数据

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、哪些人的薪水在部门的平均薪水之上

第一步，先把平均薪水查出来

select deptno, avg(sal) avgsal from emp group by deptno;

第二步，把上面这个表当临时表，和员工表emp连接，查部门相同，薪水大于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、取得部门中（所有人的）平均的薪水等级

第一步，先查出所有人的薪水等级

select e.ename,e.deptno,s.grade from emp e
JOIN
salgrade s
ON
e.sal>=s.losal and e.sal<=s.hisal

第二步，把第一步查出来的表当成临时表来分组计算平均等级

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、不准用组函数（Max），取得最高薪水

先看看max怎么用

select max(sal) from emp

一句就搞定了

不用max，就按sal从大到小排序取第一个值

select sal from emp order by sal desc limit 1

5、取得平均薪水最高的部门的部门编号

第一步，按第四题的思想，先算平均薪水，然后排序取第一个数据

select deptno,avg(sal) avgsal from emp 
group by deptno
order by avgsal desc
limit 1

第二步，在上面的小表里取出deptno就完事

select t.deptno
FROM
(select deptno,avg(sal) avgsal from emp 
group by deptno
order by avgsal desc
limit 1)t

6、取得平均薪水最高的部门的部门名称

第一步，和第五题的第一步一样，找出平均薪水最高的部门

select deptno,avg(sal) avgsal from emp 
group by deptno
order by avgsal desc
limit 1;

第二步，和dept部门表连接，用部门编号相同当条件，查部门名称

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、求平均薪水的等级最低的部门的部门名称

第一步，先算平均薪水

select deptno, avg(sal) from emp group by deptno