MySQL/第九章、练习题.sql

#第九章、练习题

#1、查询和Zlotkey相同部门的员工姓名和工资。
SELECT last_name,salary
FROM employees
WHERE department_id IN (
											SELECT department_id
											FROM employees
											WHERE last_name = 'Zlotkey'
											);

#2、查询工资比公司平均工资高的员工的员工号，姓名和工资。
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。

SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL (
										SELECT salary
										FROM employees
										WHERE job_id = 'SA_MAN'
										);

#4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名。
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
												SELECT department_id
												FROM employees
												WHERE last_name LIKE '%u%'
												);
#老师做的
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
												SELECT DISTINCT department_id
												FROM employees
												WHERE last_name LIKE '%u%'
												);

#5、查询在部门的location_id为1700的部门工作的员工的员工号。
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id IN (
												SELECT department_id
												FROM departments
												WHERE location_id = 1700
												);

#6、查询管理者是King的员工姓名和工资。
SELECT e1.last_name,e1.salary,e1.manager_id
FROM employees e1
WHERE manager_id IN (
											SELECT employee_id
											FROM employees e2
											WHERE last_name = 'King'
										);

#老师做的
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (
											SELECT employee_id
											FROM employees
											WHERE last_name = 'King'
										);
#还可以用exists做

#7、查询工资最低的员工信息：last_name，salary。
SELECT last_name,salary
FROM employees
WHERE salary IN (
								SELECT MIN(salary)
								FROM employees
								);

#8、查询平均工资最低的部门信息。
SELECT *
FROM departments
WHERE department_id = (SELECT department_id
											FROM employees
											GROUP BY department_id
											HAVING AVG(salary) <= ALL(
																								SELECT AVG(salary) avg_dsal
																								FROM employees
																								GROUP BY 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) avg_sal
																									FROM employees
																									GROUP BY department_id
																									) t_dept_avg_sal
																						)
											);
#方式二
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
																						)
											);

#方式三：使用LIMIT
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 0,1
																						)
											);
#方式四
SELECT d.*
FROM departments d,(
										SELECT department_id,AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										ORDER BY avg_sal ASC
										LIMIT 0,1
										) t_dept_avg_sal
WHERE d.department_id = t_dept_avg_sal.department_id;


#9、查询平均工资最低的部门信息和该部门的平均工资（相关子查询）。
#我做的，差平均工资未做
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
																									)
												);

#老师做的
#方式一
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) AS d_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) avg_sal
																									FROM employees
																									GROUP BY department_id
																									) t_dept_avg_sal
																						)
											);

#方式二
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) AS d_avg_sal
FROM departments d
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
																						)
											);

#方式三：使用LIMIT
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) AS d_avg_sal
FROM departments d
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 0,1
																						)
											);
#方式四
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) AS d_avg_sal
FROM departments d,(
										SELECT department_id,AVG(salary) avg_sal
										FROM employees
										GROUP BY department_id
										ORDER BY avg_sal ASC
										LIMIT 0,1
										) t_dept_avg_sal
WHERE d.department_id = t_dept_avg_sal.department_id;

#10、查询平均工资最高的job信息。

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
																					)
								);

#老师做的
#方式一：
SELECT *
FROM jobs
WHERE job_id = (
								SELECT job_id
								FROM employees
								GROUP BY job_id
								HAVING AVG(salary) = (
																			SELECT MAX(avg_sal)
																			FROM (
																						SELECT AVG(salary) avg_sal
																						FROM employees
																						GROUP BY job_id
																						) t_dept_avg_sal
																			)
								);

#方式二
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
																			)
								);

#方式三
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 0,1
																			)
								);

#方式四

SELECT j.*
FROM jobs j,(
							SELECT job_id,AVG(salary) avg_sal
							FROM employees
							GROUP BY job_id
							ORDER BY avg_sal DESC
							LIMIT 0,1
							) t_dept_avg_sal
WHERE j.job_id = t_dept_avg_sal.job_id;

#11、查询平均工资高于公司平均工资的部门有哪些？
#我做的，不对
SELECT department_id
FROM employees
GROUP BY department_id
HAVING (
				SELECT AVG(salary)
				FROM employees
				) < ANY (
							SELECT AVG(salary)
							FROM employees
							GROUP BY department_id
							)

#老师做的
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
											SELECT AVG(salary)
											FROM employees
											);

#12、查询出公司中所有manager的详细信息。

