子查询和高级子查询

--  谁的工资比 Abel 高SELECT salary FROM employees WHERE last_name='Abel';SELECT last_name, salary FROM employees WHERE salary>=11000;SELECT last_name, salary FROM employees WHERE salary>=(SELECT salary FROM employees WHERE last_name='Abel');--  子查询一般是哪个条件未知--  子查询要包含在括号内。--  将子查询放在比较条件的右侧。--  单行操作符对应单行子查询，多行操作符对应多行子查询。--  返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资SELECT last_name, job_id, salary            FROM   employees            WHERE  job_id =                              (SELECT job_id                             FROM   employees                             WHERE  employee_id = 141)            AND    salary >                            (SELECT salary                             FROM   employees                             WHERE  employee_id = 143);--  在子查询中使用组函数    题目：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name, job_id, salary            FROM   employees            WHERE  salary =                             (SELECT MIN(salary)                             FROM   employees);--  子查询中的 HAVING 子句     首先执行子查询。向主查询中的HAVING 子句返回结果。--  题目：查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT   department_id, MIN(salary)FROM     employeesGROUP BY department_idHAVING   MIN(salary) >                       (SELECT MIN(salary)                        FROM   employees                        WHERE  department_id = 50);--  多行子查询   IN: 等于列表中任意一个   ANY:  和子查询返回的某个值比较  ALL：和子查询返回的所有值比较--  1、在多行子查询中使用 ALL 操作符  --  题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salarySELECT employee_id, last_name, job_id, salary                        FROM   employees                        WHERE  salary < ALL                                            (SELECT salary                                             FROM   employees                                             WHERE  job_id = 'IT_PROG')                        AND    job_id <> 'IT_PROG';--  2、子查询中的空值问题SELECT emp.last_name                        FROM   employees emp                        WHERE  emp.employee_id NOT IN                                                     (SELECT mgr.manager_id                                                      FROM   employees mgr);--  NO ROWS selected--  高级子查询SELECT last_nameFROM   employeesWHERE  salary >                 (SELECT salary                 FROM   employees                 WHERE  employee_id = 149) ;--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id  --成对比较SELECTemployee_id, manager_id, department_idFROMemployeesWHERE  (manager_id, department_id) IN                      (SELECT manager_id, department_id                       FROM   employees                       WHERE  employee_id IN (141,174))ANDemployee_id NOT IN (141,174);--不成对比较举例SELECT  employee_id, manager_id, department_idFROM    employeesWHERE   manager_id IN                   (SELECT  manager_id                   FROM    employees                   WHERE   employee_id IN (174,141))AND     department_id IN                   (SELECT  department_id                   FROM    employees                   WHERE   employee_id IN (174,141))ANDemployee_id NOT IN(174,141);--在 FROM 子句中使用子查询--问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资-- 方法一：select last_name,department_id,salary,(select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salaryfrom employees e1where salary >          (select avg(salary)          from employees e2            where e1.department_id = e2.department_id          GROUP BY department_id          );--方法二：SELECT  a.last_name, a.salary,         a.department_id, b.salavgFROM    employees a, (SELECT   department_id,                       AVG(salary) salavg                      FROM     employees                      GROUP BY department_id) bWHERE   a.department_id = b.department_idAND     A.salary > b.salavg;--单列子查询应用举例--在 CASE 表达式中使用单列子查询--问题：显式员工的employee_id,last_name和location。其中，--若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。SELECT employee_id, last_name,       (CASE        WHEN department_id =                                         THEN 'Canada' ELSE 'USA' END) LOCATIONFROM   employees;--在 ORDER BY 子句中使用单列子查询--问题：查询员工的employee_id,last_name,要求按照员工的department_name排序SELECT   employee_id, last_nameFROM     employees eORDER BY(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);--相关子查询举例--问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_idSELECT last_name, salary, department_idFROM   employees outerWHERE  salary >(SELECT AVG(salary)                   FROM   employees                   WHERE  department_id =                            OUTER.department_id) ;--问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_idSELECT e.employee_id, last_name,e.job_idFROM   employees e WHERE  2 <= (SELECT COUNT(*)             FROM   job_history              WHERE  employee_id = e.employee_id);--EXISTS 操作符检查在子查询中是否存在满足条件的行--如果在子查询中存在满足条件的行:--不在子查询中继续查找--条件返回 TRUE--如果在子查询中不存在满足条件的行:--条件返回 FALSE--继续在子查询中查找--问题：查询公司管理者的employee_id,last_name,job_id,department_id信息SELECT employee_id, last_name, job_id, department_idFROM   employees outerWHERE  EXISTS ( SELECT 'X'                 FROM   employees                 WHERE  manager_id =                         OUTER.employee_id);--问题：查询departments表中，不存在于employees表中的部门的department_id和department_nameSELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (SELECT 'X'                  FROM   employees                  WHERE  department_id                          = d.department_id);--相关更新应用举例ALTER TABLE employeesADD(department_name VARCHAR2(14));UPDATE employees eSET    department_name =               (SELECT department_name        FROM   departments d       WHERE  e.department_id = d.department_id);--问题：删除表employees中，其与emp_history表皆有的数据DELETE FROM employees EWHERE employee_id =             (SELECT employee_id            FROM   emp_history             WHERE  employee_id = E.employee_id);--使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块--WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中--使用 WITH 子句可以提高查询效率--问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息WITH dept_costs  AS (   SELECT  d.department_name, SUM(e.salary) AS dept_total   FROM    employees e, departments d   WHERE   e.department_id = d.department_id   GROUP BY d.department_name),avg_cost    AS (   SELECT SUM(dept_total)/COUNT(*) AS dept_avg   FROM   dept_costs)SELECT * FROM   dept_costs WHERE  dept_total >        (SELECT dept_avg          FROM avg_cost)ORDER BY department_name;