数据库专题 MySQL练习题，接上篇 + 问题(10题)参考答案

1.取出每个部门薪水最高的员工的名称
select ename from emp e,(select MAX(sal)max,deptno from emp group by deptno)
 zgbu where e.sal=zgbu.max and e.deptno = zgbu.deptno

2.哪些人的薪水在各自部门平均薪水之上
select ename sal from emp,(select AVG(SAL)avg1,DEPTNO from emp GROUP BY DEPTNO
) avbu where emp.sal>avbu.avg1 and emp.DEPTNO=avbu.deptno

3.不准使用MAX，求出最高薪水
select SAL from emp order by sal DESC limit 1

4.获得平均薪水最高的部门的部门编号
select deptno from (select AVG(sal) as avg1,deptno from emp group by deptno) as bum order by bum.avg1 desc limit 1

5.取得平均薪水最高的部门的部门名称
select dname from dept,(select deptno from emp group by deptno order by AVG(sal) DESC limit 1)
 dno where dept.DEPTNO = dno.deptno

6.获取每个薪水等级对应多少员工
select grade,COUNT(dengji.GRADE) from (select grade from salgrade,emp where emp.sal between salgrade.losal and salgrade.HISAL) dengji GROUP BY dengji.GRADE


7.列出至少有5个员工的部门
select * from (select deptno,COUNT(deptno) rs from emp group by DEPTNO) 
bm where bm.rs>=5

8.列出所有 “CLERK”（办事员）的姓名，部门名称，部门人数
select ename,dname from emp e inner join dept d ON e.deptno=d.DEPTNO where e.job="clerk"
select deptno,COUNT(*) from emp e group by e.DEPTNO

9.列出薪资最低大于1500的各种工作，以及从事此工作雇员的全部人数
select e.JOB,MIN(SAL)minsal,COUNT(*)  from emp e GROUP BY JOB HAVING minsal>1500

10.列出各种工作最低工资，以及对应雇员的姓名
select e.ENAME,b1.job,b1.minsal	from emp e,(select JOB,MIN(SAL)minsal  from emp e group by job
) b1 where e.job=b1.job and b1.minsal=e.sal