子查询（二）

子查询（二）

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

SELECT department_id from  employees 
where department_id IS not null
GROUP BY department_id HAVING AVG(salary) > (
select AVG(salary) from employees )

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

#方式一
SELECT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
from employees e1 ,
(select DISTINCT manager_id from employees) manager
where e1.employee_id = manager.manager_id;


#方式二     (自连接)
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
from employees e1, employees e2
WHERE e1.employee_id = e2.manager_id

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

SELECT min(salary) from employees e,
     ( SELECT MAX(salary) mx,department_id 
       from employees GROUP BY department_id 
       ORDER BY mx LIMIT 1) t_max
where e.department_id = t_max.department_id;

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

#分三步分析
# 1、 查询平均工资最高的部门的department_id 
SELECT AVG(salary) ag ,department_id from employees GROUP BY department_id  ORDER BY ag desc LIMIT 1  #department_id = 90

# 2、查询平均工资最高的部门的 manager的员工号
SELECT * from departments where department_id = 90  # manager_id = 100

# 3、manager的详细信息
SELECT last_name, department_id, email, salary from employees where employee_id = 100


#最后合成语句（最终结果）
SELECT last_name, department_id, email, salary 
from employees where employee_id =
	( SELECT manager_id from departments d ,
		   ( SELECT AVG(salary) ag ,department_id 
		     from employees GROUP BY department_id  
		     ORDER BY ag desc LIMIT 1 ) agv
	  WHERE d.department_id =  agv.department_id);

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

SELECT DISTINCT department_id from departments WHERE department_id not in
(SELECT department_id FROM employees WHERE job_id = "ST_CLERK")；

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

SELECT last_name from employees WHERE manager_id IS null

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

#方式一
SELECT e.employee_id,e.last_name,e.hire_date,e.salary,e.manager_id from employees e, 
	( SELECT employee_id eid,last_name from employees 
	  WHERE last_name like "De Haan") t_manager
WHERE e.manager_id = t_manager.eid;


#方式二  (自连接)
SELECT e.employee_id,e.last_name,e.hire_date,e.salary,e.manager_id 
from employees e,employees e1
WHERE e.manager_id = e1.employee_id and e1.last_name like "De Haan";

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

# 方式一   （from 后面声明子查询）
SELECT e.employee_id , e.last_name, e.salary FROM employees e,
	( SELECT AVG(salary) ag, department_id from employees 
	  GROUP BY department_id) t_ag_sal
WHERE e.salary > t_ag_sal.ag and e.department_id = t_ag_sal.department_id；


#方式二   （相关子查询）
SELECT e1.employee_id , e1.last_name, e1.salary 
FROM employees e1 where salary > (
	SELECT avg(salary) FROM employees e2 
	where department_id = e1.department_id
);

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

#方式一   (from 后面声明子查询)
SELECT d.department_name FROM departments d ,
	( SELECT COUNT(*) a,department_id from employees 
	  GROUP BY department_id HAVING a > 5) t_coun
WHERE d.department_id = t_coun.department_id;


#方式二   （相关子查询）
SELECT d.department_name FROM departments d where 5 < 
	( SELECT COUNT(*) from employees e
	  WHERE d.department_id = e.department_id);

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

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

如果是子查询的话，通常都是从外往里写

---