数据库（四）

每个部门最高薪水名称

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

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

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

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

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

不用组函数max，取得最高薪水

1.降序 limit1

select sal from emp order by sal desc limit 1;

2.自连接 

select distinct a.sal from emp a join emp b on a.sal <b.sal;

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

select deptno, avg(sal) avgsal from emp 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);

平均薪水最高的部门的部门名称

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

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

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

select t.*, grade from (select d.dname, avg(sal) avgsal from emp e join dept d on e.deptno=d.deptno group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade = (select grade from salgrade where

取得比普通员工的最高薪水还要高的领导人名称

使用not in，小括号要排除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));

薪水最高前五名

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

取得薪水最高的6-10名员工

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

最后入职的五名员工

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

每个薪水等级有多少员工

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

列出所有员工及领导的名字

select a.ename, b.ename from emp a left join emp b on a.mgr = b.empno;