Oracle 学习笔记

求比普通员工的最高薪水还要高的经理人名称:

SQL>
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)
);

求平均薪水最高的部门的部门名称:

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