ORACLE循序渐进讲解（第二讲） - 数据库编程

AME JOB SAL DEPTNO ---------- --------- --------- ------ CLARK MANAGER 2450.00 10 BLAKE MANAGER 2850.00 30 JONES MANAGER 2975.00 20 KING PRESIDENT 5000.00 10 MILLER CLERK 1300.00 10 JAMES CLERK 950.00 30 ADAMS CLERK 1100.00 20 SMITH CLERK 1800.00 20
9、显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号。

ENAME            SAL DEPTNO
---------- --------- ------
JONES        2975.00     20
SCOTT        3000.00     20
KING         5000.00     10
FORD         3000.00     20
或者
SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
ENAME            SAL DEPTNO
---------- --------- ------
JONES        2975.00     20
SCOTT        3000.00     20
KING         5000.00     10
FORD         3000.00     20

10、查询与SMITH部门和岗位完全相同的所有雇员

SQL> select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
ENAME
----------
SMITH
ADAMS

11、查询高于自己部门的平均工资的员工信息。

SQL> select ename,sal,svg "部门平均工资" from emp,(select deptno,avg(sal) svg from emp group by deptno) t where sal>svg and emp.deptno=t.deptno;
ENAME            SAL       部门平均工资
---------- --------- ------------
ALLEN        1600.00 1566.6666666
JONES        2975.00         2375
BLAKE        2850.00 1566.6666666
SCOTT        3000.00         2375
KING         5000.00 2916.6666666
FORD         3000.00         2375
6 rows selected

12、修改员工scott的岗位、工资、补助与smith员工一样。

SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
1 row updated

13、oracle分页。

SQL> select * from emp where empno in(select empno from (select empno,rownum rn from (select empno from emp order by sal desc) where rownum<=10) t where t.rn>=6);
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10
 7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30
 7934 MILLER     CLERK      7782 1982-01-23    1300.00     34.56     10
 7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30
 7369 SMITH      CLERK      7902 1980-12-17    1800.00               20

14、用查询结果创建新表

SQL> create table emp1(id,name) as select empno,ename from emp;
Table created
Executed in 0.532 seconds

SQL> select * from emp1;
   ID NAME
----- ----------
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
14 rows selected

15、合并查询。（union、union all、intersect、minus）（1）union 取两个结果集的并集，会自动去掉结果集中重复行。

SQL> select empno id,ename name from emp
  2  union
  3  select id,name from emp1;
   ID NAME
----- ----------
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
14 rows selected

（2）union all 取两个结果集的并集，不会去掉结果集中重复行。

SQL>  select empno id,ename name from emp
  2   union all
  3   select id,name from emp1
  4  ;
   ID NAME
----- ----------
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
 7369 SMITH
 7499 ALLEN
 7521 WARD
 7566 JONES
 7654 MARTIN
 7698 BLAKE
   ID NAME
----- ----------
 7782 CLARK
 7788 SCOTT
 7839 KING
 7844 TURNER
 7876 ADAMS
 7900 JAMES
 7902 FORD
 7934 MILLER
28 rows selected

（3）intersect 取两个结果的交集。

SQL>  select ename,sal from emp where sal>2500
  2    intersect
  3    select ename,sal from emp where job='MANAGER';
ENAME            SAL
---------- ---------
BLAKE        2850.00
JONES        2975.00

（4）minus 取两个结果的相

ORACLE循序渐进讲解（第二讲）(二)