oracle学习77-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