oracle数据库 高级子查询练习

例题：
–1.查询比本部门平均工资高的员工编号，姓名，薪资，部门编号
M1：
select empno, ename, deptno, sal
from emp
outer where sal > (select avg(sal) from emp where deptno = outer.deptno);
M2：
select empno, ename, e.deptno, sal, avgsal
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
where e.deptno = b.deptno
and e.sal > avgsal;
–2.查询所有部门名称和人数
M1：
select d.dname 部门名称, count(e.empno) 部门人数
from emp e, dept d
where d.deptno = e.deptno(+)–关联查询，把没有人数的部门也显示出来
group by d.deptno, d.dname;
M2：
select dname, (select count(empno) from emp where deptno = d.deptno)
from dept d;
–3.查询哪些员工是经理?
M1:
select * from emp where empno in (select mgr from emp);
M2:
select *
from emp e
where 0 < (select count(empno) from emp where e.empno = mgr);
-练习1
–1.查询比所在职位平均工资高的员工姓名，职位。
–注意是部门相同还是工作相同
–方法1
select ename, job
from emp
outer where sal > any (select avg(sal) from emp where job = outer.job);
–方法2
select ename, job
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
where e.deptno = b.deptno
and e.sal > b.avgsal;
– 2.查询工资为其部门最低工资的员工编号，姓名，工资。
–方法1：相关子查询
select deptno, ename, sal
from emp
outer where sal = (select min(sal)
from emp
where deptno = outer.deptno
group by deptno);
–方法2
select deptno, ename, sal
from emp
where sal in (select min(sal) from emp group by deptno);
–练习2
–1.查询所有雇员编号，名字和部门名字。
select empno,ename,dname from emp e,dept d
where e.deptno=d.deptno;
–2.查询哪些员工是经理？
select ename from emp
where empno in(select mgr from emp);

select m.ename from emp m
where 0<(select count(empno) from emp where m.empno=mgr);

–3.查询哪些员工不是经理？
–4.查询每个部门工资最低的两个员工编号，姓名，工资。
select deptno,ename,sal
from emp e
where(select count(*)
from emp m
where e.deptno=m.deptno
and m.sal<e.sal)<2
order by deptno,sal;

–练习3
–如下练习，用exists或not exists完成
–1.列出至少有一个雇员的所有部门名称。
select dname
from dept d
where exists (select ‘1’
from emp
where deptno = d.deptno
group by deptno
having count(empno) > 0);

–2.列出一个雇员都没有的所有部门名称。
select dname
from dept d
where not exists (select ‘1’
from emp
where deptno = d.deptno
group by deptno
having count(empno) > 0);
–课后练习
–1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。
select ename, deptno
from emp m
where sal >
(select avg(sal) from emp where deptno = m.deptno group by deptno);

select ename,e.deptno,avgsal from emp e,
(select deptno,avg(sal) avgsal from emp group by deptno)b
where e.deptno=b.deptno and e.sal>avgsal;
–2.查询员工姓名和直接上级的名字。
select e.ename 员工姓名, m.ename 上级姓名
from emp e, emp m
where e.mgr = m.empno;
–3.查询每个部门工资最高的员工姓名，工资。
select ename,sal from emp
where sal in(select max(sal) from emp group by deptno);

select ename,sal from emp m
where sal=(select max(sal) from emp where deptno=m.deptno);
–4.查询每个部门工资前两名高的员工姓名，工资。
–解题思路：只有一个工资比自己工资水平高
select deptno,ename,sal
from emp e
where(select count(*)
from emp m
where e.deptno=m.deptno
and m.sal>e.sal)<2
order by deptno,sal;