#进阶8：子查询
/*
出现在其他语句中的select语句（嵌套）

分类：
	按子查询出现的位置：
		SELECT 后面
        FROM 后面
        WHERE 或 HAVING 后面
        EXISTS后面
*/
USE myemployees;

#一、where 或 having 后面
#1、单行
#案例1:谁的工资比 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和工资
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:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT 	MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#2、多行
#IN/NOT IN, ANY/SOME, ALL
#案例4:查询location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT department_id
    FROM departments
    WHERE location_id IN (1400, 1700)
);

#案例5:查询其他工种中比job_id为IT_PROG工种的任一工资低的员工的员工号、姓名、job_id和salary
#比任一低，< max()
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = ‘IT_PROG‘
) AND job_id != ‘IT_PROG‘;

SELECT employee_id, last_name, salary
FROM employees
WHERE salary < (
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = ‘IT_PROG‘
) AND job_id != ‘IT_PROG‘;


#案例6:查询其他工种中比job_id为IT_PROG工种的任意工资低的员工的员工号、姓名、job_id和salary
SELECT employee_id, last_name, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = ‘IT_PROG‘
) AND job_id != ‘IT_PROG‘;


#3、多行多列
#案例7:查询员工编号最小并且工资最高的员工信息
#普通写法
SELECT * 
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
) AND salary = (
	SELECT MAX(salary)
	FROM employees
);

#多行多列的写法，要求每一列的操作符是一致的
SELECT * 
FROM employees
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);


#二、select 后面
#案例8：查询每个部门的员工个数
SELECT d.department_id, (
	SELECT count(*)
    FROM employees as e
    WHERE e.department_id = d.department_id
) as num
FROM departments as d;


#案例9：查询员工号=102的部门名
#强行外查询。。。
SELECT (
	SELECT department_name
	FROM departments as d
	INNER JOIN employees e
	ON d.department_id = e.department_id
	WHERE e.employee_id = 102
) 部门名;


#三、from 后面
#案例10:查询每个部门的平均工资的工资等级
SELECT avg(salary), department_id
FROM employees
GROUP BY department_id;

# 子查询结果充当表，必须起别名
SELECT avg_dep.*, g.grade_level
FROM (
	SELECT avg(salary) as ag, department_id
	FROM employees
	GROUP BY department_id
) as avg_dep  
INNER JOIN job_grades as g
ON avg_dep.ag BETWEEN g.lowest_sal AND g.highest_sal;


#四、exists /not exists 后面
#bool类型，只关心有没有
#用的较少，能够用其他子查询代替
SELECT EXISTS(
	SELECT employee_id 
    FROM employees 
    WHERE salary=30000
);

#案例11:查询有员工的部门名
SELECT department_name
FROM departments as d
WHERE exists(
	SELECT *
    FROM employees as e
    WHERE d.department_id = e.department_id
);