SELECT *
FROM employees
WHERE employee_id IN (
											SELECT manager_id
											FROM employees
											GROUP BY manager_id
											);

#老师做的（讲EXISTS时的练习题）
#方式一：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

#方式二：子查询
SELECT DISTINCT employee_id,last_name,job_id,department_id
FROM employees emp
WHERE employee_id IN (
											SELECT DISTINCT manager_id
											FROM employees
											);

#方式三：EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
							SELECT *
							FROM employees e2
							WHERE e1.employee_id = e2.manager_id
							);

#13、各个部门中，最高工资中最低的那个部门的最低工资是多少？

#我做的，不对
SELECT MIN(salary)
FROM employees
WHERE department_id = (
												SELECT department_id
												FROM (
															SELECT MAX(salary),department_id
															FROM employees
															) d_max
											);

#老师做的
#方式一
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
																							)
												);

#方式二
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
																							)
												);

#方式三
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 0,1
																							)
												);

#方式四
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 0,1
									) t_dept_max_sal
WHERE e.department_id = t_dept_max_sal.department_id;

#14、查询平均工资最高的部门的manager的详细信息：
# last_name，department_id，email，salary

SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
											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
																																			)
																							)
											)

#老师做的
#方式一
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
											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
																																		)
																							)
											);

#方式二
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
											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
																																		)
																							)
											);

#方式三
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
											SELECT DISTINCT manager_id
											FROM employees e,(
																				SELECT department_id,AVG(salary) avg_sal
																				FROM employees
																				GROUP BY department_id
																				ORDER BY avg_sal DESC
																				LIMIT 0,1
																				) t_dept_avg_sal
											WHERE e.department_id = t_dept_avg_sal.department_id
											);


#15、查询部门的部门号，其中不包括job_id是“ST_CLERK"的部门号。
#我做的也对，只是使用的表是employees，换成departments表就和老师做的结果一样
SELECT DISTINCT department_id
FROM employees
WHERE department_id <> (
												SELECT department_id
												FROM employees
												WHERE job_id = 'ST_CLERK'
												GROUP BY job_id
												);

#老师做的
#方式一
SELECT department_id
FROM departments
WHERE department_id NOT IN (
														SELECT DISTINCT department_id
														FROM employees
														WHERE job_id = 'ST_CLERK'
														);

#方式二
SELECT department_id
FROM departments d
WHERE NOT EXISTS(
							SELECT *
							FROM employees e
							WHERE d.department_id = e.department_id
							AND e.job_id = 'ST_CLERK'
							);
							

#16、选择所有没有管理者的员工的last_name。
#我做的，所得结果与老师的代码一致
SELECT last_name
FROM employees
WHERE manager_id IS NULL;

#老师做的
SELECT emp.last_name
FROM employees emp
WHERE NOT EXISTS (
							SELECT *
							FROM employees mgr
							WHERE emp.manager_id = mgr.employee_id
							);


#17、查询员工号、姓名、雇佣时间、工资，其中员工的管理者为“De Haan”。
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id = (
										SELECT employee_id
										FROM employees
										WHERE last_name = 'De Haan'
										);

#老师做的
#方式一
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
										SELECT employee_id
										FROM employees
										WHERE last_name = 'De Haan'
										);

#方式二
SELECT e1.employee_id,e1.last_name,e1.hire_date,e1.salary
FROM employees e1
WHERE EXISTS (
							SELECT *
							FROM employees e2
							WHERE e1.manager_id = e2.employee_id
							AND e2.last_name = 'De Haan'
							);


#18、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资（相关子查询）

#在相关子查询课程中已作为例题讲解
#方式一：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
								SELECT AVG(salary)
								FROM employees e2
								WHERE department_id = e1.department_id
								);

#方式二：在FROM中声明子查询

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
								SELECT department_id,AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal;


#19、查询每个部门下的部门人数大于5的部门名称（相关子查询）
SELECT d.department_name
FROM departments d
WHERE 5 < (
					SELECT COUNT(*)
					FROM employees e
					WHERE e.department_id = d.department_id
					);

#20、查询每个国家下的部门个数大于2的国家编号（相关子查询）
SELECT * FROM locations

SELECT country_id
FROM locations l
WHERE 2 < (
					SELECT COUNT(*)
					FROM departments d
					WHERE l.location_id = d.location_id
					);

/*
子查询的编写技巧（或步骤）：（1）、从里往外写		（2）、从外往里写
如何选择？
1、如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写。
2、如果是相关子查询，通常都是从外往里写。
*/