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 取两个结果的相