[打卡]msq 子查询

# 单行子查询

# 单行操作符= !=
SELECT last_name,salary,department_id
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
);
# 中英互换
# 习题：返回公司工资最少的员工的last_name，job_id和salary

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

SELECT manager_id,department_id,employee_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 manager_id,department_id,employee_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 MIN(salary)
FROM employees
WHERE department_id = 110

#非法使用子查询

SELECT employee_id,last_name
from employees
WHERE salary (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);