【MySQL练习】子查询练习

数据库表数据：https://www.cnblogs.com/zhishu/p/16452950.html

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>(
		SELECT MAX(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 employee_id,last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name REGEXP '[u]'
);

#方式二
SELECT employee_id, last_name
FROM employees
WHERE department_id = ANY(
		SELECT DISTINCT department_id
		FROM employees
		WHERE last_name LIKE '%u%'
);

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

用多表查询join也可以

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 department_id,department_name
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary) = (
		SELECT MIN(dept_avg_sal)
		FROM (
			SELECT AVG(salary) AS dept_avg_sal
			FROM employees
			GROUP BY department_id
		) AS t_avg
	)
);

其他几种方式

点击查看代码

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

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

#方式一
SELECT a.*,(SELECT AVG(salary) FROM employees WHERE department_id = a.department_id) AS avg_sal
FROM departments a
WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary) = (
				SELECT MIN(dept_avg_sal)
				FROM (
						SELECT AVG(salary) AS dept_avg_sal
						FROM employees
						GROUP BY department_id
				) AS t_avg
		)
);

#方式二
SELECT b.*,AVG(salary) AS avg_sal
FROM employees a
JOIN departments b ON a.department_id = b.department_id
GROUP BY department_id
HAVING avg_sal = (
		SELECT MIN(dept_avg_sal)
		FROM (
				SELECT AVG(salary) AS dept_avg_sal
				FROM employees
				GROUP BY department_id
		) AS t_avg
)

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

SELECT * 
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
				SELECT MAX(job_avg_sal)
				FROM (
						SELECT AVG(salary) job_avg_sal
						FROM employees
						GROUP BY job_id
				) AS job_sal
		)
);

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 employee_id,last_name,salary
FROM employees
WHERE employee_id IN(
		SELECT DISTINCT manager_id
		FROM employees
);

#方式二
SELECT DISTINCT a.employee_id,a.last_name,a.salary
FROM employees a 
JOIN employees b 
WHERE a.employee_id = b.manager_id;

#方式三
SELECT employee_id,last_name,salary
FROM employees a
WHERE EXISTS (
	SELECT * 
	FROM employees b
	WHERE a.employee_id = b.manager_id
);

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

各部门最高工资->取最小的最高工资->和各部门最高工资比较，相等的就是那个部门->查这个部门的最低工资

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

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

SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id = (
		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
				)
		)
		AND manager_id IS NOT NULL
);

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

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

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

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

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

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e2.department_id = e1.`department_id`
);

SELECT employee_id,last_name,salary
FROM employees a,
(SELECT department_id,AVG(salary) avg_sal
FROM employees b GROUP BY department_id) dept_avg_sal
WHERE a.department_id = dept_avg_sal.department_id
AND a.salary>dept_avg_sal.avg_sal;

19.查询每个部门下的部门人数大于 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 的国家编号（相关子查询）

#方式一：是我写的
SELECT country_id
FROM countries c
WHERE 2<(
	  SELECT COUNT(*) AS dept_num
	  FROM departments a
	  JOIN locations b ON a.location_id = b.location_id
	  WHERE c.country_id = b.country_id
	  GROUP BY country_id
);

#方式二
SELECT country_id
FROM locations l
WHERE 2 < (
	SELECT COUNT(*)
	FROM departments d
	WHERE l.`location_id` = d.`location_id`
);