MySQL_Z9子查询

【选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary】
select last_name, job_id, salary
from employees 
where salary > all (
		select salary
		from employees
		where JOB_ID = 'SA_MAN'
);

【查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名】
select employee_id, last_name
from employees
where department_name = any(select department_name
						from employees
						where last_name like %u%);

【查询平均工资最低的部门信息】
#方式一：
select *
from departments
where department_name = (select department_name
		from employees
		group by department_name
		having avg(salary) = (
							select min(avg_sal)
							from (	
									select avg(salary) avg_sal
									from employees
									group by  department_name) ta_avg_sal
								)
							)
		);
#方式二：
select *
from departments
where department_name = (select department_name
		from employees
		group by department_name
		having avg(salary) <= all(select avg(salary) avg_sal
									from employees
									group by  department_name)
		);

#方式三：
select *
from departments
where department_name = (select department_name
		from employees
		group by department_name
		having avg(salary) = (select avg(salary) avg_sal
									from employees
									group by  department_name
									order by avg_sal asc
									limit 0,1)
		);

#方式四：
select *
from departments d join 
		(select department_name, avg(salay) avg_sal
		from employees
		group by department_name
		order by avg_sal asc
		limit 0,1) ta_de_avg
on d.department_name = ta_de_avg.department_name;

【查询平均工资最低的部门信息和该部门的平均工资（相关子查询）】
select d.*, (select avg(salary) from employees where department_name = d.department_name) ta_avg
from departments d
where department_name = (
						select department_name
						from employees
						group by department_name
						having avg(salary) <= (	select avg(salary)
											from employees 
											group by department_name)
						);

【各个部门中  最高工资中最低的那个部门  的最低工资是多少?】
select min(salary)
from employees
group by department_id
where department_id = (
						select department_id
						from employees
						group by department_id
						having max(salary) <= all( 
													select max(salary)
													from employees
													group by department_id);
						)

【查询平均工资最高的部门的manager 的详细信息: last_name, department_id, email, salary】
select last_name, department_id, email, salary
from employees
where manager_id in (
					select distinct manager_id
					from employees
					where department_id = (
											select department_id
											from employees
											group by department_id
											having avg(salary) = (select max(av_sal)
																	from (
																			select avg(salary) av_sal
																			from employees
																			group by department_id) t_sal
																	)
														)
	);

!!!【选择所有没有管理者的员工的last_name】
select last_name
from employees em
where not exists (
					select *
					from employees mag
					where em.employee_id = mag.manager_id
					);

【查询员工号、姓名、雇用时间、工资，其中员工的管理者为'De Haan'】
select employee_id, last_name, hire_date, salary
from employees
where manager_id in (select employee_id
					from employees
					where last_name = 'De Haan');

【查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）】
select employee_id, last_name, salary
from employees e1
where salary > (
				select avg(salary)
				from employees e2
				where department_id = e.department_id
				);

select e.employee_id, e.last_name, e.salary
from employees e, (select department_id, avg(salary) avg_sal
					from employees
					group by department_id) t_avg_sal
where e.department_id = t_avg_sal.department_id 
and e.salary > t_avg_sal.avg_sal

！！！【查询每个部门下的部门人数大于5 的部门名称（相关子查询）】
#子查询
select department_name
from departments d, (	select department_id, count(employee_id) co
						from employees 
						group by department_id
						having co > 5)  tab_coun
where d.department_id = tab_coun.department_id
#相关子查询
select department_name
from departments d
where 5< (
			select count(*)
			from employees e
			where d.department_id  = e.department_id