MySQL基础练习题：习题7-11

这部分主要是为了帮助大家回忆回忆MySQL的基本语法，数据库来自于MySQL的官方简化版，题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。

求平均薪水的等级最高的部门的部门名称

Solution：

先求各部门平均薪水的等级，再求最高等级，最后显示平均薪水等级等于最高等级的部门名称

Operation：

1

select a.deptno,a.avgsal,b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

2

+-------+
| grade |
+-------+
|     4 |
+-------+

mysql> select d.dname,c.avgsal,c.grade
from
	(select a.deptno,a.avgsal,b.grade
     	from
     		(select deptno,avg(sal) as avgsal from emp group by deptno) a
     	join
     		salgrade b
     	on
     		avgsal between losal and hisal) c
join
	dept d
on
	c.deptno=d.deptno and c.grade=
		(select b.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) a join salgrade b on avgsal between losal and hisal order by b.grade desc limit 1);
+-------------+-------------+-------+
| dname       | av