Toggle navigation
首页
产品中心
全新RDIF.vNext低代码框架
镇店
.NET C/S开发框架
.NET Web敏捷开发框架
.NET 快速开发框架(全新EasyUI版本)
.NET 代码生成器
.NET WebAPI服务端开发框架
客户案例
付款方式
国思学堂
技术文章
新闻资讯
下载
关于
首页
技术文章
数据库金典
正文
原创
2018-07-13
浏览 (
7910
)
[强烈推荐]ORACLE SQL:经典查询练手第四篇(不懂装懂,永世饭桶!)
本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢! 接上三篇: [[推荐]ORACLE SQL:经典查询练手第一篇](http://www.guosisoft.com/article/detail/80) [[推荐]ORACLE SQL:经典查询练手第二篇](http://www.guosisoft.com/article/detail/66) [[推荐]ORACLE SQL:经典查询练手第三篇](http://www.guosisoft.com/article/detail/65) 本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来! 本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下: ![file](http://doc.rdiframework.net/blog/article/20180713111346129.png-pw) ![file](http://doc.rdiframework.net/blog/article/20180713111400687.png-pw) ER图: ![file](http://doc.rdiframework.net/blog/article/20180713111524267.png-pw) 用SQL完成以下问题列表: ``` 1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。 2. 各个部门中工资大于5000的员工人数。 3. 各个部门平均工资和人数,按照部门名字升序排列。 4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。 5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,显示部门名字、地区名称。 6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)。 7. 哪些员工的工资,介于50号 和80号部门平均工资之间。 8. 所在部门平均工资高于5000 的员工名字。 9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。 10. 最高的部门平均工资是多少。 ``` 各试题解答如下(欢迎大家指出不同的方法或建议!): ``` /*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/ SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资 ,MAX(SALARY) AS 最高工资,MIN(SALARY) AS 最低工资 ,COUNT(*) AS 人数 FROM EMPLOYEES GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID ASC; 部门号 平均工资 最高工资 最低工资 人数 ------ ---------- ---------- ---------- ---------- 4400 4400 4400 1 9500 13000 6000 2 4150 11000 2500 6 6500 6500 6500 1 3475.55555 8200 2100 45 5760 9000 4200 5 10000 10000 10000 1 8973.85294 14000 6100 34 21333.3333 24000 20000 3 8600 12000 6900 6 10150 12000 8300 2 7000 7000 1 rows selected /*--------2、各个部门中工资大于5000的员工人数。---------*/ SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES WHERE SALARY > 5000 GROUP BY DEPARTMENT_ID; DEPARTMENT_ID COUNT(*) ------------- ---------- 2 1 1 5 2 1 34 3 6 2 rows selected /*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/ SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM (SELECT (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME, EMP.SALARY FROM EMPLOYEES EMP) GROUP BY DPTNAME ORDER BY DPTNAME; DPTNAME AVG(SALARY) COUNT(*) ------------------------------ ----------- ---------- Accounting 10150 2 Administration 4400 1 Executive 21333.33333 3 Finance 8600 6 Human Resources 6500 1 IT 5760 5 Marketing 9500 2 Public Relations 10000 1 Purchasing 4150 6 Sales 8973.852941 34 Shipping 3475.555555 45 1 rows selected --或者-- SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*) FROM EMPLOYEES EMP,DEPARTMENTS DEPT WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID GROUP BY DEPT.DEPARTMENT_NAME ORDER BY DEPT.DEPARTMENT_NAME; DEPARTMENT_NAME AVG(EMP.SALARY) COUNT(*) ------------------------------ --------------- ---------- Accounting 10150 2 Administration 4400 1 Executive 21333.333333333 3 Finance 8600 6 Human Resources 6500 1 IT 5760 5 Marketing 9500 2 Public Relations 10000 1 Purchasing 4150 6 Sales 8973.8529411764 34 Shipping 3475.5555555555 45 rows selected --可以看到,这种方式,对于部门号为空的没有统计出来 /*--------4、列出每个部门中有同样工资的员工的统计信息, 列出他们的部门号,工资,人数。---------*/ SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT FROM EMPLOYEES EMP1,EMPLOYEES EMP2 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND EMP1.SALARY = EMP2.SALARY AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY; DEPARTMENT_ID SALARY CNT ------------- ---------- ---------- 2200.00 2 2400.00 2 2500.00 20 2600.00 6 2700.00 2 2800.00 6 2900.00 2 3000.00 2 3100.00 6 3200.00 12 3300.00 2 3600.00 2 4800.00 2 7000.00 2 7500.00 2 8000.00 6 9000.00 2 9500.00 6 10000.00 6 10500.00 2 11000.00 2 20000.00 2 rows selected /*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门, 显示部门名字、地区名称。---------*/ SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*) FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.SALARY > 1000 GROUP BY D.DEPARTMENT_NAME,L.CITY HAVING COUNT(*) > 2; DEPARTMENT_NAME CITY COUNT(*) ------------------------------ ------------------------------ ---------- IT Southlake 5 Sales Oxford 34 Finance Seattle 6 Shipping South San Francisco 45 Executive Seattle 3 Purchasing Seattle 6 rows selected /*--------6、哪些员工的工资,高于整个公司的平均工资, 列出员工的名字和工资(降序)。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY FROM EMPLOYEES WHERE SALARY > ( SELECT AVG(SALARY) FROM EMPLOYEES ) ORDER BY SALARY DESC; FIRST_NAME||''||LAST_NAME SALARY ---------------------------------------------- ---------- Steven King 24000.00 Neena Kochhar 20000.00 Lex De Haan 20000.00 John Russell 14000.00 Karen Partners 13500.00 Michael Hartstein 13000.00 Nancy Greenberg 12000.00 Alberto Errazuriz 12000.00 Shelley Higgins 12000.00 Lisa Ozer 11500.00 Den Raphaely 11000.00 Gerald Cambrault 11000.00 Ellen Abel 11000.00 Eleni Zlotkey 10500.00 Clara Vishney 10500.00 Peter Tucker 10000.00 Janette King 10000.00 Harrison Bloom 10000.00 Hermann Baer 10000.00 Tayler Fox 9600.00 --共50条数据 /*--------7、哪些员工的工资,介于50号 和80号 部门平均工资之间。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY FROM EMPLOYEES WHERE SALARY BETWEEN (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50) AND (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 80); NAME SALARY ---------------------------------------------- ---------- Bruce Ernst 6000.00 David Austin 4800.00 Valli Pataballa 4800.00 Diana Lorentz 4200.00 John Chen 8200.00 Ismael Sciarra 7700.00 Jose Manuel Urman 7800.00 Luis Popp 6900.00 Matthew Weiss 8000.00 Adam Fripp 8200.00 Payam Kaufling 7900.00 Shanta Vollman 6500.00 Kevin Mourgos 5800.00 Renske Ladwig 3600.00 Trenna Rajs 3500.00 Christopher Olsen 8000.00 Nanette Cambrault 7500.00 Oliver Tuvault 7000.00 Lindsey Smith 8000.00 Louise Doran 7500.00 Sarath Sewall 7000.00 Mattea Marvins 7200.00 David Lee 6800.00 Sundar Ande 6400.00 Amit Banda 6200.00 William Smith 7400.00 Elizabeth Bates 7300.00 Sundita Kumar 6100.00 Alyssa Hutton 8800.00 Jonathon Taylor 8600.00 Jack Livingston 8000.00 Kimberely Grant 7000.00 Charles Johnson 7211.00 Nandita Sarchand 4200.00 Alexis Bull 4100.00 Kelly Chung 3800.00 Jennifer Dilly 3600.00 Sarah Bell 4000.00 Britney Everett 3900.00 Jennifer Whalen 4400.00 Pat Fay 6000.00 Susan Mavris 6500.00 William Gietz 8300.00 rows selected /*--------8、所在部门平均工资高于5000 的员工名字。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) > 5000); NAME SALARY ---------------------------------------------- ---------- Michael Hartstein 13000.00 Pat Fay 6000.00 Susan Mavris 6500.00 Alexander Hunold 9000.00 Bruce Ernst 6000.00 David Austin 4800.00 Valli Pataballa 4800.00 Diana Lorentz 4200.00 Hermann Baer 10000.00 John Russell 14000.00 Karen Partners 13500.00 Alberto Errazuriz 12000.00 Gerald Cambrault 11000.00 Eleni Zlotkey 10500.00 Peter Tucker 10000.00 David Bernstein 9500.00 Peter Hall 9000.00 Christopher Olsen 8000.00 Nanette Cambrault 7500.00 Oliver Tuvault 7000.00 --等54行数据… /*--------9、列出各个部门中工资最高的员工的信息:名字、部门号、工资。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME ,SALARY,DEPARTMENT_ID FROM EMPLOYEES WHERE (DEPARTMENT_ID,SALARY) IN (SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); NAME SALARY DEPARTMENT_ID ---------------------------------------------- ---------- ------------- Jennifer Whalen 4400.00 10 Michael Hartstein 13000.00 20 Den Raphaely 11000.00 30 Susan Mavris 6500.00 40 Adam Fripp 8200.00 50 Alexander Hunold 9000.00 60 Hermann Baer 10000.00 70 John Russell 14000.00 80 Steven King 24000.00 90 Nancy Greenberg 12000.00 100 Shelley Higgins 12000.00 110 rows selected /*--------10、最高的部门平均工资是多少。---------*/ SQL> SELECT MAX(AVGSALARY) FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID); MAX(AVGSALARY) -------------- 21333.33333333 ``` ----- 一路走来数个年头,感谢RDIF框架的支持者与使用者,大家可以通过下面的地址了解详情。 官方网站:[http://www.guosisoft.com/](http://www.guosisoft.com/) [http://www.rdiframework.net/](http://www.rdiframework.net/) 特别说明,框架相关的技术文章请以官方网站为准,欢迎大家收藏! **国思RDIF低代码快速开发框架**由海南国思软件科技有限公司专业团队长期打造、一直在更新、一直在升级,请放心使用! 欢迎关注国思RDI低代码快速开发框架官方公众微信(微信号:guosisoft),及时了解最新动态。 使用微信扫描二维码立即关注 ![微信扫描二维码](http://doc.rdiframework.net/weixin.png )
正文到此结束
本文标签:
数据库
版权声明:
本站原创文章,由
guosisoft.com
发布,遵循
CC 4.0 by-sa
版权协议,转载请附上原文出处链接和本声明。
上一篇
[推荐]ORACLE SQL:经典查询练手第二篇(不懂装懂,永世饭桶!)
下一篇
[推荐]ORACLE SQL:经典查询练手第五篇(不懂装懂,永世饭桶!)
热门推荐
{{article.title}}
热门指数:
浏览({{article.lookCount + 5000}})
相关文章
{{article.title}}
该篇文章的评论功能暂时被站长关闭
说给你听
本文目录
文章标签
RDIF.NET
其他
微信开发
.NET
消息交互
.NetCore
项目管理
常用工具
工作流
Web前端
数据库
挨踢业界
随机文章
RDIFramework.NET敏捷开发框架 ━ 工作流程组件Web业务平台
.NET敏捷开发框架-RDIFramework.NET V5.1发布(跨平台)
信息系统项目管理系列之九:项目质量管理
常用linux命令,开发必备
.NET快速信息化系统开发框架 V3.2 新增解压缩工具类ZipHelper
RDIFramework.NET开发框架编码管理助力业务编码的自动处理
实例演示使用RDIFramework.NET 框架的工作流组件进行业务流程的定义—请假申请流程-Web
RDIFramework.NET V3.2->新增模块管理界面导出功能(可按条件导出)
git用.gitignore忽略指定文件
经典的52条产品交互设计原则
RDIFramework.NET ━ .NET敏捷开发框架V3.5版本全新发布 100%源码授权
干货|工作中要使用Git,看这篇文章就够了
RDIFramework.NETV2.9版本 Web新增至14套皮肤风格+三套界面组合(共42套皮肤组合)
RDIFramework.NET开发实例之产品管理(WebForm版)
RDIFramework.NET Web敏捷开发框架 V6.1发布(.NET6+、Framework双引擎)
RDIFramework.NET框架SOA解决方案(集Windows服务、WinForm形式与IIS形式发布)-分布式应用
.NET快速信息化系统开发框架 V3.2 -> WinForm“组织机构管理”界面组织机构权限管理采用新的界面,操作权限按模块进行展示
.NET快速信息化系统开发框架 V3.2-新增锁定用户与解除锁定用户的功能
微信公众号开发C#系列-12、微信前端开发利器:WeUI
JavaScript资源大全中文版(Awesome最新版)
网站信息
文章总数:599 篇
标签总数:8 个
分类总数:8 个
留言数量:1385 条
在线人数:
89
人
运行天数:1321天
最后更新:2023-05-18
QQ:406590790
13005007127