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

时间:2023-02-12 07:55:53


/*************************************************************************************************/ 
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