oracle子查詢嵌套查詢

1.建立表：建立請自己去看以下網址

http://www.cnblogs.com/xwdreamer/archive/2012/06/05/2537170.html

2.要求：查詢出工資比SMITH還要高的所有員工資訊

select * from EMP where sal>(select sal from emp where ename='SMITH');

3.要求：查出高于公司平均工資的全部員工資訊

select * from EMP where sal>(select avg(sal) from emp);

**************************************************************************************************************************

3.IN操作符：用于指定一個子查詢的判斷範圍

select * from EMP where sal in(select sal from emp where job='MANAGER');

*****************************************************************************************************************************************

4.ANY操作符：與第一個内容相比對，有三種比對形式;

1.=ANY   -----> 這時和in語句作用一樣

2.<ANY   ------》小于最大值

3.>ANY   ------》大于最小值

(1).select * from emp where sal=ANY(select sal from emp where job='MANAGER');

(2)select * from emp where sal<ANY(select sal from emp where job='MANAGER');

(3)select * from emp where sal>ANY(select sal from emp where job='MANAGER');

************************************************************************************************************************************************************

4.ALL操作符：與第一個内容相比對，有兩種比對形式;

1. >ALL   ---------->大于最大值

2. <ALL   ----------->小于最小值

(1)select * from emp where sal>ALL(select sal from emp where job='MANAGER');

(2)select * from emp where sal<ALL(select sal from emp where job='MANAGER');