练习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)
课后作业
- 查询薪水多于他所在部门平均薪水的雇员名字，部门号。
select e.ename, e.deptno
from emp e
where e.sal>
(select avg(sal)
from emp
where deptno=e.deptno
group by deptno)
- 查询员工姓名和直接上级的名字。
select e.ename, (select ename
from emp
where empno=e.mgr)
from emp e
- 查询每个部门工资最高的员工姓名，工资。
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 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
