Mysql8.0習題系列(五):子查詢(一篇搞懂子查詢做題技巧,超詳細)

語言: CN / TW / HK

本文已參與「新人創作禮」活動,一起開啟掘金創作之路

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