-- 子查询(嵌套查询)
数据准备
链接:https://pan.baidu.com/s/1SlQxvy96xVPkGdg2gTAY8w
提取码:18n4
-- 谁的工资比Abel的工资高?SELECT last_name, salary
FROM employees
WHERE salary >(SELECT salary
FROM employees
WHERE last_name ='Abel');-- 子查询分类/*
1.单行子查询(子查询结果只有一个值) VS 多行子查询(子查询的结果有多个)
2.内查询是否被执行多次:相关子查询 VS 不相关子查询
*/-- 单行子查询 (从里往外写,从外往里写)-- 查询工资大于149号员工工资的员工信息SELECT last_name, salary
FROM employees
WHERE salary >(SELECT salary
FROM employees
WHERE employee_id =149);-- 返回job_id与141号员工相同,工资比143号员工多的员工姓名,job_id和工资SELECT last_name, job_id, salary
FROM employees
WHERE job_id =(SELECT job_id
FROM employees
WHERE employee_id =141)AND salary >(SELECT salary
FROM employees
WHERE employee_id =143);-- 返回公司工资最少的员工的信息SELECT last_name, salary
FROM employees
WHERE salary =(SELECTMIN(salary)FROM employees
);-- 题目:查询与141员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_idSELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id =(SELECT manager_id
FROM employees
WHERE employee_id =141)AND department_id =(SELECT department_id
FROM employees
WHERE employee_id =141)AND employee_id <>141;-- having中的子查询-- 查询最低工资大于110号部门的最低工资的部门id和其最低工资SELECT department_id,MIN(salary)FROM employees
WHERE department_id ISNOTNULLGROUPBY department_id
HAVINGMIN(salary)>(SELECTMIN(salary)FROM employees
WHERE department_id =110);-- 题目:显式员工的employee_id,last_name和location。-- 其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。SELECT employee_id,
last_name,(CASE department_id
WHEN(SELECT department_id
FROM departments
WHERE location_id =1800)THEN'Canada'ELSE'USA'END)`location`FROM employees;-- 5.多行子查询-- 多行子查询的操作符 in any all some-- 题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salarySELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ANY(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';-- 题目:返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salarySELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <ALL(SELECT salary
FROM employees
WHERE job_id ='IT_PROG')AND job_id <>'IT_PROG';-- 查询平均工资最低的部门idSELECT department_id
FROM employees
GROUPBY department_id
HAVINGAVG(salary)<=ALL((SELECTAVG(salary)FROM employees
GROUPBY department_id));-- 6.相关子查询-- 题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_idSELECT last_name, salary, department_id
FROM employees e1
WHERE salary >(SELECTAVG(salary)FROM employees
WHERE department_id = e1.department_id
);-- 若employees表中employee_id与job_history表中employee_id相同的数目不小于2,-- 输出这些相同id的员工的employee_id,last_name和其job_idSELECT employee_id, last_name, job_id
FROM employees e
WHERE2<=(SELECTCOUNT(*)FROM job_history j
WHERE j.employee_id = e.employee_id
);-- exist / not exist;-- 题目:查询公司管理者的employee_id,last_name,job_id,department_id信息-- 自连接SELECTDISTINCT e2.employee_id, e2.last_name, e2.job_id, e2.department_id
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;-- 子查询SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN(SELECTDISTINCT manager_id
FROM employees);-- existsSELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHEREEXISTS(SELECT*FROM employees e2
WHERE e1.employee_id = e2.manager_id
);-- 课后练习#1.查询和Zlotkey相同部门的员工姓名和工资SELECT last_name, salary
FROM employees
WHERE department_id =(SELECT department_id
FROM employees
WHERE last_name ='Zlotkey');#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。SELECT employee_id, last_name, salary
FROM employees
WHERE salary >(SELECTAVG(salary)FROM employees
);#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salarySELECT last_name, job_id, salary
FROM employees
WHERE salary >ALL(SELECT salary
FROM employees
WHERE job_id ='SA_MAN');#4.查询和姓名 中包含字母u 的员工在相同部门的员工 的员工号和姓名SELECT employee_id, last_name
FROM employees e
WHERE department_id =ANY(SELECTDISTINCT department_id
FROM employees e2
WHERE e2.last_name LIKE'%u%');#5.查询在部门的location_id为1700的部门工作的员工的员工号SELECT employee_id
FROM employees
WHERE department_id IN(SELECT department_id
FROM departments
WHERE location_id =1700);#6.查询管理者是King的员工姓名和工资SELECT last_name, salary
FROM employees
WHERE manager_id IN(SELECT employee_id
FROM employees
WHERE last_name ='King');#7.查询工资最低的员工信息: last_name, salarySELECT last_name, salary
FROM employees
WHERE salary <=ALL(SELECT salary
FROM employees
);#8.查询平均工资最低的部门 信息SELECT*FROM departments
WHERE department_id =(SELECT department_id
FROM employees
GROUPBY department_id
HAVINGAVG(salary)<=ALL(SELECTAVG(salary)FROM employees
GROUPBY employees.department_id
));#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)SELECT d.*,(SELECTAVG(salary)FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id =(SELECT department_id
FROM employees
GROUPBY department_id
HAVINGAVG(salary)<=ALL(SELECTAVG(salary) avg_sal FROM employees GROUPBY department_id));#10.查询平均工资最高的 job 信息SELECT*FROM jobs
WHERE job_id =(SELECT job_id
FROM employees
GROUPBY job_id
HAVINGAVG(salary)>=ALL(SELECTAVG(salary)FROM employees
GROUPBY job_id
));#11.查询平均工资高于公司平均工资的部门有哪些?SELECT department_id
FROM employees
WHERE department_id ISNOTNULLGROUPBY department_id
HAVINGAVG(salary)>(-- 公司平均工资SELECTAVG(salary)FROM employees
);#12.查询出公司中所有 manager 的详细信息SELECT*FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?SELECTMIN(salary)FROM employees
WHERE department_id =(SELECT department_id
FROM employees
GROUPBY department_id
HAVINGMAX(salary)<=ALL(SELECTMAX(salary)FROM employees
GROUPBY department_id
));