SQL练习34题

三张表

emp:

dept:

salgrade:

一、每个部门最高薪水的员工名称

第一步：取得每个部门的最高薪水：

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

第二步：将以上查询结果作为临时表t，进行表连接查询：

select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal;

二、哪些人的薪水大于部门平均薪水

第一步：找出部门平均薪水：

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

第二步：表连接：

select e.ename,e.sal,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.deptno=t.deptno and e.sal>t.avgsal;

三、部门中所有员工薪水等级的平均值

第一步：查出所有员工薪水等级

select e.deptno,e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

第二步：在第一步基础上按部门分组求等级的平均值。注意，有group by 时select后面只能跟分组字段和分组函数（count、sum、min、max、avg）。所以可以直接在select后面加avg函数求等级的平均值。

select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

四、不用组函数max求最大薪水（两种方法）

方法一：用limit函数取出部分查询结果

select ename,sal from emp order by sal desc limit 1;

方法二：自连接

select sal from emp where sal ont in (select distinct a.sal from emp a join emp b on a.sal<b.sal);

五、平均薪水最高的部门编号（三种方法）

方法一：order by降序，limit 1取第一个查询结果

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

方法二：max，注意分组函数嵌套max(avg(sal))是错误用法

第一步，找出最高薪水

select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t

第二步，找出薪水等于最高薪水的部门编号

select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

方法三：having max(t.avgsal)，此时t.avgsal是临时表t中的一列，而非分组函数嵌套

select e.deptno,t.avgsal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on e.deptno=t.deptno having max(t.avgsal);

六、平均薪水最高的部门名称

select d.dname,avg(e.sal) as avgsal from emp e join  dept d on e.deptno=d.deptno group by d.dname order by avgsal desc limit 1;

七、平均薪水等级最低的部门名称

注意：可能有好几个部门都是同一个等级，都是最低等级

第一步：找出最低的平均薪水

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

第二步：找出最低平均薪水的薪水等级，因为薪水最低时等级也最低

select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between s.losal and s.hisal

第三步：找出各部门名称和薪水等级，where薪水等级=第二步求的最低薪水等级

select

t*,s.grade

from

(select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno=d.deptno group by d.dname) t

join

salgrade s

on

t.avgsal between s.losal and s.hisal

where

s.grade=(select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) between s.losal and s.hisal);

八、取得比普通员工（员工代码没有在mgr字段出现）的最高薪水还要高的领导人姓名

注意not in 后面不能有null

第一步：找出普通员工的最高薪水

select max(sal) from emp where deptno not in (select distinct mgr from emp where mgr is not null);

第二步：比普通员工最高薪水还要高的，一定是领导人

select ename,sal from emp where sal>(select max(sal) from emp where deptno not in (select distinct mgr from emp where mgr is not null));

九、取得薪水最高的5名员工

select ename,sal from emp order by sal desc limit 5;

十、取得薪水最高的第6到第10名员工

select ename,sal from emp order by sal desc limit 5,5;

十一、取得最后入职的5名员工

注意：日期也可以升序降序排

select ename,hiredate from emp order by hiredate desc limit 5;