MySQL基础篇第9章

李林楠
已于 2023-01-05 16:50:41 修改
阅读量175
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2023-01-05 16:50:11 首次发布
本文链接：https://blog.csdn.net/lln1540295459/article/details/128563718
版权
MySQL 专栏收录该内容
28 篇文章 1 订阅
订阅专栏
课后练习

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name ,salary 
from employees 
where department_id = (
											 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 DISTINCT department_id 
												FROM employees 
												where last_name LIKE '%u%'
												)
												
												
												
#5.查询部门的location_id为1700的部门工作的员工的工作号
SELECT DISTINCT e.department_id
FROM employees e
LEFT JOIN departments d
on e.department_id=d.department_id								
WHERE location_id = 1700												
												
												
												
SELECT employee_id
FROM employees
WHERE department_id in (																							
												SELECT DISTINCT department_id
												FROM departments
												WHERE location_id = 1700
												)									
																								
								
												
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
										 SELECT employee_id 
										 FROM employees 
										 WHERE last_name = 'King'
										)



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



#8.查询平均工资最低的部门信息
SELECT * 
FROM departments
WHERE department_id = (
											  SELECT DISTINCT department_id
												FROM employees
												GROUP BY department_id
												HAVING AVG(salary) = (
																						 SELECT MIN(avg_salary)
																						 FROM (
																									 SELECT AVG(salary) avg_salary
																									 FROM employees
																									 GROUP BY department_id
																									) avg
																						 )
											)



#优化，减少一层子查询
SELECT * 
FROM departments
WHERE department_id = (
											  SELECT DISTINCT department_id
												FROM employees
												GROUP BY department_id
												HAVING AVG(salary) <= ALL (
																									 SELECT AVG(salary) avg_salary
																									 FROM employees
																									 GROUP BY department_id
																									)
											)



#LIMIT方式
SELECT * 
FROM departments
WHERE department_id = (
											  SELECT DISTINCT department_id
												FROM employees
												GROUP BY department_id
												HAVING AVG(salary) =  (
																							  SELECT AVG(salary) avg_salary
																							  FROM employees
																								GROUP BY department_id
																								ORDER BY avg_salary ASC
																								LIMIT 0,1
																							)
											)



#其他方式
SELECT d.* 
FROM departments d,(
										SELECT department_id,AVG(salary) avg_salary
										FROM employees
										GROUP BY department_id
										ORDER BY avg_salary ASC
										LIMIT 0,1
									 ) t_avg
WHERE d.department_id = t_avg.department_id 



#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
FROM departments d
WHERE department_id = (
											  SELECT DISTINCT department_id
												FROM employees
												GROUP BY department_id
												HAVING AVG(salary) =  (
																							  SELECT AVG(salary) avg_salary
																							  FROM employees
																								GROUP BY department_id
																								ORDER BY avg_salary ASC
																								LIMIT 0,1
																							)
									    )
					
									 

SELECT d.*,avg_salary
FROM departments d,(
                    SELECT department_id,AVG(salary) avg_salary
										FROM employees
										GROUP BY department_id
										ORDER BY avg_salary ASC
										LIMIT 0,1
									 ) t_avg
WHERE d.department_id = t_avg.department_id



#10.查询平均工资最高的job信息
SELECT j.*
FROM jobs j,(
						 SELECT job_id,AVG(salary) avg_salary
						 FROM employees
						 GROUP BY job_id
						 ORDER BY avg_salary DESC
						 LIMIT 0,1
					  ) t_avg
WHERE j.job_id = t_avg.job_id



#11.查询平均工资高于公司平均工资的部门有哪些
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 DISTINCT m.* 
FROM employees e,employees m
WHERE e.manager_id = m.employee_id



#子查询
SELECT *
FROM employees
WHERE employee_id IN (
											SELECT DISTINCT manager_id
											FROM employees
										 )
							


#EXISTS	
SELECT *
FROM employees e
WHERE EXISTS (
							SELECT *
							FROM employees m
							WHERE e.employee_id	= m.manager_id
						 )



#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = (
												SELECT department_id
												FROM employees
												WHERE department_id IS NOT NULL
												GROUP BY department_id
												ORDER BY MAX(salary) ASC
												LIMIT 0,1
											 )



#14.查询平均工资最高的部门的manager的详细信息
SELECT * 
FROM employees 
WHERE employee_id = (
											SELECT manager_id
											FROM departments
											WHERE department_id = (
																							SELECT department_id
																							FROM employees
																							WHERE department_id IS NOT NULL
																							GROUP BY department_id
																							ORDER BY MAX(salary) DESC
																							LIMIT 0,1
																						)
)



#15.查询部门的部门号，其中不包括job_id是'ST_CLERK'的部门号
SELECT department_id
FROM departments
WHERE department_id <> (
												SELECT DISTINCT department_id
												FROM employees
												WHERE job_id = 'ST_CLERK'
												)



SELECT department_id
FROM departments d
WHERE NOT EXISTS (
									SELECT DISTINCT department_id 
									FROM employees e
									WHERE d.department_id = e.department_id
									AND job_id = 'ST_CLERK'
								 )



#16.选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees e1
WHERE NOT EXISTS (
							SELECT *
							FROM employees e2
							WHERE e1.manager_id = e2.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'
										)



#18.查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资（相关子查询）
#相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
								SELECT AVG(salary)
								FROM employees e2
								WHERE department_id = e1.department_id
							 )
							 
							 
							 
#在FROM中声明子查询
SELECT employee_id,last_name,salary
FROM employees e1,(
										SELECT department_id,AVG(salary) avg_salary
										FROM employees
										GROUP BY department_id
									 ) e2
WHERE e1.department_id = e2.department_id
AND e1.salary > e2.avg_salary



#19.查询每个部门下的部门人数大于5的部门
SELECT department_name
FROM departments d
WHERE 5 < (
					 SELECT COUNT(*)
					 FROM employees e
					 WHERE d.department_id = e.department_id
					)				



SELECT department_name
FROM departments
WHERE department_id IN (
												SELECT department_id
												FROM employees
												GROUP BY department_id
												HAVING COUNT(*) > 5
											 )
											 
											 
#20.查询每个国家下的部门个数大于2的国家编号						 
SELECT 	country_id
FROM locations l
WHERE 2 < (
						SELECT COUNT(*)
						FROM departments d
						WHERE l.location_id=d.location_id
					)										 
											 
SELECT location_id,COUNT(*) a
FROM departments
GROUP BY location_id