SQL 子查询练习

--子查询练习
/*  
    1、子查询（内查询）在主查询（外查询）之前一次执行完成
    2、子查询的结果被主查询使用
    3、在查询列表中使用子查询，只能是单行单列。见练习2
    4、除非进行TOP N 分析，否则不要在子查询中使用ORDER BY语句，
       因子查询效率较低，排序耗费资源。见练习3
*/

--练习1：查询所有工资大于CLARK的员工信息
--把子查询结果当做一个值使用
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'CLARK')

--练习2：查询所有员工信息以及工资总和
--把子查询结果当做一个列使用
SELECT emp.*,(SELECT SUM(sal) FROM emp) 工资总和
FROM emp;

--练习3：查询工资最高TOP 5
--把子查询结果当做一个表使用
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;

--练习4：查询员工表中第6到第12条数据
--利用子查询解决ROWNUM不能计算大于等于某个值的问题
SELECT * FROM (SELECT ROWNUM rnum,emp.* FROM emp)
WHERE rnum BETWEEN 6 AND 12;

      --推荐写法（提前过滤）
      SELECT * FROM (SELECT ROWNUM rnum, emp.* FROM emp WHERE ROWNUM<=12)
      WHERE rnum >=6;
      
--多行子查询
--练习1：查询所有不是部门经理的员工
--IN 的使用
SELECT * FROM emp e
WHERE e.empno NOT IN (SELECT manager_id FROM DEPT
WHERE manager_id IS NOT NULL)

--练习2：查询所有员工人数不少于3人的部门信息
--子查询中可以使用子句
SELECT * FROM dept
WHERE deptno IN (SELECT deptno FROM emp 
                 GROUP BY deptno
                 HAVING COUNT(*)>=3 )
                 
--相关子查询（内外交互式）
--练习：查询员工编号，姓名，部门编号，工资，本部门工资总和
SELECT empno,ename,deptno,sal,
                 (SELECT SUM(sal) FROM emp 
                  WHERE e.deptno = deptno) 部门工资总和
FROM emp e
ORDER BY deptno;