每个部门最高薪水名称
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;
