9. 子查询练习题

1.查询和Zlotkey相同部门的员工姓名和工资

select first_name , last_name , salary 
from employees e 
where department_id = (select department_id
						from employees e2
						where last_name = 'Zlotkey'
						);

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id , first_name , last_name , salary 
from employees e 
where salary > (select avg(salary)
				from employees e2
				);

3.选择工资大于所有JOB_ID='SA_MAN'的员工的工资的员工的last_name， job_id, salary

select last_name , job_id , salary 
from employees e 
where salary > all (select salary
					from employees e2
					where job_id = 'SA_MAN'
					);

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓

select employee_id , last_name 
from employees e 
where department_id in (select department_id
						from employees e2
						where last_name like '%u%'
						);

5.查询在部门的location_id为1700的部门工作的员工的员工号

select e.employee_id 
from employees e join departments d 
on e.department_id = d.department_id 
where d.location_id = 1700;

select employee_id 
from employees e 
where department_id in (select department_id
						from departments d
						where location_id = 1700
						);

6.查询管理者是King的员工姓名和工资

select e.first_name , e.last_name , e.salary 
from employees e join employees e2 
on e.manager_id = e2.employee_id 
where e2.last_name = 'King';

select last_name , salary 
from employees e 
where manager_id  in (select employee_id 
						from employees e2 
						where last_name = 'King'
						);

7.查询工资最低的员工信息:last_name，salary

select last_name , salary 
from employees e 
where salary <= (select min(salary)
				from employees e2
				);

8.查询平均工资最低的部门信息

select department_id , avg(salary) avg_salary 
from employees e 
group by department_id 
having avg_salary <= all(select avg(salary)
					     from employees e2 
					     group by department_id 
					     );
	
select *
from departments d
where department_id = (
						select department_id 
						from employees e 
						group by department_id 
						having avg(salary) <= all(select avg(salary)
												  from employees e2 
												  group by department_id 
												  )
					   );

9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

select department_id , avg(salary) avg_salary 
from employees e 
group by department_id 
having avg_salary <= all(select avg(salary)
					  from employees e2 
					  group by department_id 
					  );
				
					 
select d.*, t_avg_salary.avg_salary
from departments d join (
						select department_id , avg(salary) avg_salary
						from employees e 
						group by department_id 
						having avg_salary <= all(select avg(salary)
												  from employees e2 
												  group by department_id 
												  )
						) t_avg_salary
on d.department_id = t_avg_salary.department_id;

select d.*, t_avg_salary.avg_salary
from departments d join (
						select department_id , avg(salary) avg_salary
						from employees e 
						group by department_id 
						order by avg_salary
						limit 1
						) t_avg_salary
on d.department_id = t_avg_salary.department_id;

10.查询平均工资最高的 job 信息

select job_id , avg(salary) avg_salary 
from employees e 
group by job_id 
order by avg_salary DESC
limit 1;

11.查询平均工资高于公司平均工资的部门有哪些?

select department_id 
from employees e 
group by department_id 
having avg(salary) > (select avg(salary)
					  from employees e2
					  );

12.查询出公司中所有 manager 的详细信息

select distinct *
from employees e join employees e2 
on e.manager_id = e2.employee_id ;

select distinct e2.*
from employees e join employees e2 
on e.manager_id = e2.employee_id ;

13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

## 理解错意思了
select max(salary) max_salary 
from employees e 
group by department_id 
having max_salary <= all (select max(salary)
						  from employees e2
						  group by department_id
						  );

## 方式一
select min(salary)
from employees e 
group by department_id 
having department_id = (
						select department_id 
						from employees e1
						group by department_id 
						having max(salary) <= all (select max(salary)
												  from employees e2
												  group by department_id
						  							)
						)
		
## 方式二，当然里面也可以用limit
select min(salary)
from employees e , (
						select department_id 
						from employees e1
						group by department_id 
						having max(salary) <= all (select max(salary)
												  from employees e2
												  group by department_id
						  							)
						)t_dep_id
where e.department_id = t_dep_id.department_id;

14,查询平均工资最高的部门的 manager 的详细信息:last_name，department_id,email，salary

select last_name , department_id , email , salary 
from employees e
where employee_id = (select manager_id 
					from employees e1 
					where manager_id is not null
					group by department_id 
					having avg(salary) >= all(select avg(salary)
											  from employees e2
											  where manager_id is not null
											  group by department_id
											  )
					);
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees e, (
        SELECT department_id, AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal DESC
        LIMIT 0,1
    ) t_dept_avg_sal
    WHERE e.`department_id` = t_dept_avg_sal.`department_id`
);

15，査询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

select department_id 
from employees e 
group by department_id
having department_id not in (select department_id
						     from employees e2
							 where job_id = 'ST_CLERK'
							 );

16，选择所有没有管理者的员工的last_name

select last_name 
from employees e 
where manager_id is null;

17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为'De Haan'

select employee_id , first_name , hire_date , salary 
from employees e 
where manager_id = (select employee_id
					from employees e2
					where last_name = 'De Haan'
					);

18.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资(相关子査询)

select e.employee_id , e.last_name , e.salary 
from employees e join (select employee_id, department_id , avg(salary) avg_salary 
					   from employees e2
					   group by department_id) e1
on e.employee_id = e1.employee_id
where e.salary > e1.avg_salary;

19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询)

select department_name 
from departments d 
where department_id in (select department_id 
						from employees e 
						group by department_id 
						having count(employee_id) > 5
						);
					
SELECT department_name
FROM departments d
WHERE 5 < (
	SELECT COUNT(*)
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

20.查询每个国家下的部门个数大于 2的国家编号(相关子查询)

select country_id 
from locations l join departments d 
on l.location_id = d.location_id 
group by country_id 
having count(department_id) > 2;

select l.*
from locations l 
where 2 < (select count(*) 
			from departments d
			where l.location_id = d.location_id
		  );