(先看外部表，先选取外部表的第一行，用它的列与内部表的条件进行相连查询)

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)