实验说明：编辑脚本文件5.sql，放在F:\目录下，并用Start F:\5.sql执行。输出结果在F:\exp5.txt中。
注：如果重复使用Start F:\5.sql命令，则在使用前分别用drop view account;drop view research;drop view sales命令删除已创建的视图。
实验结果：
SQL> set linesize 120
SQL> set echo on
SQL> conn / as sysdba
已连接。
SQL> alter user scott account unlock identified by aaa;
用户已更改。
SQL> GRANT create any view TO SCOTT;
授权成功。
SQL> conn scott/aaa
已连接。
SQL> CREATE VIEW account AS select ename,sal,job from emp where deptno=10;
视图已创建。
SQL> CREATE VIEW research AS select ename,sal,job from emp where deptno=20;
视图已创建。
SQL> CREATE VIEW sales AS select ename,sal,job from emp where deptno=30;
视图已创建。
SQL> conn system/aaa
已连接。
SQL> GRANT select on scott.account to scott;
授权成功。
SQL> GRANT select on scott.research to scott;
授权成功。
SQL> GRANT select on scott.sales to scott;
授权成功。
SQL> conn scott/aaa
已连接。
SQL> select worker.ename 员工名,manager.ename 上司名
2 from emp worker,emp manager
3 where worker.mgr=manager.empno;
员工名 上司名
---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
员工名 上司名
---------- ----------
FORD JONES
MILLER CLARK
已选择13行。
SQL>
SQL>
SQL> select ename,sal from account where sal>2000
2 union
3 select ename,sal from research where sal>2000
4 union
5 select ename,sal from sales where sal>2000;
ENAME SAL
---------- ----------
BLAKE 2850
CLARK 2450
FORD 3000
JONES 2975
KING 5000
SCOTT 3000
已选择6行。
SQL>
SQL>
SQL> select distinct job from account
2 union
3 select distinct job from research
4 union
5 select distinct job from sales;
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
SQL>
SQL>
SQL> select * from account
2 union
3 select * from research where job='CLERK';
ENAME SAL JOB
---------- ---------- ---------
ADAMS 1100 CLERK
CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK
SMITH 800 CLERK
SQL>
SQL>
SQL> select ename,sal from account where sal>2000
2 union all
3 select ename,sal from research where sal>2000
4 union all
5 select ename,sal from sales where sal>2000;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
JONES 2975
SCOTT 3000
FORD 3000
BLAKE 2850
已选择6行。
SQL>
SQL>
SQL> select distinct job from account
2 union all
3 select distinct job from research
4 union all
5 select distinct job from sales;
JOB
---------
CLERK
PRESIDENT
MANAGER
CLERK
MANAGER
ANALYST
SALESMAN
CLERK
MANAGER
已选择9行。
SQL>
SQL>
SQL> select job from account
2 intersect
3 select job from research
4 intersect
5 select job from sales;
JOB
---------
CLERK
MANAGER
SQL>
SQL>
SQL> select job from account
2 minus
3 select job from sales;
JOB
---------
PRESIDENT
SQL>
SQL>
SQL> select * from research
2 minus
3 select * from account
4 minus
5 select * from sales;
ENAME SAL JOB
---------- ---------- ---------
ADAMS 1100 CLERK
FORD 3000 ANALYST
JONES 2975 MANAGER
SCOTT 3000 ANALYST
SMITH 800 CLERK
SQL>
SQL>
SQL> select sal from emp where ename='JONES';
SAL
----------
2975
SQL> select ename,job,sal from emp where sal>2975;
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
SQL> select ename,job,sal from emp
2 where sal>(select sal from emp where ename='JONES');
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
SQL>
SQL>
SQL> select deptno,dname from dept
2 where deptno in(select deptno from emp where sal>=3000);
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
SQL>
SQL>
SQL>
SQL> select ename,sal,job from emp
2 where deptno not in (select deptno from emp
3 where sal < 1000);
ENAME SAL JOB
---------- ---------- ---------
CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK
SQL>
SQL>
SQL> select ename,sal,job from emp
2 where deptno !=all (select deptno from emp
3 where sal < 1000);
ENAME SAL JOB
---------- ---------- ---------
CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK
SQL>
SQL>
SQL> select deptno,ename,sal from emp e
2 where sal=(select max(sal) from emp where deptno=e.deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
SQL>
SQL>
SQL> select * from emp where (to_char(hiredate,'yy'),deptno) in
2 (select to_char(hiredate,'yy'),deptno from emp where sal>=3000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
已选择6行。
SQL>
SQL>
SQL> select e.deptno,a.average,e.ename,e.sal 工资,e.sal-a.average 差值
2 from emp e,(select deptno,avg(sal) average from emp
3 group by deptno) a
4 where e.deptno=a.deptno
5 order by 1,2;
DEPTNO AVERAGE ENAME 工资 差值
---------- ---------- ---------- ---------- ----------
10 2916.66667 CLARK 2450 -466.66667
10 2916.66667 KING 5000 2083.33333
10 2916.66667 MILLER 1300 -1616.6667
20 2175 JONES 2975 800
20 2175 FORD 3000 825
20 2175 ADAMS 1100 -1075
20 2175 SMITH 800 -1375
20 2175 SCOTT 3000 825
30 1566.66667 WARD 1250 -316.66667
30 1566.66667 TURNER 1500 -66.666667
30 1566.66667 ALLEN 1600 33.3333333
DEPTNO AVERAGE ENAME 工资 差值
---------- ---------- ---------- ---------- ----------
30 1566.66667 JAMES 950 -616.66667
30 1566.66667 BLAKE 2850 1283.33333
30 1566.66667 MARTIN 1250 -316.66667
已选择14行。
SQL>
SQL>
SQL> select deptno,avg(sal) from emp group by deptno
2 having avg(sal)>(select avg(sal) from emp where deptno=30);
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
SQL>
SQL>
SQL> select job,avg(sal) from emp group by job
2 having avg(sal)=(select min(avg(sal)) from emp group by job);
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
SQL>
SQL>
SQL> select ename,job,sal from emp,dept where loc='NEW YORK'
2 and emp.deptno=dept.deptno and sal>(select sal from emp
3 where ename='SCOTT');
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
SQL> spool off
实验脚本（5.sql文件）：
spool F:\exp5.txt
set linesize 120
set echo on
conn / as sysdba
alter user scott account unlock identified by aaa;
GRANT create any view TO SCOTT;
conn scott/aaa
CREATE VIEW account AS select ename,sal,job from emp where deptno=10;
CREATE VIEW research AS select ename,sal,job from emp where deptno=20;
CREATE VIEW sales AS select ename,sal,job from emp where deptno=30;
conn system/aaa
GRANT select on scott.account to scott;
GRANT select on scott.research to scott;
GRANT select on scott.sales to scott;
conn scott/aaa
select worker.ename 员工名,manager.ename 上司名
from emp worker,emp manager
where worker.mgr=manager.empno;
select ename,sal from account where sal>2000
union
select ename,sal from research where sal>2000
union
select ename,sal from sales where sal>2000;
select distinct job from account
union
select distinct job from research
union
select distinct job from sales;
select * from account
union
select * from research where job='CLERK';
select ename,sal from account where sal>2000
union all
select ename,sal from research where sal>2000
union all
select ename,sal from sales where sal>2000;
select distinct job from account
union all
select distinct job from research
union all
select distinct job from sales;
select job from account
intersect
select job from research
intersect
select job from sales;
select job from account
minus
select job from sales;
select * from research
minus
select * from account
minus
select * from sales;
select sal from emp where ename='JONES';
select ename,job,sal from emp where sal>2975;
select ename,job,sal from emp
where sal>(select sal from emp where ename='JONES');
select deptno,dname from dept
where deptno in(select deptno from emp where sal>=3000);
select ename,sal,job from emp
where deptno not in (select deptno from emp
where sal < 1000);
select ename,sal,job from emp
where deptno !=all (select deptno from emp
where sal < 1000);
select deptno,ename,sal from emp e
where sal=(select max(sal) from emp where deptno=e.deptno);
select * from emp where (to_char(hiredate,'yy'),deptno) in
(select to_char(hiredate,'yy'),deptno from emp where sal>=3000);
select e.deptno,a.average,e.ename,e.sal 工资,e.sal-a.average 差值
from emp e,(select deptno,avg(sal) average from emp
group by deptno) a
where e.deptno=a.deptno
order by 1,2;
select deptno,avg(sal) from emp group by deptno
having avg(sal)>(select avg(sal) from emp where deptno=30);
select job,avg(sal) from emp group by job
having avg(sal)=(select min(avg(sal)) from emp group by job);
select ename,job,sal from emp,dept where loc='NEW YORK'
and emp.deptno=dept.deptno and sal>(select sal from emp
where ename='SCOTT');
spool off
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/500789/viewspace-709119/ ，如需转载，请注明出处，否则将追究法律责任。
