Mysql习题

数据库：bjpowernode.sql

1.找出每个部门的最高薪资的员工

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

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

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

3.取得部门所有人的平均薪水等级

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

4.不用组函数max，取得最高薪水，要求用2中方法

第一种：降序+limit
	select ename, sal from emp order by sal desc limit 1;
第二种：利用表的自连接！
	1.找出比最高工资小的工资：
		select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal；
	2.找出最高工资：
		select ename, sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
	

5.取得平均薪水最高的部门编号，要求2种方法

第一种：降序+limit
	select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
第二种：分组函数max() 难！
	select deptno, avg(sal) avgsal 
	from emp 
	group by deptno 
	having 
		avgsal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t) t;

6.找出平均薪水等级最低的部门名称

第一步：获得各个部门的平均薪水等级
	select d.dname, s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno join salgrade s on t.avgsal between s.losal and s.hisal;
第二步：找出等级最低的
	select tb.dname, tb.grade 
	from 
		(select d.dname, s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno join salgrade s on t.avgsal between s.losal and s.hisal) tb 	//（1中的结果）
		having 
		tb.grade = (select min(m.grade) from 
		(select d.dname, s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join dept d on t.deptno = d.deptno join salgrade s on t.avgsal between s.losal and s.hisal) m);	//（1中的结果）

7. 取得比普通员工（不是别人的领导）最高薪水还要高的领导人姓名

1.找出普通员工的最高薪水
	select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
【注意】not in ()在使用时，小括号里一定要排除null
2.直接求解
	select ename from emp where sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

8.取得薪水最高的第6到第10名员工

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

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

日期也可以排序

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

10.求解每个薪水等级的员工数

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

11.列出所有的员工 以及各自领导的名字

select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
【注意】left不能忘

12.列出雇佣日期早于自己领导的员工编号，姓名及部门名称

select e1.empno, e1.ename, d.dname from emp e1 join emp e2 on e1.mgr = e2.empno and e1.hiredate < e2.hiredate join dept d on e1.deptno = d.deptno;

13.列出部门名称以及部门中的员工，同时列出没有员工的部门

select d.deptno, d.dname, e.ename from emp e right join dept d on e.deptno = d.deptno order by d.deptno;

14.列出至少有5名员工的部门

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

15。列出薪水比‘SMITH’高的员工姓名

方法1：自连接
	select e1.ename, e1.sal from emp e1 join emp e2 on e1.sal > e2.sal and e2.ename = 'smith';
方法2：select嵌套
	select ename, sal from emp where sal > (select sal from emp where ename = 'smith');

16.列出job为‘CLERK’的姓名及其部门名称和部门人数

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

20.列出最低薪资大于1500的工作岗位，以及对应工作的人数

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