MySQL34道作业（有难度，坚持）

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

select ename, sal, deptno from emp 
where sal in (select max(sal) from emp group by deptno);

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

select a.ename, a.sal from emp a 
join (select deptno, avg(sal) avg_sal from emp group by deptno) b 
on a.deptno = b.deptno 
where a.sal > b.avg_sal;

3、取得部门中（所有人的）平均的薪水等级，如下：

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

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

方案1：

select sal from emp order by sal desc limit 1;

方案2：

select sal from emp where sal >= all(select sal from emp);

5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

方案1：

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

方案2：

select t2.deptno 
    from (select deptno, avg(sal) avgsal from emp group by deptno) t2 
    where t2.avgsal = (select max(t1.avgsal) maxavgsal from (select deptno, avg(sal) avgsal from emp group by deptno) t1) ;

6、取得平均薪水最高的部门的部门名称

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

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

select d.dname from dept d 
    join (select t.deptno, t.avgsal, s.grade from salgrade s join (select deptno, avg(sal) avgsal from emp group by deptno) t on t.avgsal between s.losal and s.hisal order by avgsal limit 1) g
    on d.deptno = g.deptno;

8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

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));

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

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

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

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

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

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

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

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

13、面试题

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

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

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

select b.empno, b.ename ename_e, d.dname from emp a join emp b on b.mgr = a.empno and b.hiredate < a.hiredate join dept d on b.deptno = d.deptno;

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

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

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

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

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

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

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

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

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

 select job, count(empno) from emp where sal > 1500 group by job;

21、列出在部门"SALES"工作的员工的姓名,假定不知道销售部的部门编号.

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

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

select b.ename ename,  d.dname dname, ifnull(a.ename, 'no exists') mgrname, s.grade from emp a 
    right join emp b on b.mgr = a.empno 
    join dept d on d.deptno = b.deptno 
    join salgrade s on b.sal between losal and hisal
where b.sal > (select avg(e.sal) from emp e);

23、列出与"SCOTT"从事相同工作的所有员工及部门名称.

select e.ename, d.dname from emp e 
join dept d on e.deptno = d.deptno
where e.job = (select e.job from emp e where e.ename = 'SCOTT') and e.ename != 'SCOTT';

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

select e.ename, e.sal from emp e
where e.sal in (select e.sal from emp e where e.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.deptno != 30 and e.sal > (select e.sal from emp e where e.deptno = 30 order by e.sal desc limit 1); 

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

select d.dname, count(e.empno), ifnull(avg(e.sal), 0), ifnull(avg(TimeStampDiff(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.*, count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.deptno;

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

select * from emp e where e.sal in (select min(e.sal) from emp e group by e.job);

30、列出各个部门的 MANAGER(领导)的最低薪金。

select a.deptno, min(a.sal) from emp a join emp b on b.mgr = a.empno group by a.deptno;

31、列出所有员工的年工资,按年薪从低到高排序。

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

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

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

33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数。

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

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

update emp set sal = (sal + sal * 0.1) where TimeStampDiff(year, hiredate, now()) > 30;