求比普通员工的最高薪水还要高的经理人名称:
SQL>select ename from emp where empno in(select distinct mgr from empwhere 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 dname from dept where deptno=( select deptno from ( select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal= ( select max(avg_sal) from ( select avg(sal) avg_sal,deptno from emp group by deptno ) ))
Oracle数据库中的rownum 字段的特别之处:只能用小于号或者是小于或等于号:
SQL> select empno,ename from emp where rownum < 10; EMPNO ENAME---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 已选择9行。 SQL> select empno,ename from emp where rownum >10; 未选定行 SQL> select empno,ename from emp where rownum =10; 未选定行求薪水最高的前5名雇员: SQL> select ename,sal from(select ename,sal from emp order by sal desc) 2 where rownum <= 5;
order by sal desc : 是按薪水的从大到小排列.
order by sal asc :默认的排序顺序为升序ASC
相关资源：Oracle学习笔记(rownum和rowid)
