oracle 连接练习部分答案

9.select * from
(select grade,deptno from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between salgrade.losal and salgrade.hisal) m
join dept on dept.deptno = m.deptno
where m.grade =
(
  select min(grade) from
  (select deptno,avg(sal) avg_sal from emp group by deptno) t
  join salgrade on avg_sal between salgrade.losal and salgrade.hisal
)
10.
select * from emp where emp.empno in (select distinct(e1.mgr) from emp e1,emp e2 where e1.mgr=e2.empno) and emp.sal>
(
select max(sal) from emp where emp.empno not in
(select distinct(e1.mgr) from emp e1,emp e2 where e1.mgr=e2.empno))
12.
select ename,sal from
(     
       select ename,sal,rownum r from
              (select ename,sal from emp order by sal desc)
) where r>5 and r<11  
=======分页利用3个select嵌套实现,是oracle中效率最高的分页=======