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