[打卡]Aloe0514 --子查询02

#子查询的分类
#角度1.从内查询返回的结果条目数量
#单行子查询    /    多行子查询
#角度2.内查询是否被执行多次
#相关子查询    /     不相关子查询

-- 单行子查询

#单行子查询的操作符: =(等于)  !=(<>)(不等于) >(大于) <(小于) >=(大于等于) <+(小于等于)
-- Q1.查询工资大于149号员工的信息
SELECT * 
FROM employees
WHERE salary > (
								SELECT salary
								FROM employees 
								WHERE employee_id = 149
								);
-- 多个子查询
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
							);
-- 子查询不能再进行子查询，可以使用 AND 进行查询							
							
-- Q2.返回公司工资最少的员工的信息
SELECT last_name,job_id,salary
FROM employees
GROUP BY salary
HAVING salary = (SELECT MIN(salary)
								 FROM employees
								 );

#Q3.查询与141号员工的 manager_id,department_id相同的其他的员工信息
#1.
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;
#2.(效率较低)
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;																		

#Q4. 查询最低工资大于110号部门最低工资的部门id和最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
											SELECT MIN(salary)
											FROM employees 
											WHERE department_id = 110
											);

-- 非法使用子查询

#1.子查询返回数量超过一条
SELECT employee_id,last_name
FROM employees 
WHERE salary = (
								SELECT MIN(salary)
								FROM employees
								GROUP BY department_id
								);