高级子查询

练习1.1 查询比所在职位平均工资高的员工姓名，职位。

select ename, job
from emp e
where sal>
(select avg(sal) 
from emp 
where e.job=job);

1.2 查询工资为其部门最低工资的员工编号，姓名，工资。

select empno, ename, sal 
from emp outer
where sal in 
(select min(sal)
from emp 
where outer.deptno=deptno);

练习2.1 查询所有雇员编号，名字和部门名字。

select empno, ename, (select dname
from dept 
where e.deptno=deptno)
from emp e

2.2 查询哪些员工是经理。

select e.ename
from emp e
where e.empno in (select mgr 
from emp 
where e.empno=mgr) 

2.3 查询哪些员工不是经理。

select e.ename 
from emp e
where e.empno not in(select mgr 
from emp 
where e.empno=mgr)

2.4 查询每个部门工资最低的两个员工编号，姓名，工资。

select empno, ename, sal 
from emp 
where sal in (select min(sal)
from 
(select ename, sal, deptno
from emp 
where (ename,sal) not in
(select e.ename, e.sal 
from emp e
where e.sal in(select min(sal)
from emp 
where deptno=e.deptno 
group by deptno))) b
group by b.deptno)

union 

select e.empno, e.ename, e.sal 
from emp e
where e.sal in(select min(sal)
from emp 
where deptno=e.deptno 
group by deptno);

select e.empno, e.ename, e.sal 
from emp e
where (select count(*)
from emp 
where deptno=e.deptno and sal<e.sal)<2

练习3.1 （exists）列出至少有一个雇员的所有部门名称。

select d.dname
from dept d
where exists(select count(*)
from emp 
where deptno=d.deptno 
group by deptno 
having count(*)>=1)

练习3.2 列出一个雇员都没有的所有部门名称。

select d.dname 
from dept d 
where not exists(select count(*)
from emp 
where deptno=d.deptno 
group by deptno 
having count(*)>=1)

课后作业

1. 查询薪水多于他所在部门平均薪水的雇员名字，部门号。

select e.ename, e.deptno
from emp e
where e.sal>
(select avg(sal)
from emp 
where deptno=e.deptno 
group by deptno)

2. 查询员工姓名和直接上级的名字。

select e.ename, (select ename 
from emp 
where empno=e.mgr)
from emp e

3. 查询每个部门工资最高的员工姓名，工资。

select e.ename, e.sal 
from emp e
where e.sal in (select max(sal)
from emp 
where deptno=e.deptno 
group by deptno )

4. 查询每个部门工资前两名高的员工姓名，工资。

• 结果多出一个

select ename, sal 
from emp 
where sal in (select max(sal)
from 
(select ename, sal, deptno
from emp 
where (ename,sal) not in
(select e.ename, e.sal 
from emp e
where e.sal in(select max(sal)
from emp 
where deptno=e.deptno 
group by deptno))) b
group by b.deptno)

union 

select e.ename, e.sal 
from emp e
where e.sal in(select max(sal)
from emp 
where deptno=e.deptno 
group by deptno);

select e.ename, e.sal, e.deptno
from emp e
where (select count(*)
from emp 
where deptno=e.deptno and sal>e.sal)<2