[打卡]子查询2024/5/14

#单行子查询

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

/*
子查询的编写技巧

1.从里往外写

2.从外往里写

*/

#习题：查询工资大于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
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);
#查询与141号员工的manager_id和department_id相同的其他员工的employee_id,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;

#习题：查询最低工资>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
);

#非法使用子查询
SELECT employee_id,last_name
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);