MySQL 练习题

MySQL 练习题

题目来源“老杜入门MySQL学习：https://www.bilibili.com/video/BV1Vy4y1z7EX?p=132

建立三张表

emp 员工表

salgarde 工资等级表

dept 部门表

1. 取得每个部门最高薪水的人员名称

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

2. 哪些人的薪水在部门的平均薪水之上

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

3. 取得部门中（所有人的）平均的薪水等级（工资由高到低1~5等级：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 1
select 
 e.ename,
 sal
from 
 emp e
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 
 e.deptno,
 avg(sal) avgsal
from 
 emp e
group by deptno order by avgsal desc limit 1;

方案二：
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 
 d.dname,
 e.avg(sal) as avgsal 
from emp e
join dept d
on e.deptno = d.deptno
group by d.dname 
order by
avgsal desc
limit 1; 

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

思路：
1.找出每个部门的平均薪水
2.找出每个部门的平均薪水的等级

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) avgsal from emp group by deptno order by avgsal asc limit 1) 
     between losal and hisal
 );

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

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

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

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

10. 取得薪水最高的第六到第十名员工

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

11. 取得最后入职的5名员工

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

12. 取得每个薪水等级有多少员工

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

13. 有三个表：S(学生表)、C(课程表)、SC(学生选课表)

S(SNO，SNAME)代表（学号，姓名）

C(CUO，CNAME，CTEACHER) 代表（课号，课名，教师）

SC(SNO ，CNO，SCGRADE) 代表 (学号，课号，成绩)

1.找出没选过“黎明”老师的所有学生姓名
  select 
   S.SNAME
  from S 
  join SC on S.SNO = SC.SNO 
  join C on SC.CNO = C.CUO
  where C.CTEACHER <> '黎明';

2.列出2门以上（含2门）不及格学生姓名及平均成绩
  select 
  S.SNAME,
  avg(SC.SCGRADE)
  from S
  join SC on S.SNO = SC.SNO
  where 
  SC.SCGRADE < 60 
  having
  count(SC.CNO) >= 2;

3.即学过1号课程又学过2号课所有学生的姓名

  select 
  S.SNAME
  from S
  join SC on S.SNO = SC.SNO
  where
  SC.CNO = '1' and SC.CNO = '2';

14. 列出所有员工及领导的姓名

select 
 a.ename '员工',
 b.ename '领导'
from 
 emp a
left join emp b
on a.mgr = b.empno;

15. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

select 
 a.ename '员工',
 a.hirediate,
 b.ename '领导'，
 b.hirediate,
 d.dname
from 
 emp a
join emp b
on a.mgr = b.empno
join deptno
on a.deptno = d.deptno
where 
 a.hirediate < b.hiredate;

16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select 
 e.*,
 d.dname
from 
 emp e
right join dept d
on e.deptno = d.deptno;

17. 列出至少5个员工的所有部门

select 
 e.deptno
from 
 emp e
group by e.deptno
having count(*) >= 5;

18. 列出薪金比“SMITH”多的所有员工信息

select 
 e.*
from 
 emp e
where sal > (select sal from emp where ename = 'SMITH');

19. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数

select 
 t1.*,
 t2.deptcount 
from 
(select e.ename,e.job,d.dname from emp e join deptno d on e.deptno = e.deptno where e.job = 'CLERK') t1
join (select deptno,count(*) as deptcount from emp group by deptno) t2
on t1.deptno = t2.deptno;

20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select 
 job，
count(*)
from 
 emp e
 group by job having min(sal) > 1500;

21. 列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号

select 
 ename 
from
 emp 
where
 deptno = (select deptno from dept where dname = 'SALES');

22. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级

select 
 e.ename '员工'，
 d.dname,
 l.ename '领导'，
 s.grage
from
 emp e 
join dept d
on e.deptno = 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);

23. 列出与“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 where ename = 'SCOTT')
and
 e.ename <> 'SCOTT';

24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

select 
 ename,
 sal
from 
 emp e
where sal in (select distinct sal from emp where deptno = 30)
and 
deptno <> 30;

25. 列出薪金高于在部门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);

26. 列出在每个部门工作的员工数量，平均工资和平均服务期限

 
select 
 d.deptno,
 count(e.ename),
 ifnull(avg(e.sal),0),
 ifnull(avg(timesStamDiff(YEAR,hiredate,now())),0)
from 
 emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno;

27. 列出所有员工的姓名、部门名称和工资

select 
 e.ename,
 d.dname,
 e.sal
from 
 emp e
join dept d
on e.deptno = d.deptno;

28. 列出所有部门的详细信息和人数

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;

29. 列出各种工作的最低工资及从事此工作的雇员姓名

select 
 e.ename,
 t.*
from 
 emp e
join (select job,min(sal) minsal from emp group by job) t
on e.job = t.job and e.sal = t.minsal;

30. 列出各个部门的“MANAGER”(领导)的最低薪金

select 
 deptno,
 min(sal)
from 
 emp e
where 
 job = 'MANAGER'
 group by
 deptno;

31. 列出所有员工的年工资，按年薪从低到高排序

select 
 ename,
 (sal + ifnull(comm,0) * 12) as yearsal
from 
 emp e
order by yearsal asc;

32. 求出员工领导的薪水超过3000的员工名称与领导名称

select 
 a.ename '员工',
 b.ename '领导'
from 
 emp a
join emp b
on a.mgr = b.empno
where
b.sal > 3000;

33. 求出部门名称中，带‘S’字符的部门员工的工资合计，部门人数

select 
 d.*,
 count(e.ename),
 sum(sal),
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;

34. 给任职日期超过30年的员工加薪10%

update emp 
set sal = sal * 1.1 
where 
timestampdiff(YEAR,hiredate,now()) > 30;
 

低到高排序

select 
 ename,
 (sal + ifnull(comm,0) * 12) as yearsal
from 
 emp e
order by yearsal asc;

32. 求出员工领导的薪水超过3000的员工名称与领导名称

select 
 a.ename '员工',
 b.ename '领导'
from 
 emp a
join emp b
on a.mgr = b.empno
where
b.sal > 3000;

33. 求出部门名称中，带‘S’字符的部门员工的工资合计，部门人数

select 
 d.*,
 count(e.ename),
 sum(sal),
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;

34. 给任职日期超过30年的员工加薪10%

update emp 
set sal = sal * 1.1 
where 
timestampdiff(YEAR,hiredate,now()) > 30;