企业SQL面试复习与测试
12 对于emp，列出各个部门中工资高于本部门平均水平的员工数和部门号，按部门号排序
select count(a.sal) as 员工数,a.deptno 部门号 from emp a
where a.sal>(select avg(sal) from emp b where a.deptno=b.deptno) group by a.deptno order by a.deptno
- 对于emp中工资高于本部门平均水平，人数多于1人的，列出部门号，高于部门平均工资的人数，按部门号排序
select *
from(
select deptno,count(*) count_num
from emp e
where sal > (
select avg(sal)
from emp e1
where e.deptno = e1.deptno
)
group by deptno
) e1
where e1.count_num > 1
order by e1.deptno
14 对于emp中工资高于本部门平均水平，且其人数多于3人的，列出部门号，部门人数，按部门号排序
select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp a
where (select count(c.empno) from emp c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp b where c.deptno=b.deptno))>3
group by a.deptno order by a.deptno
(法二)
select m.deptno,count(ee1.empno)
from(
select e1.deptno,count(empno) count_num
from emp e1
where e1.sal >
(select avg(sal) from emp e2 where e1.deptno = e2.deptno)
group by e1.deptno
) m,emp ee1
where m.count_num > 3 and m.deptno = ee1.deptno
group by m.deptno
15 对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as 人数 from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>5
