| /*************************************************************************************************/ |
| 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 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)