oracle学习74-oracle之单行函数之子查询之课后练习

/*************************************************************************************************/	
40. 谁的工资比 Abel 高?
		
		1). 写两条 SQL 语句.
		
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		
		--返回值为 11000
		
		SELECT last_name, salary
		FROM employees
		WHERE salary > 11000
		
		2). 使用子查询 -- 一条 SQL 语句
		
		SELECT last_name, salary
		FROM employees
		WHERE salary > (
			SELECT salary
			FROM employees
			WHERE last_name = 'Abel'
		)
		
子查询注意: 
		
		1). 子查询要包含在括号内
		2). 将子查询放在比较条件的右侧	
 
 
41. 查询工资最低的员工信息: last_name, salary	
 
42. 查询平均工资最低的部门信息
 
43*. 查询平均工资最低的部门信息和该部门的平均工资
 
44. 查询平均工资最高的 job 信息
 
45. 查询平均工资高于公司平均工资的部门有哪些?
 
46. 查询出公司中所有 manager 的详细信息.
 
47. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
 
48. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
 
49. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
 
 
/*************************************************************************************************/
		
41. 查询工资最低的员工信息: last_name, salary	
 
		SELECT last_name, salary
		FROM employees
		WHERE salary = (
			SELECT min(salary)
			FROM employees
		)
 
42. 查询平均工资最低的部门信息
		
		SELECT *
		FROM departments
		WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id 
			HAVING avg(salary) = (
				SELECT min(avg(salary))
				FROM employees
				GROUP BY department_id
			) 
		)
 
43. 查询平均工资最低的部门信息和该部门的平均工资
 
select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d
where d.department_id = (
      SELECT department_id
      FROM employees
      GROUP BY department_id 
      HAVING avg(salary) = (
			 SELECT min(avg(salary))
			 FROM employees
			 GROUP BY department_id
			  ) 
      )
		
44. 查询平均工资最高的 job 信息
 
	1). 按 job_id 分组, 查询最高的平均工资	
	SELECT max(avg(salary))
	FROM employees
	GROUP BY job_id
	
	2). 查询出平均工资等于 1) 的 job_id
	SELECT job_id
	FROM employees
	GROUP BY job_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY job_id
	)
	
	3). 查询出 2) 对应的 job 信息
	SELECT *
	FROM jobs
	WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY job_id
		)
	)
 
45. 查询平均工资高于公司平均工资的部门有哪些?
 
	1). 查询出公司的平均工资
	SELECT avg(salary)
	FROM employees
	
	2). 查询平均工资高于 1) 的部门 ID
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) > (
		SELECT avg(salary)
		FROM employees
	)
	
 
46. 查询出公司中所有 manager 的详细信息.
	1). 查询出所有的 manager_id
	SELECT distinct manager_id
	FROM employeess
	
	2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
	SELECT employee_id, last_name
	FROM employees
	WHERE employee_id in (
		SELECT distinct manager_id
		FROM employees
	)
	
	
47. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
	1). 查询出各个部门的最高工资
	SELECT max(salary)
	FROM employees
	GROUP BY department_id
	
	2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)
	SELECT min(max(salary))
	FROM employees
	GROUP BY department_id
	
	3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的 id
	SELECT department_id
	FROM employees
	GROUP BY department_id 
	HAVING max(salary) = (
		SELECT min(max(salary))
		FROM employees
		GROUP BY department_id
	)
	
	4). 查询出 3) 所在部门的最低工资
	SELECT min(salary)
	FROM employees
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id 
		HAVING max(salary) = (
			SELECT min(max(salary))
			FROM employees
			GROUP BY department_id
		)	
	)
 
48. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
	
	1). 各个部门中, 查询平均工资最高的平均工资是多少
	SELECT max(avg(salary))
	FROM employees
	GROUP BY department_id
	
	
	2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY department_id
	)
	
	
	
	3). 查询出 2) 对应的部门的 manager_id
	SELECT manager_id
	FROM departments
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY department_id
		)	
	)
	
	
	4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的 last_name, department_id, email, salary
	SELECT last_name, department_id, email, salary
	FROM employees
	WHERE employee_id = (
		SELECT manager_id
		FROM departments
		WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING avg(salary) = (
				SELECT max(avg(salary))
				FROM employees
				GROUP BY department_id
			)	
		)	
	)
	
 
49. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
 		
		1). 查询出 1999 年来公司的所有的员工的 salary
		SELECT salary
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		2). 查询出 1) 对应的结果的最大值
		SELECT max(salary)
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息		
		SELECT *
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (
			SELECT max(salary)
			FROM employees
			WHERE to_char(hire_date, 'yyyy') = '1999'
		)
		
50. 多行子查询的 any 和 all
 
		select department_id
		from employees
		group by department_id
		having avg(salary) >= any(
		                          --所有部门的平均工资
		                          select avg(salary)
		                          from employees
		                          group by department_id
		                       )
		
any 和任意一个值比较, 所以其条件最为宽松, 所以实际上只需和平均工资最低的比较, 返回所有值
而 all 是和全部的值比较, 条件最为苛刻, 所以实际上返回的只需和平均工资最高的比较, 所以返回
平均工资最高的 department_id