MySQL数据库：第九章：子查询

#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary 
FROM employees
WHERE last_name = 'Abel'
#②查询哪个员工的工资>①
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary 
    FROM employees
    WHERE last_name = 'Abel'
);
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#③查询job_id = ① 并且salary>②的员工姓名，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
);
#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询最低工资
SELECT MIN(salary)
FROM employees
#②查询last_name,job_id,salary，要求salary= ①
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees

);
SELECT * FROM employees;
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
SELECT MIN(salary) 
FROM employees
WHERE department_id = 50
#②查询每个部门的最低工资，筛选条件：哪个部门最低工资>①
SELECT MIN(salary) 最低工资,department_id
FROM employees
GROUP BY department_id
HAVING 最低工资>(
    SELECT MIN(salary) 
    FROM employees
    WHERE department_id = 50
);

#题目：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
#②查询员工姓名，要求：department_id 属于①列表
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN (1400,1700)
);
#题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary 
FROM employees
WHERE job_id = 'IT_PROG'
#②查询其它部门中salary<any(①)
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
    SELECT DISTINCT salary 
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
    SELECT DISTINCT salary 
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#等价于
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#1. 查询和 Zlotkey 相同部门的员工姓名和工资
#①查询Zlotkey的部门
SELECT  department_id
FROM employees
WHERE last_name = 'Zlotkey'
#②查询姓名和工资，要求部门号 = ①
SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT  department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);
#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
#①公司平均工资
SELECT AVG(salary)
FROM employees
#②查询salary>①的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);
#3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②将①结果集和employees表进行连接查询，关联条件为老个表的department_id一致
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE e.salary > ag_dep.ag;
#6. 查询管理者是 King 的员工姓名和工资
#①查询king的员工号
SELECT employee_id
FROM employees
WHERE last_name = 'k_ing'
#②查询哪个员工的领导号是①
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
    SELECT employee_id
    FROM employees
    WHERE last_name = 'k_ing'
);