練習(xí)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);
練習(xí)2.1 查詢所有雇員編號硼控，名字和部門名字。
select empno, ename, (select dname
from dept
where e.deptno=deptno)
from emp e
2.2 查詢哪些員工是經(jīng)理狐血。
select e.ename
from emp e
where e.empno in (select mgr
from emp
where e.empno=mgr)
2.3 查詢哪些員工不是經(jīng)理淀歇。
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
練習(xí)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)
練習(xí)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)
課后作業(yè)
- 查詢薪水多于他所在部門平均薪水的雇員名字，部門號碰逸。
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 )
- 查詢每個部門工資前兩名高的員工姓名满钟，工資。
- 結(jié)果多出一個
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
