7、求平均薪水的等级最低的部门的部门名称。

注意：平均薪水最低的等级肯定最低，平均薪水不是最低的也有可能等级最低。

第一步：分组查询每个部门编号、平均薪水

select deptno,avg(sal) as avgSal from emp group by deptNo

第二步：在第一步的基础上，联合薪资等级表salgrade，查询出每个部门平均薪水等级

select deptno,avgSal,grade from salgrade join (select deptno,avg(sal) as avgSal from emp group by deptNo) t on avgSal between loSal and hiSal

第三步：在第二步的基础上，查出最低薪资等级 3

select min(grade)as minGrade from salgrade join (select deptno,avg(sal) as avgSal from emp group by deptNo) t on avgSal between loSal and hiSal

第四步：在第三步基础上，查询哪些部门平均薪资的等级是最低等级

select t.deptno,avgSal,grade,dname from salgrade join (select deptno,avg(sal) as avgSal from emp group by deptNo) t on avgSal between loSal and hiSal join dept on dept.deptNo=t.deptno join (select min(grade)as minGrade from salgrade join (select deptno,avg(sal) as avgSal from emp group by deptNo) t on avgSal between loSal and hiSal) m on grade=m.minGrade

取得比普通员工（员工编号没有在mgr上出现的）的最高薪水还要高的经理人姓名

第1步：查询出现在mgr上的所有员工编号

select distinct mgr from emp where mgr is not null

第2步：在“第1步”查询的结果集的基础上，查询没有出现在mgr上的所有员工信息

select * from emp where empno not in(select distinct mgr from emp where mgr is not null)

第3步：在“第2步”查询的结果集的基础上，查询出普通员工的最高薪水

select sal from emp where empno not in(select distinct mgr from emp where mgr is not null) order by sal desc limit 1

或者

select max(sal) as maxSal from emp where empno not in(select distinct mgr from emp where mgr is not null)

第4步：在“第3步”的查询结果基础上，筛选出最终结果

select * from emp where sal>(select max(sal) as maxSal from emp where empno not in(select distinct mgr from emp where mgr is not null))

取得薪水最高的前五名员工

select * from emp order by sal desc limit 5