(先看外部表，先选取外部表的第一行，用它的列与内部表的条件进行相连查询)
1.查询比所在职位平均工资高的员工姓名，职位
select ename,job
from emp b
where sal>(select avg(sal) from emp where EMP.DEPTNO
= b.DEPTNO)
2.查询每个部门工资最低/最高的两个员工姓名，编号，工资
select empno,ename,sal,DEPTNO
from EMP outerTab
where 1 >= (select count(*) from emp where EMP.sal < outerTab.sal and outerTab.DEPTNO = EMP.DEPTNO )
order by deptno,sal
select OUTERTAB.ename,OUTERTAB.sal,OUTERTAB.DEPTNO
FROM EMP outerTab -- 这个部门中工资第一高或者第二高的员工
where 1>= (select count(*) from emp where EMP.sal > outerTab.sal )
order by deptno,sal
3.查询哪些员工是经理
select *
from emp e
where (select count(empno) from emp p where p.mgr=e.EMPNO)>0
select ename from emp b
where b.empno in
(select mgr from emp where EMP.mgr = b.empno)（相关子查询）
select ename from emp where empno in (select mgr from emp )（嵌套子查询）
select ename
from emp e
where exists(select 1 from emp p where p.mgr=e.empno)
4.查询哪些员工不是经理
select *
from emp e
where (select count(empno) from emp p where p.mgr=e.EMPNO)=0
select ename from emp b
where b.empno not in
(select mgr from emp where EMP.mgr = b.empno)（相关子查询）
select ename from emp where empno not in (select nvl(mgr,100) from emp )（嵌套子查询）
select ename
from emp e
where not exists(select 1 from emp p where p.mgr=e.empno)
exsists和not exsists操作符
1.列出至少有一个雇员的所有部门名称
select dname
from dept s
where exists (select 1 from emp where EMP.DEPTNO=s.DEPTNO)
2.列出没有雇员的所有部门名称
select dname
from dept s
where not exists (select 1 from emp where EMP.DEPTNO=s.DEPTNO)
