sql高级子查询相关应用

下面对高级子查询做简要总结：包括多列子查询、FROM子句中使用子查询、使用EXISTS和NOT EXISTS操作符、使用子查询更新和删除数据、使用WITH子句

1.多列子查询
1.查询与141号或174号员工的manger_id和department_id相同的其他员工的employee_id，manger_id,department_id

(1)方式一：
SELECT employee_id,manager_id,department_id 
FROM employees
WHERE manger_id IN(SELECT manager_id 
                   FROM employees
                   WHERE employee_id IN(141,174))
AND department_id IN(SELECT department_id
                     FROM employees
                     WHERE employee_id IN(141,174))
AND employee_id NOT IN(141,174);

（2）方式二：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manger_id,department_id) IN (SELECT manager_id,department_id
                                    FROM employees
                                    WHERE employee_id IN(141,174))
AND employee_id NOT IN(141,174);             

2.返回比本部门平均工资高的员工的last_name，department_id，salary以及平均工资

(1)方式一：
SELECT last_name,
       department_id,
       salary,
       (SELECT AVG(salary) 
        FROM employee e3 
        WHERE e1.department_id = e3.department_id 
        GROUP BY department_id) avg_salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2
                WHERE e1.department_id = e2.department_id
                GROUP BY department_id)
(2)方式二：
SELECT a.last_name,a.salary,a.department_id,b.salavg
FROM employees a,(SELECT department_id,
                         AVG(salary) salavg
                  FROM employees 
                  GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;

2.单列子查询表达式
3.显示员工的employee_id，last_name和location。其中，若员工的department_id
与location_id为1800的department_id相同，则location为Canada，其余则为USA

SELECT employee_id,last_name,(CASE department_id
                              WHEN (SELECT department_id
                                    FROM departments
                                    WHERE location_id = 1800)
                             THEN 'Canada' ELSE 'USA') location
FROM employees;

4.若查询员工的employee_id，last_name，要求按照员工的department_name排序

SELECT employee_id,last_name
FROM employee e
ORDER BY(SELECT department_name 
         FROM departments d
         WHERE d.department_id = e.department_id);

3.相关子查询
5.若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employ_id，last_name和其job_id

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*) 
            FROM job_history j
            WHERE e.employee_id = j.employee_id);

EXISTS
6.查询公司管理则的employee_id，last_name，job_id，department_id信息

SELECT employee_id,last_name,job_id,department_id
FROM employees outer
WHERE EXISTS(SELECT 'X'
             FROM employees
             WHERE outer.employee_id = employee_id);

7.查询departments表中，不存在与employees表中的部门的department_id和department_name

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(SELECT 'X'
                FROM employees
                WHERE d.department_id = e.department_id);

4.关于数据更新
8.修改表employees，添加department_name列，赋予department_id相应的部门名称

ALTER TABLE employees
ADD(department_name VARCHAR(14));

UPDATE employees e 
SET department_name = (SELECT department_name
                       FROM departments d
                       WHERE d.department_id = e.department_id);

5.关于数据删除
9.删除表employees表中与emp_history表皆有的数据

DELETE FORM employees e
WHERE employee_id IN(SELECT employee_id
                     FROM emp_history
                     WHERE employee_id = e.employee_id);

6.附加
10.查询员工的last_name，department_id，salary，其中员工的salary，department_id与有奖金的任何一个员工的salary，department_id相同即可

SELECT last_name,department_id,salary
FROM employees
WHERE (salary,department_id) IN(SELECT salary,department_id
                                FROM employees
                                WHERE commission_pct is not null);

11.选择工资大于所有job_id=’sa_man’的员工的工资的员工的last_name,job_id,salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary > all(SELECT salary
                   FROM employees
                   WHERE job_id = 'sa_man');

12.选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees e1
WHERE NOT EXSITS(SELECT 'X'
                FROM employees e2
                WHERE e1.manager_id = e2.employee_id);

13.查询10，50，20号部门的job_id,department_id并且department_id按10，50，20的顺序排列

SELECT job_id,department_id,1 dumy
FROM employees 
WHERE department_id = 10
UNION
SELECT job_id,department_id,2
FROM employees 
WHERE department_id = 50
UNION
SELECT job_id,department_id,3
FROM employees 
WHERE department_id = 20
ORDER BY 3;

s