列子查询

#2、列子查询(多行)
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#（1）查询所有员工姓名
SELECT `last_name`
FROM `employees`;
#（2）查询location_id是1400或1700的部门中的所有员工部门编号
SELECT  `department_id`
FROM `departments`
WHERE `location_id`IN(1400,1700);
#综合（1）和（2）
SELECT `last_name`
FROM `employees`
WHERE `department_id`
IN(
SELECT  DISTINCT `department_id`
FROM `departments`
WHERE `location_id`IN(1400,1700)
);


#案例2：返回其他部门中比job_id为‘IT_PROG’部门任一工资低
#的员工的：工号、姓名、job_id以及salary

#(1)查询job_id为‘IT_PROG’部门的工资
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG';


#(2)查询其他工种中比job_id为‘IT_PROG’部门任一工资低的员工的：工号、姓名、job_id以及salary
SELECT `job_id`,`last_name`,`salary`
FROM `employees`
WHERE `salary` < ANY(
SELECT `salary`
FROM `employees`
WHERE `job_id`='IT_PROG'
)AND `job_id` <> 'IT_PROG';

#案例3：f返回其他部门中比job_id为‘IT_PROG’部门所有员工工资都低的员工的
#员工号、姓名、job_id以及salary
#（1）查询job_id为‘IT_PROG’部门的员工的最低工资
SELECT salary
FROM `employees`
WHERE `job_id`='IT_PROG';

#（2）查询员工号、姓名、job_id以及salary满足（1）
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM  `employees`
WHERE `salary`<ALL(
SELECT salary
FROM `employees`
WHERE `job_id`='IT_PROG'
);