Mysql8.0習題系列（五）：子查詢（一篇搞懂子查詢做題技巧，超詳細）
本文已參與「新人創作禮」活動，一起開啟掘金創作之路
Mysql8.0習題系列
軟件下載地址 提取碼：7v7u 數據下載地址 提取碼：e6p9
子查詢
1.查詢和Zlotkey相同部門的員工姓名和工資
首先我們查出Zlotkey所在的部門，再放入子查詢中進行選擇
sql
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees
WHERE last_name = 'Zlotkey'
);
2.查詢工資比公司平均工資高的員工的員工號，姓名和工資。
先找出平均工資
sql
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
sql
SELECT last_name, job_id, salary FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SA_MAN');
4.查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
sql
SELECT employee_id, last_name
FROM employees
WHERE job_id IN (SELECT job_id FROM employees
WHERE last_name LIKE '%u%');
5.查詢在部門的location_id為1700的部門工作的員工的員工號
sql
SELECT
employee_id
FROM
employees
WHERE department_id IN
(SELECT
department_id
FROM
departments WHERE location_id = 1700);
6.查詢管理者是King的員工姓名和工資
sql
SELECT last_name, salary
FROM employees
WHERE manager_id IN
(SELECT employee_id FROM employees WHERE last_name = 'King'
);
7.查詢工資最低的員工信息: last_name, salary
sql
SELECT last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
8.查詢平均工資最低的部門信息
sql
SELECT * FROM departments
WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id
ORDER BY AVG(salary) LIMIT 0,1);
9.查詢平均工資最低的部門信息和該部門的平均工資（相關子查詢）
sql
SELECT d.*, avg_sl.avg_dep FROM departments d,(SELECT department_id, AVG(salary) avg_dep FROM employees GROUP BY department_id
ORDER BY AVG(salary)) avg_sl
WHERE d.`department_id` = avg_sl.department_id
LIMIT 1
;
10.查詢平均工資最高的 job 信息
sql
SELECT * FROM jobs WHERE job_id = (SELECT job_id FROM employees GROUP BY job_id
ORDER BY AVG(salary) LIMIT 1);
11.查詢平均工資高於公司平均工資的部門有哪些?
sql
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 的詳細信息
sql
SELECT * FROM employees WHERE employee_id
IN (SELECT DISTINCT manager_id FROM employees);
13.各個部門中 最高工資中最低的那個部門的 最低工資是多少?
sql
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id
ORDER BY MAX(salary) ASC LIMIT 1;
14.查詢平均工資最高的部門的 manager 的詳細信息: last_name, department_id, email, salary
sql
SELECT last_name, department_id, email,salary
FROM employees
WHERE manager_id = (SELECT DISTINCT employee_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC
LIMIT 1);
15. 查詢部門的部門號，其中不包括job_id是"ST_CLERK"的部門號
sql
SELECT department_id
FROM departments d
WHERE department_id NOT IN (
SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK'
);
16. 選擇所有沒有管理者的員工的last_name
```sql
方法1：
SELECT last_name FROM employees WHERE employee_id = (SELECT employee_id FROM employees WHERE manager_id IS NULL);
SELECT last_name FROM employees e1 WHERE NOT EXISTS ( SELECT * FROM employees e2 WHERE e1.manager_id = e2.employee_id ); ```
17．查詢員工號、姓名、僱用時間、工資，其中員工的管理者為 'De Haan'
sql
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id = (
SELECT employee_id FROM employees WHERE last_name = 'De Haan'
);
18.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資（相關子查詢）
sql
SELECT employee_id, last_name, salary
FROM employees e, (SELECT department_id, AVG(salary) av_dep
FROM employees GROUP BY department_id) e2
WHERE e.`department_id` = e2.department_id
AND e.`salary` > av_dep;
19.查詢每個部門下的部門人數大於 5 的部門名稱（相關子查詢）
方法一
sql
SELECT department_name
FROM departments
WHERE department_id
IN (SELECT department_id
FROM employees GROUP BY department_id
HAVING COUNT(*) > 5);
方法二
sql
SELECT department_name,department_id
FROM departments d
WHERE 5 < (
SELECT COUNT(*)
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
20.查詢每個國家下的部門個數大於 2 的國家編號（相關子查詢）
sql
SELECT country_id
FROM locations l
WHERE 2 <(
SELECT COUNT(*) FROM departments d
WHERE l.`location_id` = d.`location_id`);