[打卡]美味的sql之 我的烤盘饭吃到吐

#子查询

#需求：谁的工资比Abel的高！

#方法1：
SELECT salary
from employees
WHERE last_name='Abel';

SELECT last_name, salary
FROM employees
WHERE salary>11000;
#方式2：自连接

SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.salary>e1.salary #多表连接条件
AND e1.last_name='Abel';

#方式3：子查询
SELECT last_name, salary
FROM employees
WHERE salary>(
SELECT salary
from employees
WHERE last_name='Abel'

);

#称谓的规范：分为外查询(或主查询)、内查询（或子查询）

/*
子查询（或内查询）在主查询之前一次执行完成

子查询的结果被主查询（或外查询）使用

注意事项：子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询


*/

SELECT last_name, salary
FROM employees
WHERE (
SELECT salary
from employees
WHERE last_name='Abel'

)
/*
子查询的分类

角度一：从内查询返回的结果条目数
单行子查询 vs 多行子查询 （工资大于6000的姓名）

角度二: 内查询是否被执行多
相关子查询 vs 不相关子查询
如：
相关子查询的需求:查询工资大于本部门平均工资的员工信息
不相关子查询的需求：查询工资大于本公司平均工资的员工信息


*/
#单行子查询

#单行子查询操作符：= ! = <> > >= <=

#习题：查询工资大于149号员工工资的员工信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(

SELECT salary
FROM employees
WHERE employee_id = 149
);

#习题：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary,job_id
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
AND salary>(

SELECT salary
FROM employees
WHERE employee_id=143

)
);
/*
子查询编写技巧
1. 从里往外写

2. 从外往里写
*/

#中英文互换

#习题：返回工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
from employees
);

#习题：查询与141号员工的manger_id和department_id相同的employee_id,manger_id和department_id

SELECT 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;


SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) =(
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141
)
AND employee_id!=141;

#习题:查询最低工资大于110号部门最低部门最低工资的部门id和其最低工资


SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING min(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=60
)
AND department_id is not null;

#非法使用子查询



SELECT employee_id,last_name
FROM employees
WHERE salary=(

SELECT MIN(salary)
FROM employees
GROUP BY department_id
);