oracle学习68-oracle之数据处理之课后练习

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 e
		where department_id = (
      			select department_id
      			from employees e
      			where employee_id = 108
		) and salary = (
      			select min(salary)
      			from employees
      			where department_id = e.department_id
		)