子查询案例讲解

#1.查询和Zlotkay相同部门的员工姓名和工资
#(1)查询姓名为Zlotkey的部门编号
SELECT `department_id`
FROM `employees`
WHERE `last_name`='Zlotkey';
#查询部门编号与(1)相等的员工姓名和工资(标量子查询)
SELECT `last_name`,`salary`,`department_id`
FROM `employees`
WHERE `department_id`=(
SELECT `department_id`
FROM `employees`
WHERE `last_name`='Zlotkey'
);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
#(1)查询平均工资
SELECT AVG(`salary`)
FROM `employees`;
#(2)查询满足条件(1)的员工号，姓名和工资(标量)
SELECT `department_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary`>(
SELECT AVG(`salary`)
FROM `employees`
);

#3.查询各部门中工资比本部门平均工资高的员工的员工号和姓名
#(1)查询各个部门的平均工资
SELECT  `department_id`,AVG(`salary`) 平均工资
FROM `employees`
GROUP BY `department_id`;
#(2)查询满足条件(1)的员工的员工号和姓名
SELECT `department_id`,`last_name`
FROM `employees`
WHERE `salary`>ALL(
SELECT  `department_id`,AVG(`salary`) 平均工资
FROM `employees`
GROUP BY `department_id`
);
#正解
#（2）连接（1）结果集和`employees`表，进行筛选
SELECT `employee_id`,`last_name`,`salary`,e.`department_id`
FROM  `employees` e 
INNER JOIN(
SELECT  `department_id`,AVG(`salary`) 平均工资
FROM `employees`
GROUP BY `department_id`
) ag_dep
ON e.`department_id`=ag_dep.`department_id`
WHERE `salary`>ag_dep.平均工资;


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#(1)查询姓名中包含u的员工的部门
SELECT DISTINCT `department_id`
FROM `employees`
WHERE `last_name` LIKE '%u%';
#（2）查询满足条件（1）的员工的员工号和姓名
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的部门工作的员工的员工号
#(1)查询在部门的location_id为1700的部门工作的员工的部门号
SELECT   DISTINCT `department_id`
FROM `departments`
WHERE `location_id`=1700;
#(2)查询满足条件（1）的员工的员工号
SELECT `employee_id`
FROM `employees`
WHERE `department_id`=ANY(
SELECT   DISTINCT `department_id`
FROM `departments`
WHERE `location_id`=1700
);


#6.查询管理者是K_ing的员工姓名和工资
#(1)查询管理者是K_ing的员工有哪些
SELECT `employee_id`
FROM `employees`
WHERE `last_name`='K_ing';
#（2）查询满足条件（1）的员工姓名和工资
SELECT `last_name`,`salary`
FROM `employees`
WHERE `manager_id`
IN(
SELECT `employee_id`
FROM `employees`
WHERE `last_name`='K_ing'
);


#7.查询工资最高的员工的姓名，要求`first_name`和`last_name`
#显示为一列，列名为姓，名
#(1)查询最高工资
SELECT MAX(`salary`)
FROM `employees`;
#(2)查询工资等于（1）的姓，名
SELECT CONCAT(`first_name`,`last_name`) "姓.名"
FROM `employees`
WHERE `salary`=(
SELECT MAX(`salary`)
FROM `employees`
);