update語句中使用子查詢

55. 更改 108 員工的信息: 使其工資變為所在部門中的最高工資, job 變為公司中平均工資最低的 job

1). 搭建骨架

update employees set salary = (), job_id = () where employee_id = 108;

2). 所在部門中的最高工資

select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)

3). 公司中平均工資最低的 job

select job_id
from employees
group by job_id
having avg(salary) =  (
select min(avg(salary))
from employees
group by job_id
)

4). 填充

update employees e set salary = (
select max(salary)
from employees
where department_id = e.department_id
), job_id = (
select job_id
from employees
group by job_id
having avg(salary) =  (
select min(avg(salary))
from employees
group by job_id
)
) where employee_id = 108;

56. 刪除 108 號員工所在部門中工資最低的那個員工.
1). 查詢 108 員工所在的部門 id

select department_id
from employees 
where employee_id = 108;

2). 查詢 1) 部門中的最低工資

select min(salary)
from employees
where department_id = (
select department_id
from employees 
where employee_id = 108;
)

3). 刪除 1) 部門中工資為 2) 的員工信息

delete from employees where salary = (
select min(salary)
from employees
where department_id = (
select department_id
from employees 
where employee_id = 108
)
) and department_id = (
select department_id
from employees 
where employee_id = 108
)