【MySQL】试题 — 31道巩固 SQL 语句的练习题

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

1. 哪些人的薪水在部门的平均薪水之上
   先拿出每个部门的平均工资，然后连接到(部门平均工资上的部门名称，薪级)作为临时表。
   [En]

   First take out the average salary of each department, and then connect it to (the name of the department on the average salary of the department, salary scale) as a temporary table.

   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

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;

ps：博主少写了3道哟！ ( •̀ ω •́ )✧

Original: https://www.cnblogs.com/Burning-youth/p/15685482.html
Author: 猿头猿脑的王狗蛋
Title: 【MySQL】试题 — 31道巩固 SQL 语句的练习题