练习题2——子查询经典例题

上网课写的作业，半个小时的课，硬生生写了一个半多小时。记录一下，方便以后容易想起来

题目中涉及的脚本文件：myemployees.sql

链接：https://pan.baidu.com/s/1z_AUrCNCv87XhGGm7FypSQ?pwd=e5wy 
提取码：e5wy
复制这段内容后打开百度网盘手机App，操作更方便哦

#子查询经典案例

1. 查询工资最低的员工信息: last_name, salary
2. 查询平均工资最低的部门信息
3. 查询平均工资最低的部门信息和该部门的平均工资
4. 查询平均工资最高的 job 信息
5. 查询平均工资高于公司平均工资的部门有哪些?
6. 查询出公司中所有 manager 的详细信息.
7.*********************************************

1. 查询工资最低的员工信息: last_name, salary

#①工资最低
SELECT MIN(salary) 
FROM employees
#② 查询工资最低的员工信息
SELECT last_name,salary
FROM employees
WHERE salary =(
	SELECT MIN(salary) 
	FROM employees)

2. 查询平均工资最低的部门信息

 #①查询各部门平均工资
 SELECT AVG(salary),department_id
 FROM employees
 GROUP BY department_id
 #② 在①的表中查询平均工资最低的部门编号
 
 SELECT department_id
 FROM employees
 GROUP BY department_id
 ORDER BY AVG(salary)
 LIMIT 1
 
 #③查询②部门编号的  (这里有个漏洞 假设了没有平均工资相同的部门，如果非要严谨来写，则使用平均工资作为查询的条件)
 SELECT *
 FROM departments
 WHERE department_id = ( 
	 SELECT department_id
	 FROM employees
	 GROUP BY department_id
	 ORDER BY AVG(salary)
	 LIMIT 1)
 

3. 查询平均工资最低的部门信息和该部门的平均工资

 #①查询平均工资最低的部门编号
SELECT * , (SELECT AVG(salary)  FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1) "平均工资"
FROM departments d 
WHERE d.department_id = (
	 SELECT department_id
	 FROM employees
	 GROUP BY department_id
	 ORDER BY AVG(salary)
	 LIMIT 1) 
 
 #做法2：
 SELECT d.* , pj
 FROM departments d
 JOIN ( 
	 SELECT AVG(salary) pj ,department_id
	 FROM employees
	 GROUP BY department_id
	 ORDER BY AVG(salary)
	 LIMIT 1) ag
ON d.department_id = ag.department_id
	 

4. 查询平均工资最高的 job 信息

#①查询平均工资最高的job_id
SELECT job_id , AVG(salary) pj
FROM employees
GROUP BY job_id
ORDER BY  pj DESC
LIMIT 1;    
#② 查询job信息
SELECT j.*
FROM jobs j
WHERE j.`job_id` = (SELECT job_id 
FROM employees
GROUP BY job_id
ORDER BY  AVG(salary) DESC
LIMIT 1 )

5. 查询平均工资高于公司平均工资的部门有哪些?

 #①查询部门平均工资和部门编号
 SELECT AVG(salary),department_id
 FROM employees e
 GROUP BY  department_id
 
 #②在①的结果集上，筛选
 SELECT AVG(salary),department_id
 FROM employees e
 GROUP BY  department_id
 HAVING AVG(salary)>(SELECT AVG(salary) FROM employees)

6. 查询出公司中所有 manager 的详细信息.

#做法一：
SELECT DISTINCT e.*
FROM employees e
JOIN employees d
ON e.employee_id = d.manager_id

#做法二：
#①查询所有manager_id  
SELECT DISTINCT manager_id
 FROM employees 
 #查询所有manager属于①
 SELECT *
 FROM employees
 WHERE employee_id = ANY( SELECT DISTINCT manager_id FROM employees );

因为是练习子查询，所有有一些用连接的方法就没有使用。同时题目假设了没有相同的平均工资或最大工资（因为题中多次使用了 limit ）