SQL(3)子查询

笔记来自宋红康老师课程

MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili

子查询课程

#子查询
#1. 由一个具体的需求，引入子查询
#需求：谁的工资比Abel的高？ #自连接比子查询效率高
#方式1：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式2：自连接 
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` #多表的连接条件
AND e1.last_name = 'Abel';

#方式3：子查询 #10行记录
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
            SELECT salary
             FROM employees
             WHERE last_name='Abel'
            );

#2. 称谓的规范：外查询（或主查询）、内查询（或子查询）
- 子查询的结果被主查询（外查询）使用 。
3. 子查询的分类
角度1：从内查询返回的结果的条目数
	单行子查询  vs  多行子查询

角度2：内查询是否被执行多次
	相关子查询  vs  不相关子查询
	
 比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息。（不同部门员工会对应不同平均工资进行比较）
       不相关子查询的需求：查询工资大于本公司平均工资的员工信息。（不同员工和同一个平均工资进行比较）
#技巧：找到定语部分，即为内查询的内容。1）从内往外写：适合内查询复杂，多层嵌套的情况 2）从外往内写：适合内查询简单的情况

#4. 单行子查询
#4.1 单行操作符： =  !=  >   >=  <  <= 
#题目：查询工资大于149号员工工资的员工的信息 #13行记录
SELECT employee_id,salary
FROM employees
WHERE salary >(
                SELECT salary
                FROM employees
                WHERE employee_id=149   
             );        
  

#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资#11条记录
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和salary
#错误操作：为啥返回King、AD_PRES、2100？？？
SELECT last_name,job_id,MIN(salary)
FROM employees


#正确操作：返回Olson、ST_CLERK、2100.00;此题从外往内写理解起来更舒服
SELECT last_name,job_id,salary 
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

#题目：查询与141号员工的manager_id和department_id相同的其他员工 #7行记录
#的employee_id，manager_id，department_id。
方式1：通用
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id=(
                  SELECT manager_id
                  FROM employees
                  WHERE employee_id=141
                  )
AND department_id=(
                  SELECT department_id
                  FROM employees
                  WHERE employee_id=141      
                  )
AND employee_id <>141;

方式2：了解；对内查询优化
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id)=(
                              SELECT manager_id,department_id
                              FROM employees
                              WHERE employee_id=141    
                              )
AND employee_id <>141;

#题目：查询最低工资大于110号部门最低工资的部门id和其最低工资
#参考答案
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id =110
		     );
#自己的代码
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
                  SELECT MIN(salary)
                  FROM employees
                  WHERE department_id=110
                  GROUP BY department_id
                  #HAVING department_id=110  #用WHERE还是Having?聚合键所对应的条件用WHERE；聚合函数用HAVING
                  );
#优化反思：1)内查询不需要用GROUP BY 就可找到110号部门的最低工资，外查询因为要找每个部门的最低工资才需要GROUP BY。如果分组后再设置筛选条件：区别用WHERE还是HAVING
#2)为啥要考虑部门id是否为NULL值？若题目改成50号部门，最终查询结果会出现NULL值的部门，如果不需要则可去NULL值。
#返回结果：110部门及这个部门的最小工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id = 110;

#返回结果：12个部门id及各部门的MIN工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id    

#CASE中的子查询
#题目：显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’Canada’，其余则为’USA’。
#自己的代码；107条记录；注意 CASE WHEN THEN   ELSE END 的两种表达方式
SELECT employee_id,last_name,
(CASE 
WHEN department_id=(
SELECT department_id
FROM departments
WHERE location_id=1800
) THEN 'Canada'
ELSE 'USA'
END) AS location
FROM employees;

#参考答案
SELECT department_id, employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
						ELSE 'USA' END "location"
FROM employees;



#4.2 子查询中的空值问题：由于内查询返回结果为空，故外查询查询结果也为空
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

#4.3 非法使用子查询
#错误：Subquery returns more than 1 row；内查询返回12个部门各自的最低工资，为多行记录，不能用=
SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);   

#5.多行子查询
#5.1 多行子查询的操作符： IN——相当于=，表示或的意思；  ANY ALL SOME(同ANY)——需要和单行比较操作符一起使用
# ANY / ALL:
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary 
#76条记录；比IT部门任一工资低，只要满足比IT部门最高工资9000元低即可
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY (
                  SELECT salary
                  FROM employees
                  WHERE job_id='IT_PROG'      
                  )
AND job_id <>'IT_PROG'；


#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
#返回44条记录；比IT部门所有员工工资低，即满足比IT部门最低工资4200还要低
SELECT 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';

#题目：查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。在Oracle中可以，比如 MIN(AVG(salary))。单行函数可以嵌套。
#方式1：自己的代码
SELECT department_id,AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary 
LIMIT 1;

#思路：类似高考数学题，根据已知条件推出多个结论，再尝试把它们组合起来解题。从内往外。
#step1.找出各个部门的平均工资AVG(salary); GROUP BY
SELECT AVG(salary)
FROM employees
GROUP BY department_id
step2:把1中的查询结果当成一张表t_dept_avg_sal，再找该表中avg_sal字段的最小值(不能嵌套聚合函数，故通过重命名将其变成字段)；
#返回结果为最低平均工资
SELECT MIN(avg_sal)
FROM (
      SELECT AVG(salary) AS avg_sal
      FROM employees
      GROUP BY department_id      
      ) AS t_dept_avg_sal
step3：在employees表中通过筛选条件：AVG(salary)=2中的最低平均工资，找出department_id
SELECT department_id,AVG(salary)
FROM employees 
GROUP BY department_id
HAVING AVG(salary)=(
                  SELECT MIN(avg_sal)
                  FROM (
                        SELECT AVG(salary) AS avg_sal
                        FROM employees
                        GROUP BY department_id      
                        ) AS t_dept_avg_sal      
                  );


#从外往内更好理解，筛选条件为：平均工资最低：1）AVG(salary)=最低平均工资；2）AVG(salary)<=ALL平均工资
#方式2：一会AVG(salary),一会avg_sal,太复杂了？？？怎么直接构造包含department_id和avg_sal的新表？？？
#SQL对该代码的执行顺序？？？
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM(
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id #此内查询将平均工资重命名为字段avg_sal
				) t_dept_avg_sal #把avg_sal这列数据看成一个表
			);#此子查询返回结果为最低平均工资

#方式2：代码更容易；从外往内
#step1：外查询语句；AVG(salary)<=ALL平均工资
#step2:查出各部门的平均工资
#step3：2中返回的是多行记录，故用ALL/ANY等关键字。
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
                        SELECT AVG(salary)
                        FROM employees
                        GROUP BY department_id      
                        );


#5.3 空值问题：要判断子查询中的NULL值是否是自己想要的，不需要则要去空值。
#要查管理层之外的员工：107-18=89条记录；WHERE employee_id  IN ()可查出18条记录；
#由于内查询中包含NULL值，当没有去空值的前提下去做外查询，返回结果为空。
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees 
                  WHERE manager_id IS NOT NULL
			);

#6. 相关子查询
#回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
#6.1 
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);
		
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1：使用相关子查询 #返回38条记录
#思路：本部门平均工资要随外部查询记录变化。
#执行逻辑：
#1、employees表总共107行，比如第一次输入last_name='King',salary=24000,department_id=90的记录。
#2、内查询查出90号部门的平均工资为19333。
#3、24000>19333，故满足条件，输出last_name='King',salary=24000,department_id=90
#4、比如第2次输入last_name='Austin',salary=4800,department_id=60的记录。
#5、内查询查出60号部门的平均工资为5760.
#6、4800<5760，故不满足条件，该结果不输出。
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1
WHERE salary>(
            SELECT AVG(salary)
            FROM employees e2
            WHERE department_id=e1.department_id
            );

#查出每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;


#方式2：在FROM中声明子查询
#思路：1、查出每个部门的平均工资，并将其查询结果重命名为表e2。2、原employees表与e2表进行多表内连接
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees AS e1,
(SELECT department_id,AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id) AS e2
WHERE e1.department_id=e2.department_id
AND e1.salary>e2.avg_sal;



#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
#输出这些相同id的员工的employee_id,last_name和其job_id
#相关子查询。 job_history表为轮岗情况，即轮岗次数大于等于2的结果要输出。 疑问：如何计数——COUNT
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e 
WHERE 2<=(
            SELECT COUNT(*)
            FROM job_history j
            WHERE j.employee_id=e.employee_id
         );
# 执行逻辑：
#1、输入employee_id=101，内查询返回结果为2次
#2、2次符合条件，故输出101
#3、输入employee_id=102，内查询返回结果为1次
#4、1次不符合条件，故不输出


#6.2 EXISTS 与 NOT EXISTS关键字:相关子查询
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：自连接
#要判断出是管理者，2张employees表，e1.manager_id=e2.employee_id 自连接 把e2看成管理者表。 返回18行记录
SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
FROM employees e1
JOIN employees e2
ON e1.manager_id=e2.employee_id;

SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id;

#返回19条记录，其中1个NULL值
SELECT DISTINCT manager_id
FROM employees;

#方式2：子查询
#正确做法：1、查出manger_id 2、employee_id IN () 子查询返回多行结果，故用IN
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
                        SELECT DISTINCT manager_id
                        FROM employees
                        );

#错误操作：相关子查询？？？
SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE e1.employee_id IN (
                        SELECT employee_id
                        FROM employees e2
                        WHERE e2.employee_id=e1.manager_id      
                        AND e1.manager_id IS NOT NULL);
#方式3：使用EXISTS ？？？
#e1表中的employee_id=100送进去后，与e2表中的107条记录进行比较，若正好等于e2表中的manager_id，则返回TRUE
#e2表第1行manager_id=NULL，此时返回FALSE；e2表第2行manager_id=100，此时返回TRUE
SELECT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE  EXISTS (
                  SELECT * #可以是SELECT任何字段
                  FROM employees e2
                  WHERE e2.manager_id=e1.employee_id     #注意该筛选条件不同于自连接的筛选条件
                  );

#题目：查询departments表中，不存在于employees表中的部门的department_id和department_name
#方式1：外连接 只存在departments表中的数据 返回16条记录
SELECT d.department_id,d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;

#方式2：NOT EXISTS
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
                  SELECT *
                  FROM employees e
                  WHERE e.department_id=d.department_id      
                  );

 
#题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
#员工表中增加新字段department_name
# 1） ALTER TABLE employees 
ADD(department_name VARCHAR2(14)); 

# 通过两表部门id相等的关系，把部门表里的department_name更新到员工表。相关子查询。
#2） UPDATE employees e
SET department_name = (
                        SELECT department_name 
                        FROM departments d 
                        WHERE e.department_id = d.department_id
                        );

#题目：删除表employees中，其与emp_history表皆有的数据    
DELETE FROM employees e1 
WHERE employee_id in (
                        SELECT employee_id 
                        FROM emp_history e2
                        WHERE e2.employee_id = e1.employee_id
                        );  

子查询课后练习

#子查询课后练习题

#做题技巧总结
#要通过执行内查询的返回结果判断是单行还是多行子查询、相关还是非相关子查询



#1.查询和Zlotkey相同部门的员工姓名和工资 
#单行子查询，不相关子查询，返回34条记录
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        WHERE last_name='Zlotkey'      
                    );


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
#单行子查询，不相关子查询,返回51条记录
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
            SELECT AVG(salary)
            FROM employees      
            );
           

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary 
#多行子查询；比所有都高，即比最高值14000高。 不相关子查询。 返回3行记录。
SELECT last_name,job_id,salary
FROM employees
WHERE salary>ALL(
                  SELECT salary
                  FROM employees
                  WHERE job_id='SA_MAN'
                  );


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 
#多行子查询IN, 返回96条记录
SELECT employee_id,last_name
FROM employees
WHERE department_id IN ( 
                        SELECT DISTINCT department_id #加或不加DISTINCT不影响整体查询结果
                        FROM employees
                        WHERE last_name LIKE '%u%'      
                        );


#5.查询在部门的location_id为1700的部门工作的员工的员工号
#多行子查询IN，不相关子查询,返回18条记录
SELECT employee_id
FROM employees
WHERE department_id IN (
                        SELECT department_id
                        FROM departments
                        WHERE location_id=1700
                        );


#6.查询管理者是King的员工姓名和工资 
#多行子查询，不相关子查询，返回14条记录
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
                  SELECT employee_id
                  FROM employees
                  WHERE last_name='King'
                  );

#7.查询工资最低的员工信息: last_name, salary 
#单行子查询，不相关子查询，返回1条记录
SELECT last_name,salary
FROM employees
WHERE salary=(
                  SELECT MIN(salary)
                  FROM employees      
                  );


#8.查询平均工资最低的部门信息(!!!重要)（把部门表里的所有部门信息都找出来）
#高级篇中会有工具分析不同方式的执行快慢
#方式1：从里往外写：把平均工资查询结果当成一个表，查找出MIN(avg_sal)——员工表的department_id——部门表的所有信息
SELECT *
FROM departments
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary)=(
                                          SELECT MIN(avg_sal)
                                          FROM (
                                                SELECT AVG(salary) AS avg_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ) AS t_dept_avg_sal
                                          )
                   );


#为何把department_id和avg_sal两个字段作为一张表时，SELECT语句查询结果部门为NULL值？？？聚合函数的问题吗？？？

                  SELECT department_id,AVG(salary)
                  FROM (
                        SELECT department_id,AVG(salary) AS avg_sal
                        FROM employees
                        GROUP BY department_id
                        ) AS t_dept_avg_sal;

                 
#方式2：AVG(salary)<=ALL() 多行子查询，不相关子查询
SELECT *
FROM departments
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary)<=ALL(
                                                SELECT AVG(salary)
                                                FROM employees
                                                GROUP BY department_id     
                                                )
                    );

#方式3：利用 ORDER BY 和 LIMIT 把最低工资3475找出来
SELECT *
FROM departments
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary)=(
                                                SELECT AVG(salary) avg_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ORDER BY avg_sal
                                                LIMIT 1 #即使有两个相等的最低平均工资，也不影响
                                           ) 
                   );

#方式4：多表连接：把ORDER BY 和 LIMIT 把最低工资3475、部门id两个字段作为一张表
SELECT d.*
FROM departments d ,(
                        SELECT department_id,AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id
                        ORDER BY AVG(salary)
                        LIMIT 1
                    ) t_dept_avg_sal
WHERE d.department_id=t_dept_avg_sal.department_id;


#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询） 
#在8的基础上对方式1、2、3、4改进方法一：SELECT中写子查询，以方式1为例
SELECT d. *,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) avg_sal
FROM departments d
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING AVG(salary)=(
                                          SELECT MIN(avg_sal)
                                          FROM (
                                                SELECT AVG(salary) AS avg_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ) AS t_dept_avg_sal
                                          )
                   );

#在8的基础上对4改进方法二
SELECT d.*,t_dept_avg_sal.avg_sal
FROM departments d ,(
                        SELECT department_id,AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id
                        ORDER BY AVG(salary)
                        LIMIT 1
                    ) t_dept_avg_sal
WHERE d.department_id=t_dept_avg_sal.department_id;


#10.查询平均工资最高的 job 信息
#方式1: 把平均工资查询结果当成一个表，查出MAX(avg_sal)——员工表中的job_id——job表的所有信息
SELECT *
FROM jobs
WHERE job_id=(
                  SELECT job_id
                  FROM employees
                  GROUP BY job_id
                  HAVING AVG(salary)=(
                                          SELECT MAX(avg_sal)
                                          FROM(
                                                SELECT job_id,AVG(salary) avg_sal
                                                FROM employees
                                                GROUP BY job_id
                                              ) t_job_avg_sal
                                     )
             );


#方式2：AVG(salary)>=ALL(平均工资)
SELECT *
FROM jobs
WHERE job_id=(
                  SELECT job_id
                  FROM employees
                  GROUP BY job_id
                  HAVING AVG(salary)>=ALL(
                                          SELECT AVG(salary)
                                          FROM employees
                                          GROUP BY job_id      
                                         )
             );

#方式3：利用LIMIT和GROUP BY直接找出最高的平均工资
SELECT *
FROM jobs
WHERE job_id=(
                  SELECT job_id     
                  FROM employees
                  GROUP BY job_id
                  HAVING AVG(salary)=(
                                    SELECT AVG(salary) avg_sal
                                    FROM employees
                                    GROUP BY job_id
                                    ORDER BY avg_sal DESC
                                    LIMIT 1
                                    )
              );

#方式4：多表连接，把ORDER BY 和 LIMIT 把最高工资、job_id两个字段作为一张表
SELECT j.*
FROM jobs j,(
            SELECT job_id,AVG(salary) avg_sal
            FROM employees
            GROUP BY job_id
            ORDER BY avg_sal DESC
            LIMIT 1
            ) t_job_avg_sal
WHERE j.job_id=t_job_avg_sal.job_id;


#11.查询平均工资高于公司平均工资的部门有哪些?
#单行子查询，不相关子查询。    让查找部门的话，NULL值通常不列示。  非聚合函数写在WHERE上执行效率更高。
SELECT department_id,AVG(salary)
FROM 
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>(
                  SELECT AVG(salary)
                  FROM employees      
                  );


#12.查询出公司中所有 manager 的详细信息
#技巧：子查询和自连接一般可以互用。用IN的一般可以改写成EXISTS，NOT IN 可改写成 NOT EXISTS。
#方式1：多行子查询；返回18行记录
SELECT *
FROM employees
WHERE employee_id IN (
                        SELECT DISTINCT manager_id
                        FROM employees
                        );

#方式2：自连接,内连接即交集部分
SELECT DISTINCT *
FROM (
      SELECT mgr.*
      FROM employees emp,employees mgr
      WHERE emp.manager_id=mgr.employee_id
      ) t_mgr;

#方式3：使用EXISTS：相关子查询；注意与自连接连接条件的区别。 比如e1表中100号员工送进去，与e2表中的107条记录比较。
#e2表中的第1条记录不满足条件，FALSE。第2条记录满足条件，TRUE。
SELECT e1.*
FROM employees e1
WHERE EXISTS (
            SELECT *
            FROM employees e2
            WHERE e2.manager_id=e1.employee_id
            );

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
#举3个部门实例进行理解
#方式1：MIN(max_sal)——员工表中的department_id——该部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary)=(
                                          SELECT MIN(max_sal)
                                          FROM (
                                                SELECT MAX(salary) max_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ) t_dept_max_sal
                                          )
                     );

#方式2：MAX(salary)<=ALL(),多行子查询，不相关子查询
SELECT MIN(salary)
FROM employees
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary)<=ALL(                                       
                                                SELECT MAX(salary) 
                                                FROM employees
                                                GROUP BY department_id
                                              )
                     );

#方式3：order by和limit 
SELECT MIN(salary)
FROM employees
WHERE department_id=(
                        SELECT department_id
                        FROM employees
                        GROUP BY department_id
                        HAVING MAX(salary)=(                              
                                                SELECT MAX(salary) max_sal
                                                FROM employees
                                                GROUP BY department_id
                                                ORDER BY max_sal ASC
                                                LIMIT 1                                             
                                          )
                     );

#方式4：多表连接,把order by和limit查询结果作为一张表
SELECT MIN(salary)
FROM employees e,(
                   SELECT department_id,MAX(salary) max_sal
                   FROM employees
                   GROUP BY department_id
                   ORDER BY max_sal ASC
                   LIMIT 1                                    
                 ) t_dept_max_sal
WHERE e.department_id=t_dept_max_sal.department_id;


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary 
#平均工资——平均工资最高——员工表中的部门——员工表中的manager_id
#方式1：
SELECT last_name,department_id,email,salary 
FROM employees
WHERE employee_id=(                                                       #如果没有AND manager_id IS NOT NULL，就改成WHERE employee_id IN(),或者=ANY()
                  SELECT DISTINCT manager_id
                  FROM employees
                  WHERE department_id=(
                                          SELECT department_id
                                          FROM employees
                                          GROUP BY department_id
                                          HAVING AVG(salary)=(
                                                                  SELECT MAX(avg_sal)
                                                                  FROM (
                                                                        SELECT AVG(salary) avg_sal
                                                                        FROM employees
                                                                        GROUP BY department_id
                                                                        ) t_dept_avg_sal
                                                            )
                                    ) 
                  AND manager_id IS NOT NULL
                  );

#方式2：AVG(salary)>=ALL()
SELECT last_name,department_id,email,salary 
FROM employees
WHERE employee_id=(                                                      
                  SELECT DISTINCT manager_id
                  FROM employees
                  WHERE department_id=(
                                          SELECT department_id
                                          FROM employees
                                          GROUP BY department_id
                                          HAVING AVG(salary)>=ALL(                                                                
                                                                        SELECT AVG(salary) 
                                                                        FROM employees
                                                                        GROUP BY department_id                                                                        
                                                            )
                                    ) 
                  AND manager_id IS NOT NULL
                  );

#方式3：order by和limit 
SELECT last_name,department_id,email,salary 
FROM employees
WHERE employee_id=(                                                      
                  SELECT DISTINCT manager_id
                  FROM employees
                  WHERE department_id=(
                                          SELECT department_id
                                          FROM employees
                                          GROUP BY department_id
                                          HAVING AVG(salary)=(                                                                
                                                                        SELECT AVG(salary) 
                                                                        FROM employees
                                                                        GROUP BY department_id
                                                                        ORDER BY AVG(salary) DESC
                                                                        LIMIT 1                                                                         
                                                            )
                                    ) 
                  AND manager_id IS NOT NULL
                  );
#方式4：多表连接
SELECT last_name,department_id,email,salary 
FROM employees
WHERE employee_id=(
                  SELECT DISTINCT e.manager_id
                  FROM employees e,(
                                    SELECT department_id,AVG(salary) 
                                    FROM employees
                                    GROUP BY department_id
                                    ORDER BY AVG(salary) DESC
                                    LIMIT 1   
                              ) t_dept_avg_sal
                  WHERE e.department_id=t_dept_avg_sal.department_id               
                  AND e.manager_id IS NOT NULL
                  );

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号 
#方法1：NOT IN ，注意找部门表，而不是员工表
SELECT DISTINCT department_id
FROM departments
WHERE department_id NOT IN (                          #job_id="ST_CLERK"的部门只有一个，用!=也可以。
                        SELECT DISTINCT department_id
                        FROM employees
                        WHERE job_id="ST_CLERK"
                        );
#方式2：NOT IN 通常都可改写成 NOT EXISTS,把部门表里的各部门的详细信息在员工表里找出来，再限定条件job_id="ST_CLERK" 找出其部门
SELECT department_id
FROM departments d
WHERE  NOT EXISTS (
                  SELECT *
                  FROM employees e
                  WHERE e.department_id=d.department_id
                  AND e.job_id="ST_CLERK"    
                  );

#16. 选择所有没有管理者的员工的last_name 
#方式1：
SELECT last_name
FROM employees
WHERE manager_id IS NULL;
#方式2：关联子查询:employees表emp进去，如果能在employees表mgr中找到emp.employee_id=mgr.manager_id
SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
                 SELECT *
                  FROM employees mgr
                  WHERE emp.manager_id=mgr.employee_id
                  );

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan' 
#方式1：De Haan——ID,manager_id=100的可能有多个员工，用IN
SELECT employee_id, last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
                  SELECT employee_id
                  FROM employees
                  WHERE last_name="De Haan"
                  );
#方式2：IN可以改写称EXISTS。emp的管理者ID正好是mgr的员工ID。
SELECT employee_id, last_name,hire_date,salary
FROM employees emp
WHERE EXISTS (
SELECT *
FROM employees  mgr
WHERE emp.manager_id=mgr.employee_id
AND mgr.last_name='De Haan'
);


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询） 
#方式1：相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary >  (
                  SELECT AVG(salary)
                  FROM employees e2
                  WHERE e2.department_id=e1.department_id
                );

#方式2：FROM中声明子查询，再进行多表连接
SELECT  employee_id,last_name,salary
FROM employees e1,(
                  SELECT department_id,AVG(salary) avg_sal
                  FROM employees 
                  GROUP BY department_id
               )dept_avg_sal
WHERE e1.department_id=dept_avg_sal.department_id
AND e1.salary>dept_avg_sal.avg_sal;

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询） 
#把部门表送进去，看员工表该部门是否人数大于5
SELECT DISTINCT department_id
FROM departments d1
WHERE 5<(
            SELECT COUNT(*)
            FROM employees e2
            WHERE d1.department_id=e2.department_id
         );


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）                  
SELECT country_id
FROM locations l
WHERE 2<(
            SELECT COUNT(*)
            FROM  departments d
            WHERE l.location_id=d.location_id
        );

/* 
子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写

如何选择？
① 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写
② 如果是相关子查询的话，通常都是从外往里写。