[打卡]2024.5.14子查询

-- 单行子查询

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

-- 习题：查询工资大于149号员工工资的员工信息
SELECT employee_id,last_name,salary
from employees
where (
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
);

/*
子查询编写技巧（步骤）
1.先从里往外写
2.再从外往里写
*/
-- 中英互换

-- 习题：返回公司工资最少的员工的 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;

-- 习题：查询最低工资大于60号部门最低工资的部门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
);