oracle 面试题下 (七) rownum的运用

select dname from dept where deptno =
(
select deptno from
(
select deptno,avg(sal)avg_sal from
(
select e1.sal, e1.deptno,e2.mgr from emp e1 join emp e2 on (e1.empno=e2.mgr)
) group by deptno
) where avg_sal =
(
select min(avg_sal) from
(
select deptno,avg(sal)avg_sal from
(
select e1.sal, e1.deptno,e2.mgr from emp e1 join emp e2 on (e1.empno=e2.mgr)
) group by deptno
)
)
)

select ename from emp
where empno in (select distinct mgr from emp where mgr is not null) and sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
)

* 关于隐藏属性 rownum :

select ename from emp where rownum < = 5;

select ename from (select ename ,rownum r from emp)where r >10;

select ename ,sal from
( select ename ,sal from emp order by sal desc )
where rownum<=5;

select ename,sal from
(
select ename ,sal ,rownum r from
(
select ename ,sal from emp order by sal desc
)
)where r>=6 and r <=10

(select ename,sal ,rownum r from emp order by sal desc)