MySQL经典33题，DQL语句硬核专项练习！！！
话不多说，直接导入三张表： 员工表：em
部门表：dept
薪资等级表：salgrade
以下DQL语句是基于上面三张表进行的。
题目- 1、取得每个部门最高薪水的人员名称
- 2、哪些人的薪水在部门的平均薪水之上
- 3、取得部门中（所有人的）平均的薪水等级
- 4、不准用组函数（Max），取得最高薪水
- 5、取得平均薪水最高的部门的部门编号
- 6、取得平均薪水最高的部门的部门名称
- 7、求平均薪水的等级最低的部门的部门名称
- 8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的 最高薪水还要高的领导人姓名
- 9、取得薪水最高的前五名员工
- 10、取得薪水最高的第六到第十名员工
- 11、取得最后入职的 5 名员工
- 12、取得每个薪水等级有多少员工
- 13、列出所有员工及领导的姓名
- 14、列出受雇日期早于其直接上级的所有员工的编号,姓名, 部门名称
- 15、列出部门名称和这些部门的员工信息,同时列出那些没有 员工的部门.
- 16、列出至少有 5 个员工的所有部门
- 17、列出薪金比"SMITH"多的所有员工信息.
- 18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门 的人数.
- 19、列出最低薪金大于 1500 的各种工作及从事此工作的全 部雇员人数.
- 20、列出在部门"SALES"<销售部>工作的员工的姓名,假定 不知道销售部的部门编号.
- 21、列出薪金高于公司平均薪金的所有员工,所在部门,上级 领导,雇员的工资等级.
- 22、列出与"SCOTT"从事相同工作的所有员工及部门名称.
- 23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名 和薪金.部门名称
- 24、列出薪金高于在部门 30 工作的所有员工的薪金的员工 姓名和薪金.部门名称.
- 25、列出在每个部门工作的员工数量,平均工资和平均服务期 限.
- 26、列出所有员工的姓名、部门名称和工资。
- 27、列出所有部门的详细信息和人数
- 28、列出各种工作的最低工资及从事此工作的雇员姓名
- 29、列出各个部门的 MANAGER(领导)的最低薪金
- 30、列出所有员工的年工资,按年薪从低到高排序
- 31、求出员工领导的薪水超过 3000 的员工名称与领导名称
- 32、求出部门名称中,带'S'字符的部门员工的工资合计、部门 人数.
- 33、给任职日期超过 30 年的员工加薪 10%.
1、取得每个部门最高薪水的人员名称
思路： 第一步：取得每个部门最高薪水（部门分组，求薪水最大值）
第二步：将第一步的表当作临时表与emp表做连接 连接条件e.deptno=t.deptno and e.sal=t.maxsal
2、哪些人的薪水在部门的平均薪水之上
思路： 第一步：找出部门的平均薪资
第二步：找出高于平均薪资的人
3、取得部门中（所有人的）平均的薪水等级
tip:
平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。 平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。
思路： 第一步：找出每个人的薪水等级
第二步：找出薪水等级的平均值
4、不准用组函数（Max），取得最高薪水
思路： 将薪水降序排列，用limit取第一个数
5、取得平均薪水最高的部门的部门编号
思路： 第一步：取得每个部门的平均薪水
第二步：找出以上结果中avgsal最大的值。
第三步：找出部门编号
6、取得平均薪水最高的部门的部门名称
方案一思路： 第一步：取得每个部门的平均薪水
第二步：取平均薪水最高的部门
第三步：找出部门名称
方案二：
7、求平均薪水的等级最低的部门的部门名称
tip:
平均薪水最低的对应的等级一定是最低的
思路： 第一步：平均薪水最低的
第二步：找出对应的等级
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的 最高薪水还要高的领导人姓名
思路： 第一步：“普通员工的最高薪水”还要高的一定是领导！找出领导
第二步：不在上面的都是普通员工
第三步：找出高于1600的
9、取得薪水最高的前五名员工
思路： 将薪水降序排列limit取前五
10、取得薪水最高的第六到第十名员工
思路： 将薪水降序排列limit取6到10名
11、取得最后入职的 5 名员工
思路： 将入职日期降序排，取前五
12、取得每个薪水等级有多少员工
思路： 第一步：找出每个员工的薪水等级
第二步：进行分组
13、列出所有员工及领导的姓名
tip:
员工的领导编号等于领导的员工编号
14、列出受雇日期早于其直接上级的所有员工的编号,姓名, 部门名称
思路： 将员工表看成一个员工表 a,领导表 b
15、列出部门名称和这些部门的员工信息,同时列出那些没有 员工的部门.
思路： 员工表和部门表进行外连接（此处使用右外连接）
16、列出至少有 5 个员工的所有部门
17、列出薪金比"SMITH"多的所有员工信息.
18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门 的人数.
思路： 第一步：找出job是CLERK的所有的人名和部门名称:
第二步：每个部门的人数
第三步：将上边两个表做连接：
19、列出最低薪金大于 1500 的各种工作及从事此工作的全 部雇员人数.
20、列出在部门"SALES"<销售部>工作的员工的姓名,假定 不知道销售部的部门编号.
思路： 第一步找出销售部门编号：
第二步：知道销售部的部门编号找出员工姓名：
21、列出薪金高于公司平均薪金的所有员工,所在部门,上级 领导,雇员的工资等级.
select a.ename '姓名',d.dname '部门', b.ename '上级领导',s.grade '工资等级'
from emp a
join dept d
on a.deptno=d.deptno and a.sal>(select avg(sal)
from emp)
left join emp b
on a.mgr=b.empno
join salgrade s
on a.sal between s.losal and hisal;
22、列出与"SCOTT"从事相同工作的所有员工及部门名称.
select e.ename,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.ename<>'SCOTT';
23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名 和薪金.部门名称
思路： 第一步：列出部门30的员工薪资
select distinct sal,deptno from emp where deptno=30;
第二步：找出薪资等于上表的员工信息
select e.sal,e.ename,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where e.sal in (select distinct sal from emp where deptno=30) and e.deptno<> 30;
24、列出薪金高于在部门 30 工作的所有员工的薪金的员工 姓名和薪金.部门名称.
select e.sal,e.ename,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where e.sal > (select max(sal) from emp where deptno=30) and e.deptno<> 30;
25、列出在每个部门工作的员工数量,平均工资和平均服务期 限.
tip:
在mysql当中怎么计算两个日期的“年差”，差了多少年？ TimeStampDiff(间隔类型, 前一个日期, 后一个日期) timestampdiff(YEAR, hiredate, now()) 间隔类型： SECOND 秒， MINUTE 分钟， HOUR 小时， DAY 天， WEEK 星期 MONTH 月， QUARTER 季度， YEAR 年
select d.dname,d.deptno,count(e.ename) ecount,ifnull(avg(e.sal),0) avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())),0) as avgservicetime
from emp e
join dept d
on e.deptno=d.deptno
group by d.deptno;
26、列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno=d.deptno;
27、列出所有部门的详细信息和人数
select d.deptno,d.dname,d.loc,count(e.ename)
from emp e
right join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc;
28、列出各种工作的最低工资及从事此工作的雇员姓名
思路： 第一步：按工作分组，找出各种工作的最低工资
select min(sal),job
from emp
group by job;
第二步：找出从事工作的雇员信息
select e.*
from emp e
join (select min(sal) minsal,job from emp group by job) t
on e.job=t.job and e.sal=t.minsal;
29、列出各个部门的 MANAGER(领导)的最低薪金
select job,deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;
30、列出所有员工的年工资,按年薪从低到高排序
select ename,(sal+ifnull(comm,0)) *12 incone
from emp
order by incone asc;
31、求出员工领导的薪水超过 3000 的员工名称与领导名称
select a.ename '员工',b.ename '领导'
from emp a
join emp b
on a.mgr=b.empno
where b.sal>3000;
32、求出部门名称中,带’S’字符的部门员工的工资合计、部门 人数.
select d.deptno,d.dname,d.loc,ifnull(sum(e.sal),0),count(e.ename)
from dept d
left join emp e
on d.deptno=e.deptno
where dname like'%S%'
group by d.deptno,d.dname,d.loc;
33、给任职日期超过 30 年的员工加薪 10%.
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;加薪之前：
加薪之后：
一起加油，一起努力，一起秃见成效
腾讯云开发者
