进阶8 子查询------测试题

class 87-99 子查询

--------------------（一）案例讲解：子查询----------------------------------------------

/*
（1）子查询的本质就是创建一个中间表。一个、一列、一行、多行多列，都是表。
（2）本质：在于分析筛选条件
*/

（1）查询和zlotkey相同部门的员工姓名和工资

step1:查询zlotkey的部门

SELECT department_id
FROM employees
WHERE last_name = ‘zlotkey’;

step2:查询部门号=step1的姓名和工资【where后面，标量子查询】

SELECT last_name, salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = ‘zlotkey’
);

（2）查询工资比公司平均工资高的员工的员工号，姓名和工资

step1:查询平均工资

SELECT AVG(salary)
FROM employees;

step2:查询工资>step1的员工号，姓名和工资【where后面，标量子查询】

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

（3）查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

step1:查询各部门的平均工资

SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

step2：连接step1的结果集和employees

SELECT employee_id, last_name, salary
FROM (
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN employees e
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;

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

step1：查询姓名中包含字母u的员工的部门

SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE ‘%u%’;

step2：查询部门号=step1中的任意一个的员工号和姓名【where后面，列子查询】【列子查询：一列多行】

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的部门工作的员工的员工号

step1：查询location_id为1700的部门

SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

step2：查询部门号=step1中的任意一个的员工号【“= any” 等价于 “in”】

SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
);

（6）查询管理者是K_ing的员工姓名和工资

step1：查询姓名为k_ing的员工编号

SELECT DISTINCT employee_id
FROM employees
WHERE last_name = ‘k_ing’;

step2：查询哪个员工的manager_id = (step1)

SELECT last_name, salary
FROM employees
WHERE manager_id IN (
SELECT DISTINCT employee_id
FROM employees
WHERE last_name = ‘k_ing’
);

（7）查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名

step1:查询最高工资

SELECT MAX(salary)
FROM employees;

step2:查询工资=step1的姓.名

SELECT CONCAT(first_name, last_name) ‘姓.名’
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);