--单行子查询 =号来查询
--显示与SMITH同一部门的所有员工
select * from emp e where e.deptno=(select deptno from emp p where p.ename='SMITH');
--多行子查询 in 来查询
--显示与10号部门 job 职位相同的雇员信息
select * from emp e where e.job in(select distinct p.job from emp p where p.deptno=10);
--比30部门员工工资都高的员工信息max,all
select * from emp e where e.sal>(select max(sal) from emp p where p.deptno=30);
select * from emp e where e.sal>all(select sal from emp p where p.deptno=30);
--查询比30部门员工任意工资高的雇员信息min、any
select * from emp e where e.sal>(select min(sal) from emp p where p.deptno=30);
select * from emp e where e.sal>any(select sal from emp p where p.deptno=30);
--多列子查询
--查询与SMITH同一部门同一岗位的员工
--方法一
select * from emp e where e.deptno=(select deptno from emp where ename='SMITH') and e.job=(select job from emp where ename='SMITH');
--方法二(deptno,job)=(select deptno,job from emp where ename='SMITH');
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
--在from 子句中使用子查询
--显示高于自己部门平均工资的员工信息
--方法一
--把（select avg(sal) deptno from emp group by deptno当作一个临时表）
select e.deptno,e.ename,e.deptno,avgs from emp e,(select avg(sal) avgs,deptno from emp group by deptno) p where e.deptno=p.deptno and e.sal>avgs;
--方法二（缺陷 不能查询平均工资）
select * from emp e where e.sal>(select avg(sal) from emp p where p.deptno=e.deptno);
--查找每个部门工资最高的人的详细信息
SQL> select * from emp e,(select max(sal) maxs,deptno from emp group by deptno) p where e.deptno=p.deptno and e.sal=maxs;
--显示每个部门的信息（编号、名称、）和人员数量
select d.deptno,d.dname,counts from dept d,(select count(*) as counts,deptno from emp group by deptno) t where d.deptno=t.deptno(+);
