前言：以下试题中涉及的 table 均来自博主前面发的随笔“”
1.取得每个部门最高薪水的人员名称 先取出每个部门的最高薪水，再作为临时表与（对应最高薪水的人员名称表）连接 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
2. 哪些人的薪水在部门的平均薪水之上 先取出每个部门的平均薪水，再作为临时表与（薪水在其部门平均薪水上的部门名称，薪水表）连接 select t.*,e.ename,e.sal 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;
3.取得部门中所有人的平均薪水等级 找到每个人的薪水等级（emp连接salgrade） select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; 基于以上结果继续按照deptno分组，求grade的平均值（直接两张表就行，不需要临时表） 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;
4.不准用组函数（Max），取得最高薪水（给出两种解决方案） 第一种，降序（limit） select ename,sal from emp order by sal desc limit 1; 第二种方案，表的自连接 select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
5.取出平均薪水最高的两个部门编号（至少给出两种解决方案） 第一种方案： select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
第二种解决方案： 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);
6.求平均薪水的等级最低的部门的部门名称 找出最低平均薪水对应的等级 select grade from salgrade where (select avg(sal) as avgsal,deptno from emp order by avgsal asc limit 1) between losal and hisal; 找出（等于最低平均薪水的等级对应的）部门名称，平均薪水，等级 select t.*,s.grade from (select d.dname,avg(sal) as avgsal from emp e join dept 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,deptno from emp order by avgsal asc limit 1) between losal and hisal);
7.取出比普通员工（员工代码没有在mgr字段出现的）的最高薪水还要高的领导人姓名 注意：not in 在使用时，后面小括号里记得排除 null 找到普通员工的最高薪水 select max(sal) from emp where empno 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 empno not in(select distinct mgr from emp where mgr is not null));
8.取出薪水最高的前五名 select ename,sal from emp order by sal desc limit 5;
9.取出薪水最高的第六到第十的员工 select ename,sal from emp order by sal desc limit 5,5;
10.取出最后入职的5名员工; select ename,hiredate from emp order by hiredate desc limit 5;
11.取出每个薪水等级有多少个员工 select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
12.列出所有员工及领导的名字 select a.ename '员工',b.ename '领导' from emp a left join emp b on a.mgr = b.empno;
13.列出受雇日期早于直接上级的所有员工的姓名，受雇日期，直接上级的姓名，受雇日期,部门名称 select a.empno '员工',a.hiredate,b.ename '领导',b.hire date,d.dname from emp a join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno where a.hiredate < b.hiredate;
14.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 select e.*,d.dname from
emp e right join dept d on e.deptno = d.deptno;
15.列出至少有5个员工的所有部门 select deptno from emp group by deptno having count(*) >= 5;
16.列出薪水比“SMITH”多的所有员工 select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
17.列出最低薪水大于1500的各种工作及其从事此工作的全部雇员人数 select job,count(*) from emp group by job having min(sal) > 1500;
18.列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号 select ename from emp where deptno = (select deptno from dept where dname = 'SALES');
19.列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级 select e.ename '员工',d.dname,l.ename '领导',s.grade from emp e join dept d on e.empno = d.deptno left join emp l on e.mgr = l.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) from emp);
20.列出与“SCOTT”从事相同工作的所有员工及其部门名称 select e.ename,e.job,d.dname from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename = 'SCOTT') and e.name <> 'SCOTT';
21.列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水 select ename,sal from emp where sal in(select distinct sal from emp where deptno = 30) and deptno <> 30;
22.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水，部门名称 select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d,deptno where e.sal > (select max(sal) from emp where deptno = 30) ;
23.列出在每个部门工作的员工数量，平均工资和平均服务期限 select d.deptno, count(e.ename) as ecount, ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime from emp e right join dept d on e.deptno = d.deptno group by d.deptno;
计算两个时间间隔的函数，语法为：
timestampdiff(间隔类型,前一个日期,后一个日期) 返回日期间的整数差。 FRAC_SECOND 表示间隔是毫秒 SECOND 秒 MINUTE 分钟 HOUR 小时 DAY 天 WEEK 星期 MonTH 月 QUARTER 季度 YEAR 年
24.列出所有员工的姓名，部门名称，和薪水 select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno;
25.列出所有部门的详细信息和人数 select d.deptno,d.dname,d.loc,count(e.ename) from emp e join dept d on e.deptno = d.deptno group by d.deptno,d.name,d.loc;
26.列出各种工作的最低工资及从事此工作的雇员姓名 select e.ename,t.* from emp e join (select job,min(sal) as minsal from emp group by job)t on e.job = t.job and e.sal = t.,minsal;
27.列出各个部门MANAGER（领导）的最低薪水 select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
28.列出所有员工的年工资，按年薪从低到高排序 select ename,(sal + ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
29.求出员工领导薪水超过3000的员工名称和领导 select a.ename '员工',b.ename '领导' from emp a join emp b on a.mgr = b.empno where b.sal > 3000;
30.求出部门名称带’S‘的部门员工的工资合计，部门人数 select d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno,d.name,d.loc;
31.给任职日期超过30年的员工加薪 10% update emp set sal= sal*1.1 where timestampdiff(YEAR,hiredate,now())>30;
试题出处：https://www.bilibili.com/video/BV1fx411X7BD?p=1