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