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
数据库专题 MySQL练习题,接上篇 + 问题(10题)参考答案
最新推荐文章于 2022-10-30 23:31:29 发布