--查询scott用户下的emp表中工资比scott高的员工的信息(此操作中的子查询只返回一行记录)
select * from emp where sal>(select sal from emp where ename='SCOTT');
2、多行单列子查询（子查询返回多行）
使用特定的关键字如IN，ANY和ALL来将外层查询的单个值与子查询的多行进行比较运算。
（1）子查询出现在where子句中的子查询语法（内嵌子查询）
--查询scott用户下的emp表中所有的经理的信息(此操作子查询会返回多行记录)
select * from emp where empno in ( select mgr from emp);
（2）子查询出现在from子句中（内嵌视图）
--将scott用户下的emp表中查询出的数据作为一个内嵌视图在FROM子句中使用
select * from (select empno,ename,job,sal from emp);
（3）子查询出现在select列表中，此时子查询只能是一个单行子查询。
select (select job from emp where empno=7369) from emp;
（4）子查询也可以出现在having字句中。
select empno,ename, sal,deptno from emp group by deptno,empno,ename,sal
having deptno in ( select deptno from emp where deptno=10 or deptno=20)
order by deptno,sal;
3、多列子查询
1、where之后
成对比较（多列子查询）
select ename,comm,sal form emp where (sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno=30);
非成对比较
select ename,sal,comm from emp where sal in(select sal from emp where deptno=30) and nvl(comm,-1) in (select nvl(comm,-1) from emp where deptno=30)
2、set之后
update monthly_orders
set (tot_orders, max_order_amt, min_order_amt, tot_amt) =
(select count(*), max(sale_price), min(sale_price), sum(sale_price) from cust_order
where order_dt >= TO_DATE('01-JUL-2001','DD-MON-YYYY'))
where month = 7 and year = 2001;
二、在DDL语句中使用子查询
1、create table
通过在create table中使用子查询，可以在建立新表的同时复制表的数据。
CREATE TABLE new_emp(id,name,sal,job,deptno) AS SELEct empno,ename,sal,job,deptno FROM emp;
2、create View
建立视图时，必须指定视图所对应的子查询语句。
CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;
3、create materialized view 建立实体化视图
CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);
三、在DML语句中使用子查询
1.INSERT
INSERT INTO employee (id,name,title,salary)
SELECT emptno,ename,job,sal FROM emp;
2.UPDATE
UPDATE emp SET (sal,comm)=
(SELECT sal,comm FROM emp WHERE ename='SMITH')
WHERE job=(SELECT job FROM emp WHERE ename='SMITH');
3.DELETE
DELECT FROM emp WHERE deptno=
(SELECT deptno FROM dept WHERE dname='SALES');
WITH avg_sal AS (SELECT AVG(salary) val FROM employee)
SELECT e.emp_id, e.lname, e.fname,
(SELECT ROUND(e.salary - val) FROM avg_sal) above_avg
FROM employee e WHERE e.salary > (SELECT val FROM avg_sal);
五、相关子查询。
相关子查询：是指需要引用主查询表列的子查询语句。相关子查询是通过EXISTS谓词来实现的。
SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');
找出工资高于其所在部门平均工资的员工
select enam,sal from emp t1 where t1.sal > (select avg(sal) from emp t2 where t1.Deptno = t2.Deptno)
找出换了二次或二次以上工作的员工：
select last_name from employees e where 2<=
(select count(*) from job_history j where j.employee_id =e.employee_id)
