SQL语句的基本练习、子查询（不相关子查询）、嵌套查询

柚稚姐姐

已于 2022-02-28 21:32:31 修改

阅读量361

点赞数

分类专栏：练习文章标签： sql

于 2022-02-24 20:26:03 首次发布

本文链接：https://blog.csdn.net/qq_41977843/article/details/123120138

版权

练习专栏收录该内容

3 篇文章 0 订阅

订阅专栏

子查询的练习

#题目1:返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT job_id 
FROM employees
WHERE employee_id = '141';

SELECT salary 
FROM employees 
WHERE employee_id = '143';

SELECT first_name,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
);

#2#题目:返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT job_id
FROM employees
WHERE employee_id=141

SELECT salary
FROM employees
WHERE employee_id = 143

SELECT first_name,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:返回公司资最少的员工的last_name,job_id和salary

SELECT MIN(salary)
FROM employees

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees
);

#题目4:查询与141号或174号员工的manager_id和department_id相同的其他员工
#的employee_id，manager_id， department_id

SELECT manager_id
FROM employees
WHERE employee_id = 141

SELECT department_id
FROM employees
WHERE employee_id = 141

SELECT manager_id
FROM employees
WHERE employee_id = 147

SELECT department_id
FROM employees
WHERE employee_id = 147

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
))
OR (manager_id=(SELECT manager_id
FROM employees
WHERE employee_id = 147

)
and department_id=(SELECT department_id
FROM employees
WHERE employee_id = 147
))

AND employee_id NOT IN(141,147);

SELECT manager_id
FROM employees
WHERE employee_id in(141,147)

SELECT department_id
FROM employees
WHERE employee_id in (141,147)



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
)
OR (manager_id,department_id)=(SELECT manager_id,department_id
FROM employees
WHERE employee_id = 147
);

#having 中放入子查询
#题目:查询最低工资大于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

)

#题目:显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’canada’，其余则为UsA’。

#题目:返回其它job_id中比job_id为’IT_PROG’部门任一工资低的员工的员工号、#姓名、job_id 以及salary

 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE job_id <> 'IT_PROG'
 AND salary < ANY (SELECT salary
													FROM employees
													WHERE job_id = 'IT_PROG' 
									);

#查平均工资最低的部门

SELECT AVG(salary) as avg_sal
FROM employees
GROUP BY department_id;

SELECT MIN(avg_sal)
FROM (SELECT AVG(salary) as avg_sal
	FROM employees
	GROUP BY department_id
			)ta_avg_salSQL


#子查询
SELECT employee_id,last_name
FROM employees
WHERE salary In(
									SELECT MIN(salary)
									FROM employees
									GROUP BY department_id
)

#exists和not exists
#题目:查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT DISTINCT mag.employee_id,mag.last_name,mag.job_id,mag.department_id
FROM employees emp JOIN employees mag
WHERE emp.manager_id = mag.employee_id

#子查询
SELECT e.employee_id,e.last_name,e.job_id,e.department_id 
FROM employees e
WHERE employee_id IN (
										SELECT DISTINCT manager_id
										FROM employees
)

SELECT employee_id,last_name,job_id,department_id 
FROM employees 
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
)
#题目:查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT d.department_id ,d.department_name
FROM employees e JOIN departments d ;
ON e.department_id = d.department_id 
WHERE e.departments_id IS NULL


#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees 
WHERE department_id=(
											SELECT  department_id
											from employees
											WHERE last_name="Zlotkey"
)
 

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT avg(salary)
FROM employees

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 salary
FROM employees
WHERE job_id="SA_MAN"

SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL(
								SELECT salary
								FROM employees
								WHERE job_id="SA_MAN"
)
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT DISTINCT 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 department_id
FROM departments
WHERE location_id = "1700"

SELECT employee_id
FROM employees
WHERE department_id IN (
											SELECT 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 MIN(avg_salary)
FROM (
						SELECT avg(salary) avg_salary
						FROM employees
						GROUP BY department_id
)table_minSalary

 
SELECT DISTINCT department_id
FROM employees
WHERE salary <  (
									SELECT MIN(avg_salary)
									FROM (
												SELECT avg(salary) avg_salary
												FROM employees
												GROUP BY department_id
									)table_minSalary
									
								);

#错误
SELECT *
FROM departments
WHERE department_id IN  (
													SELECT DISTINCT department_id
													FROM employees
													WHERE salary <  (
																						SELECT MIN(avg_salary)
																						FROM (
																						SELECT avg(salary) avg_salary
																						FROM employees
																						GROUP BY department_id
																						)table_minSalary
									
																						)


)

#正确1
SELECT *
FROM departments
WHERE department_id = (
												SELECT 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
																										)table_minsalary
																				
																							)
);

#正确2
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
																									)															
							);
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询)
SELECT MIN(avg_salary)
FROM(
		SELECT AVG(salary) avg_salary
		FROM employees
		GROUP BY department_id
)table_minSalary

SELECT 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
									)table_minSalary
								)
								
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) 
FROM departments d
WHERE department_id = (
												SELECT 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
																										)table_minsalary																			
																							)
											);
							
#除了groupby和limmit不能写子查询，其余地方都可以写，包括select


#10.查询平均工资最高的job信息
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary)>=ALL(
												SELECT AVG(salary)
												FROM employees
												GROUP BY department_id
												);

SELECT *
FROM jobs
WHERE job_id =  (
																				SELECT job_id
																				FROM employees
																				GROUP BY job_id
																				HAVING AVG(salary) = (
																																SELECT MAX(m_s)
																																FROM(
																																			SELECT AVG(salary) m_s
																																			FROM employees
																																			GROUP BY job_id
																																		) table_maxsalary
																																) 
																				
								);