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