第六章:子查询
1.比black工资高的雇员有哪些?(where 子句中带有查询)
select ename
from emp
where sal>(select sal from emp where ename='BLAKE');
不使用表连接，打印出相同的结果集！(select子句中带有查询)
select ename,loc
from emp
natural join dept;
select ename,
case deptno
when 10 then (select loc from dept where deptno=10)
when 20 then (select loc from dept where deptno=20)
when 30 then (select loc from dept where deptno=30)
else (select loc from dept where deptno=40) end location
from emp;
高于30部门最高工资的雇员有哪些?
select ename,sal
from emp
where sal>(select max(sal) from emp where deptno=30);
select ename,sal
from emp
where sal > all (select sal from emp where deptno=30);
大于10部门最小工资的雇员有哪些?
select ename,sal
from emp
where sal> (select min(sal) from emp where deptno=10);
select ename,sal
from emp
where sal > any (select sal from emp where deptno=10);
工资最高的人是谁?
select ename from emp
where sal=(select max(sal) from emp);
和ALLEN同部门，工资高于MARTIN的雇员有哪些?
select ename from emp
where
deptno=(select deptno from emp where ename='ALLEN')
and
sal>(select sal from emp where ename='MARTIN');
工作和部门与SMITH相同，工资高于JAMES的雇员有哪些?
select ename from emp
where (job,deptno)=(select job,deptno from emp where ename='SMITH')
and sal>(select sal from emp where ename='JAMES');
工资高于本部门平均工资的人（拿上游工资的人）有哪些？
select ename,sal,avgsal,e.deptno
from emp e,
(select avg(sal) avgsal,deptno
from emp
group by deptno) b
where e.deptno=b.deptno
and e.sal>b.avgsal;
工资前五名的人?(TOP-N 分析)
select ename,sal
from emp
where sal in
(select sal
from (select distinct sal from emp order by sal desc)
where rownum<6)
order by sal desc;
工资6~10的人?
select ename,sal from emp
where sal in
(select sal from
(select rownum rn,sal
from (select distinct sal
from emp order by sal desc))
where rn between 6 and 10)
order by sal desc;
