#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`
);
子查询案例讲解
最新推荐文章于 2024-03-30 21:55:51 发布