mysql(动力节点34道作业)

第一题 取得每个部门最高薪水的人员名称

第一步找出每个部门的最高薪水

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

第二步将以上的查询结果作为一张临时表

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 t.deptno = e.deptno and e.sal > avgsal;

第三题 取得部门中（所有人的）平均的薪水等级

第一步取得部门中所有人的薪水等级

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

第二步按照部门编号分组，将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;

第四题 不使用组函数max()，取得最高薪水

方案将sal降序排列

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

第五题 取得平均薪水最高的部门的部门编号

第一步取得每个部门的平均薪水

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

第二步降序取第一个

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

第六题 取得平均薪水最高的部门名称

第一步取得平均水平最高的部门

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

第二步将以上的查询结果作为一张临时表

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

第七题 取得平均薪水最低的部门名称

第一步取得平均水平最低的部门

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

第二步将以上的查询结果作为一张临时表

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

第八题 取得比普通员工的（没有当领导的员工）的最高薪水高的领导姓名

第一步取得普通员工最高工资

select
max(ee.sal) as maxsal
from
emp ee
where ee.empno not in(select distinct mgr from emp where mgr is not null);

第二步找出领导名字

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

第九题 取得薪水最高的前五名员工

select sal
from emp
order by sal desc
limit 5;

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

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

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

select hiredate
from emp
order by hiredate desc
limit 5;

第十二题 取得每个薪水等级有多少员工

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

第十三题 面试题：

有 3 个表 S(学生表)，C（课程表），SC（学生选课表） S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩） 问题： 1，找出没选过“黎明”老师的所有学生姓名。

2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。

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

第十四题 列出所有员工及领导的姓名

select a.ename,ifnull(b.ename,'没有上级') 
from emp a 
left join emp b 
on a.mgr = b.empno

第十五题 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select a.ename,a.empno,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;

第十六题 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

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

第十七题 列出至少有五名员工所在的部门

select d.dname,count(e.ename) as count
from emp e
join dept d 
on e.deptno = d.deptno
group by d.dname
having count >= 5;

第十八题 列出薪水比“SMITH‘ 多的所有员工信息

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

第十九题 列出所有“CLERK”的姓名以及其部门名称，部门人数

select t.ename,t2.*
from(select e.ename,d.dname from emp e join dept d on d.deptno=e.deptno where e.job = 'clerk') t
join(select d.dname,count(e.ename) deptno from emp e join dept d on e.deptno=d.deptno group by a.dname) t2
on t.dname=t2.dname;

第二十题 列出最低薪水大于1500的各种工作及从事此工作的全部雇佣人数

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

第二十一题 列出在部门“SALES”<销售部>工作的员工的姓名，假设不知道部门编号

select ename
from emp e
join dept d
on e.deptno = d.deptno
where d.dname = 'sales';

第二十二题 列出薪水高于公司平均薪水的员工，所在部门上级领导，员工等级

第一步找出薪水高于公司平均薪水的员工

select * 
from emp
where sal > (select avg(sal) from emp);

第二步将上面看作一张临时表

select a.ename,d.dname,e.ename,s.grade
from (select * from emp where sal > (select avg(sal) from emp)) a
join emp e
on a.mgr = e.empno
join dept d
on a.deptno = d.deptno
join salgrade s
on a.sal between losal and hisal;

第二十三题 列出“SCOTT”从事相同工作的所有员工以及部门名称

select e.ename,d.dname
from emp e
join (select * from emp where ename = 'scott') c
on e.job = c.job
join dept d
on c.deptno = d.deptno;

第二十四题 列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水

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

第二十五题 列出薪水高于部门30中员工的薪水的其他员工的姓名和薪水.部门名称

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

第二十六题 列出每个部门的员工数量，平均工资和平均服务期限

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

第二十七题 列出所有的员工姓名、部门名称和工资

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

第二十八题 列出所有部门的详细信息和人数

select e.deptno,count(*),d.loc
from emp e
join dept d
on e.deptno = d.deptno
group by deptno;

第二十九题 列出各种工作的最低工资从事此工作的雇员

第一步各种工作的最低工资

select job, min(sal) as minsal
from emp 
group by job;

第二步将上面看作一张临时表

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

第三十题 列出每个部门的MANAGER（领导）的最低薪水

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

第三十一题 列出所有员工的年工资 案年薪从低到高排序

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

第三十二题 求出员工领导薪水超过3000的员工名称于领导

select a.ename,b.ename
from emp a
join emp b
on a.mgr = b.empno
and b.sal>3000;

第三十三题 求出部门名称中带'S'字符的员工的工资合计、部门人数

select d.dname,sum(e.sal),count(e.ename)
from emp e
right join dept d
on e.deptno=d.deptno
where d.dname like '%S%'
group by d.dname;

第三十四题 给任职超过三十年的员工涨薪10%

select ename ,sal*1.1
from emp
where timestampdiff(Year,hiredate,now())>30;