1 求经理人平均薪水最低的部门名称: 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_
1 求经理人平均薪水最低的部门名称:
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)))
2 比普通员工的最高薪水还高的经理人名称
select ename from empwhere 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))
其中(select distinct mgr from emp where mgr is not null) 注意空值的处理.
3 求薪水最高的前五雇员
* 关于隐藏属性 rownum :
rownum 从1开始,每行递增 . 如查询表中的前五人:
select ename from emp where rownum < = 5;
注意oracle 中 rownum 不可直接与> , = 连用, 如:select ename from emp where rownum >10;
当需要查找r>10时必须使用子查询:
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;
4 求薪水最高的第六个人到第十个人
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 from
(select ename,sal ,rownum r from emp order by sal desc)
where r >=6 and r <=10 这样写呢?
因为 (select ename,sal ,rownum r from emp order by sal desc) 这句话先取了emp的 rownum 然后再按sal 倒排, 无法达到取 sal 6到10名的效果.
【专题推荐】：2020年oracle面试题汇总（最新）