MySQL|09子查询练习题

一些简单的数据库算法题？（关于查找），可以时常温习以增加查找的熟练度

from 尚硅谷|宋红康| MySQL基础课程

#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 employee_id,last_name,salary
FROM employees
WHERE salary > ANY (
		SELECT salary
		FROM employees
		WHERE JOB_ID = 'SA_MAN'
		);

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名  ??

SELECT job_id,employee_id,first_name
FROM employees
WHERE first_name LIKE '%u%' OR last_name LIKE '%u%'

;

#5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT last_name,department_id,employee_id
FROM employees 
WHERE  department_id = ANY(
			SELECT department_id
			FROM departments 
			WHERE location_id = '1700'
			);

#6.查询管理者是King的员工姓名和工资

SELECT manager_id,last_name,salary
FROM employees
WHERE manager_id = ANY(
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
		     );

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

SELECT employee_id,last_name,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);
#where 中不能存在聚合函数，得用子查询才行

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


-- 获得平均工资最低的那个部门的id号


SELECT *
FROM departments
WHERE department_id =(
			SELECT de
			FROM(
				SELECT department_id de,AVG(salary) sa
				FROM employees
				GROUP BY department_id
			     ) j
			WHERE sa = (
					SELECT MIN(sa)
					FROM (
						SELECT department_id de,AVG(salary) sa
						FROM employees
						GROUP BY department_id
						) j
				    )	
		     );


-- 答案逻辑太绕，我这个逻辑清楚,但是有重复代码，也不是很好，不算高级；答案中使用department_id进行相互关联比较高级
-- 目前还不知道为什么无法从以各部门平均作表的表里直接提取最低的那个部门的department_id，提取了结果为null
#  答：因为选择时用了聚合函数，两者间无法关联，应该填列名，然后对列名进行where筛选

-- 答案逻辑
-- 从内往里写

SELECT *
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) =(-- 通过找到最低价格，然后用最低价格去筛选部门号，这个是关键逻辑
						SELECT MIN(avs)
						FROM(
							SELECT department_id,AVG(salary) avs
							FROM employees
							GROUP BY department_id) in1
						)


);



#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

-- 此为全部信息，下一步将从中获取最低的那个

SELECT j.department_id,j.department_name,j.manager_id,j.location_id,asl
FROM (
	SELECT d.department_id,d.department_name,d.manager_id,d.location_id,AVG(e.salary) asl
	FROM departments d JOIN employees e
	ON d.department_id = e.department_id
	GROUP BY d.department_id
) j
WHERE asl = (
SELECT MIN(asl)
FROM (
	SELECT d.department_id,d.department_name,d.manager_id,d.location_id,AVG(e.salary) asl
	FROM departments d JOIN employees e
	ON d.department_id = e.department_id
	GROUP BY d.department_id
	) j
	     );
-- 代码逻辑如下例
SELECT employee_id,salary
FROM employees
WHERE salary = ( 
		SELECT MIN(salary)
		FROM employees
		 );


-- 答案解法

SELECT *,(SELECT MIN(avs) FROM(	SELECT department_id,AVG(salary) avs
				FROM employees
				GROUP BY department_id) in1 
	  ) min_salary
FROM departments
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary) =(-- 通过找到最低价格，然后用最低价格去筛选部门号，这个是关键逻辑
						SELECT MIN(avs)
						FROM(
							SELECT department_id,AVG(salary) avs
							FROM employees
							GROUP BY department_id
						     ) in1
					     )


);


#10.查询平均工资最高的 job 信息
-- 没什么难度，就是上面两道题的重复
-- 思路：（1）按照job_id分好组并且求平均工资，得到表1 
-- 	 （2）从表1中得到最高平均工资 
-- 	 （3）获取最高平均工资job的id，from employees，使用筛选条件为平均工资为（2）中得到最高平均工资时
--	 （4）从jobs表中获取*信息，筛选条件job_id = （max时job_id）

SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avs)
					FROM(SELECT job_id,AVG(salary) avs
					FROM employees
					GROUP BY job_id) table1
				     )
);

#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 *
FROM employees
WHERE employee_id IN( SELECT DISTINCT manager_id
		      FROM employees
		      WHERE manager_id IS NOT NULL);

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

-- 思路：（1）以部门分组，得到MAX(salary)表作表1
--	 （2）从表1中得到最低工资的那个，然后寻找那个部门，得到部门号
--	 （3）根据部门号，得到最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=(	SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary)=(SELECT MIN(max_s)
					    FROM (SELECT department_id,MAX(salary) max_s
						  FROM employees
						  GROUP BY department_id) table1
					    )
		    );

#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

-- 思路
-- （1）得表1
-- （2）通过max=avg，得最高工资部门id
-- （3）通过departments得manager_id，通过employee_id = manager_id 得*

SELECT *
FROM employees
WHERE employee_id = (
			SELECT manager_id
			FROM departments
			WHERE department_id = (
			
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) =(	SELECT MAX(sal)
									FROM(	SELECT AVG(salary) sal
									FROM employees
									WHERE department_id IS NOT NULL
									GROUP BY department_id
									) table1
						)
			
			
			)

);
		
-- 内核
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(	SELECT MAX(sal)
			FROM(	SELECT AVG(salary) sal
				FROM employees
				WHERE department_id IS NOT NULL
				GROUP BY department_id
			) table1
);

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

SELECT DISTINCT department_id 
FROM employees
WHERE job_id != 'ST_CLERK' AND department_id IS NOT NULL;

#16. 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees
WHERE manager_id IS NULL;

#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.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

-- （1）生成表1为各部门平均工资
-- （2）取要求列表，筛选条件：每个人根据自己的部门号，和对应的部门平均工资比较，取大的

SELECT employee_id,last_name,salary
FROM employees e
WHERE salary>(	SELECT av_sal
		FROM(	SELECT department_id,AVG(salary) av_sal
			FROM employees
			WHERE department_id IS NOT NULL
			GROUP BY department_id
		     ) table1
		WHERE department_id = e.department_id
	     );


#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

-- （1）表1
-- （2）看数量比较，得大于5的
-- （3）通过id得到部门名称

SELECT *
FROM departments
WHERE department_id IN (SELECT d_id
FROM (	SELECT department_id d_id,COUNT(1) cnt
	FROM employees
	GROUP BY department_id
     ) table1
WHERE cnt > 5);

-- 答案，更巧
SELECT department_name,department_id
FROM departments d
WHERE 5 < (
	SELECT COUNT(*)
	FROM employees e
	WHERE d.`department_id` = e.`department_id`
	);


#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

-- 部门坐落在location_id上，每个国家包含多个location
-- 按照国家对location进行分组
-- 部门归location管，location归country_id管，即数量=cnt2*cnt1 > 2时选择country_id（count可能为0）


SELECT l.country_id
FROM locations l,departments d
WHERE l.location_id = d.location_id
GROUP BY country_id
HAVING COUNT(1) > 2;

-- 答案:
SELECT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE l.`location_id` = d.`location_id`
);
# 注:该答案不严谨，虽然能跑出正确的国家名称，但是逻辑是错误的；
# 例如，将题目中的部门数改为大于0，那么会出现很多个us和uk等，是不严谨的；
# 我写的那个，也就是上面那个，不会出现这种问题