Oracle SQL:经典查询练手第四篇

数据库 Oracle
本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

接上三篇:

Oracle SQL:经典查询练手第一篇

Oracle SQL:经典查询练手第二篇

Oracle SQL:经典查询练手第三篇

本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来!

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

表名:REGIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
REGION_ID
NUMBER
 
 
 
 
 
2
REGION_NAME
VARCHAR2
25
 
 
 
 
 

表名:COUNTRIES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
COUNTRY_ID
CHAR
2
 
 
 
 
2
COUNTRY_NAME
VARCHAR2
40
 
 
 
 
 
3
REGION_ID
NUMBER
 
 
 
 
 
 

表名:LOCATIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
LOCATION_ID
NUMBER
4
0
 
 
 
2
STREET_ADDRESS
VARCHAR2
40
 
 
 
 
 
3
POSTAL_CODE
VARCHAR2
12
 
 
 
 
 
4
CITY
VARCHAR2
30
 
 
 
 
 
5
STATE_PROVINCE
VARCHAR2
25
 
 
 
 
 
6
COUNTRY_ID
CHAR
2
 
 
 
 
 

表名:DEPARTMENTS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
DEPARTMENT_ID
NUMBER
4
0
 
 
 
2
DEPARTMENT_NAME
VARCHAR2
30
 
 
 
 
 
3
MANAGER_ID
NUMBER
6
0
 
 
 
 
4
LOCATION_ID
NUMBER
4
0
 
 
 
 

表名:JOBS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
JOB_ID
VARCHAR2
10
 
 
 
 
2
JOB_TITLE
VARCHAR2
35
 
 
 
 
 
3
MIN_SALARY
NUMBER
6
0
 
 
 
 
4
MAX_SALARY
NUMBER
6
0
 
 
 
 

表名:EMPLOYEES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
EMPLOYEE_ID
NUMBER
6
0
 
 
 
2
FIRST_NAME
VARCHAR2
20
 
 
 
 
 
3
LAST_NAME
VARCHAR2
25
 
 
 
 
 
4
EMAIL
VARCHAR2
25
 
 
 
 
 
5
PHONE_NUMBER
VARCHAR2
20
 
 
 
 
 
6
HIRE_DATE
DATE
7
 
 
 
 
 
7
JOB_ID
VARCHAR2
10
 
 
 
 
 
8
SALARY
NUMBER
8
2
 
 
 
 
9
COMMISSION_PCT
NUMBER
2
2
 
 
 
 
10
MANAGER_ID
NUMBER
6
0
 
 
 
 
11
DEPARTMENT_ID
NUMBER
4
0
 
 
 
 

ER图:

 

 

用SQL完成以下问题列表:

  1. /*---------------------------------------------  
  2. 1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。  
  3. 2. 各个部门中工资大于5000的员工人数。  
  4. 3. 各个部门平均工资和人数,按照部门名字升序排列。  
  5. 4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。  
  6. 5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,显示部门名字、地区名称。  
  7. 6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)。  
  8. 7. 哪些员工的工资,介于50号 和80号部门平均工资之间。  
  9. 8. 所在部门平均工资高于5000 的员工名字。  
  10. 9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。  
  11. 10. 最高的部门平均工资是多少。  
  12. ---------------------------------------------*/ 

 

#p#

各试题解答如下(欢迎大家指出不同的方法或建议!):

  1. /*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/  
  2. SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资  
  3.   2         ,MAX(SALARY) AS 最高工资,MIN(SALARY)  AS 最低工资  
  4.   3         ,COUNT(*) AS 人数  
  5.   4  FROM EMPLOYEES  
  6.   5  GROUP BY DEPARTMENT_ID  
  7.   6  ORDER BY DEPARTMENT_ID ASC;  
  8.    
  9.    部门号       平均工资       最高工资       最低工资         人数  
  10. ------         ----------         ----------         ----------         ----------  
  11.        10           4400               4400               4400                 1  
  12.     20          9500              13000           6000                  2  
  13.     30           4150              11000           2500                  6  
  14.     40           6500               6500               6500                  1  
  15.     50             3475.55555     8200               2100                 45  
  16.     60           5760               9000               4200                  5  
  17.     70          10000              10000              10000                  1  
  18.     80             8973.85294     14000           6100                 34  
  19.     90             21333.3333     24000              20000                  3  
  20.    100           8600              12000           6900                  6  
  21.    110          10150              12000           8300                  2  
  22.                 7000               7000               7000                  1  
  23.    
  24. 12 rows selected  
  25.  
  26. /*--------2、各个部门中工资大于5000的员工人数。---------*/  
  27. SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES  
  28.    2  WHERE SALARY > 5000  
  29.    3  GROUP BY DEPARTMENT_ID;  
  30.    
  31. DEPARTMENT_ID   COUNT(*)  
  32. ------------- ----------  
  33.            20          2  
  34.            30          1  
  35.            40          1  
  36.            50          5  
  37.            60          2  
  38.            70          1  
  39.            80         34  
  40.            90          3  
  41.           100          6  
  42.           110          2  
  43.                        1  
  44.    
  45. 11 rows selected  
  46.  
  47. /*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/  
  48.  
  49. SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM 
  50.   2         (SELECT 
  51.   3             (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT  
  52.   4             WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,  
  53.   5             EMP.SALARY  
  54.   6  FROM EMPLOYEES EMP)  
  55.   7  GROUP BY DPTNAME  
  56.   8  ORDER BY DPTNAME;  
  57.    
  58. DPTNAME                        AVG(SALARY)   COUNT(*)  
  59. ------------------------------ ----------- ----------  
  60. Accounting                           10150          2  
  61. Administration                        4400          1  
  62. Executive                      21333.33333          3  
  63. Finance                               8600          6  
  64. Human Resources                       6500          1  
  65. IT                                    5760          5  
  66. Marketing                             9500          2  
  67. Public Relations                     10000          1  
  68. Purchasing                            4150          6  
  69. Sales                          8973.852941         34  
  70. Shipping                       3475.555555         45  
  71.                                       7000          1   
  72. 12 rows selected  
  73.  
  74. --或者--  
  75.  
  76. SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)  
  77.    2  FROM EMPLOYEES EMP,DEPARTMENTS DEPT  
  78.    3  WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID  
  79.    4  GROUP BY DEPT.DEPARTMENT_NAME  
  80.    5  ORDER BY DEPT.DEPARTMENT_NAME;  
  81.    
  82. DEPARTMENT_NAME                AVG(EMP.SALARY)   COUNT(*)  
  83. ------------------------------ --------------- ----------  
  84. Accounting                               10150          2  
  85. Administration                            4400          1  
  86. Executive                      21333.333333333          3  
  87. Finance                                   8600          6  
  88. Human Resources                           6500          1  
  89. IT                                        5760          5  
  90. Marketing                                 9500          2  
  91. Public Relations                         10000          1  
  92. Purchasing                                4150          6  
  93. Sales                          8973.8529411764         34  
  94. Shipping                       3475.5555555555         45  
  95.    
  96. 11 rows selected  
  97. --可以看到,这种方式,对于部门号为空的没有统计出来  
  98.  
  99. /*--------4、列出每个部门中有同样工资的员工的统计信息,  
  100.   列出他们的部门号,工资,人数。---------*/  
  101.  
  102. SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT  
  103.    2  FROM   EMPLOYEES EMP1,EMPLOYEES EMP2  
  104.    3  WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND 
  105.    4          EMP1.SALARY = EMP2.SALARY  
  106.    5          AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID  
  107.    6  GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;  
  108.    
  109. DEPARTMENT_ID     SALARY        CNT  
  110. ------------- ---------- ----------  
  111.            50    2200.00          2  
  112.            50    2400.00          2  
  113.            50    2500.00         20  
  114.            50    2600.00          6  
  115.            50    2700.00          2  
  116.            50    2800.00          6  
  117.            50    2900.00          2  
  118.            50    3000.00          2  
  119.            50    3100.00          6  
  120.            50    3200.00         12  
  121.            50    3300.00          2  
  122.            50    3600.00          2  
  123.            60    4800.00          2  
  124.            80    7000.00          2  
  125.            80    7500.00          2  
  126.            80    8000.00          6  
  127.            80    9000.00          2  
  128.            80    9500.00          6  
  129.            80   10000.00          6  
  130.            80   10500.00          2   
  131.            80   11000.00          2  
  132.            90   20000.00          2  
  133.    
  134. 22 rows selected  
  135.  
  136. /*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门,  
  137. 显示部门名字、地区名称。---------*/  
  138.  
  139. SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)  
  140.    2  FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L  
  141.    3  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND 
  142.    4         D.LOCATION_ID   = L.LOCATION_ID    AND 
  143.    5         E.SALARY > 1000  
  144.    6  GROUP BY D.DEPARTMENT_NAME,L.CITY  
  145.    7  HAVING COUNT(*) > 2;  
  146.    
  147. DEPARTMENT_NAME                CITY                             COUNT(*)  
  148. ------------------------------ ------------------------------ ----------  
  149. IT                             Southlake                               5  
  150. Sales                          Oxford                                 34  
  151. Finance                        Seattle                                 6  
  152. Shipping                       South San Francisco                    45  
  153. Executive                      Seattle                                 3  
  154. Purchasing                     Seattle                                 6  
  155.    
  156. rows selected 

#p#

  1. /*--------6、哪些员工的工资,高于整个公司的平均工资,  
  2.   列出员工的名字和工资(降序)。---------*/  
  3.  
  4. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY  
  5.    2  FROM EMPLOYEES  
  6.    3  WHERE SALARY > (  
  7.    4        SELECT AVG(SALARY)  
  8.    5        FROM EMPLOYEES  
  9.    6        )  
  10.    7  ORDER BY SALARY DESC;   
  11. FIRST_NAME||''||LAST_NAME                          SALARY  
  12. ---------------------------------------------- ----------  
  13. Steven King                                      24000.00  
  14. Neena Kochhar                                    20000.00  
  15. Lex De Haan                                      20000.00  
  16. John Russell                                     14000.00  
  17. Karen Partners                                   13500.00  
  18. Michael Hartstein                                13000.00  
  19. Nancy Greenberg                                  12000.00  
  20. Alberto Errazuriz                                12000.00  
  21. Shelley Higgins                                  12000.00  
  22. Lisa Ozer                                        11500.00  
  23. Den Raphaely                                     11000.00  
  24. Gerald Cambrault                                 11000.00  
  25. Ellen Abel                                       11000.00  
  26. Eleni Zlotkey                                    10500.00  
  27. Clara Vishney                                    10500.00  
  28. Peter Tucker                                     10000.00  
  29. Janette King                                     10000.00  
  30. Harrison Bloom                                   10000.00  
  31. Hermann Baer                                     10000.00  
  32. Tayler Fox                                        9600.00  
  33. --共50条数据  
  34.  
  35. /*--------7、哪些员工的工资,介于50号 和80号 部门平均工资之间。---------*/  
  36.  
  37. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  38.   2  FROM EMPLOYEES  
  39.   3  WHERE SALARY  
  40.   4  BETWEEN 
  41.   5      (SELECT AVG(SALARY) FROM EMPLOYEES  
  42.   6       WHERE DEPARTMENT_ID = 50)  
  43.   7  AND (SELECT AVG(SALARY) FROM EMPLOYEES  
  44.   8       WHERE DEPARTMENT_ID = 80);  
  45.    
  46. NAME                                               SALARY  
  47. ---------------------------------------------- ----------  
  48. Bruce Ernst                                       6000.00  
  49. David Austin                                      4800.00  
  50. Valli Pataballa                                   4800.00  
  51. Diana Lorentz                                     4200.00  
  52. John Chen                                         8200.00  
  53. Ismael Sciarra                                    7700.00  
  54. Jose Manuel Urman                                 7800.00  
  55. Luis Popp                                         6900.00  
  56. Matthew Weiss                                     8000.00  
  57. Adam Fripp                                        8200.00  
  58. Payam Kaufling                                    7900.00  
  59. Shanta Vollman                                    6500.00  
  60. Kevin Mourgos                                     5800.00  
  61. Renske Ladwig                                     3600.00  
  62. Trenna Rajs                                       3500.00  
  63. Christopher Olsen                                 8000.00  
  64. Nanette Cambrault                                 7500.00  
  65. Oliver Tuvault                                    7000.00  
  66. Lindsey Smith                                     8000.00  
  67. Louise Doran                                      7500.00  
  68. Sarath Sewall                                     7000.00  
  69. Mattea Marvins                                    7200.00  
  70. David Lee                                         6800.00  
  71. Sundar Ande                                       6400.00  
  72. Amit Banda                                        6200.00  
  73. William Smith                                     7400.00  
  74. Elizabeth Bates                                   7300.00  
  75. Sundita Kumar                                     6100.00  
  76. Alyssa Hutton                                     8800.00  
  77. Jonathon Taylor                                   8600.00  
  78. Jack Livingston                                   8000.00  
  79. Kimberely Grant                                   7000.00  
  80. Charles Johnson                                   7211.00  
  81. Nandita Sarchand                                  4200.00  
  82. Alexis Bull                                       4100.00  
  83. Kelly Chung                                       3800.00  
  84. Jennifer Dilly                                    3600.00  
  85. Sarah Bell                                        4000.00  
  86. Britney Everett                                   3900.00  
  87. Jennifer Whalen                                   4400.00  
  88. Pat Fay                                           6000.00  
  89. Susan Mavris                                      6500.00  
  90. William Gietz                                     8300.00  
  91.    
  92. 43 rows selected  
  93.  
  94. /*--------8、所在部门平均工资高于5000 的员工名字。---------*/  
  95.  
  96. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  97.    2  FROM EMPLOYEES  
  98.    3  WHERE DEPARTMENT_ID IN 
  99.    4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  100.    5         GROUP BY DEPARTMENT_ID  
  101.    6         HAVING AVG(SALARY) > 5000);  
  102.    
  103. NAME                                               SALARY  
  104. ---------------------------------------------- ----------  
  105. Michael Hartstein                                13000.00  
  106. Pat Fay                                           6000.00  
  107. Susan Mavris                                      6500.00  
  108. Alexander Hunold                                  9000.00  
  109. Bruce Ernst                                       6000.00  
  110. David Austin                                      4800.00  
  111. Valli Pataballa                                   4800.00  
  112. Diana Lorentz                                     4200.00  
  113. Hermann Baer                                     10000.00  
  114. John Russell                                     14000.00  
  115. Karen Partners                                   13500.00  
  116. Alberto Errazuriz                                12000.00  
  117. Gerald Cambrault                                 11000.00  
  118. Eleni Zlotkey                                    10500.00  
  119. Peter Tucker                                     10000.00  
  120. David Bernstein                                   9500.00  
  121. Peter Hall                                        9000.00  
  122. Christopher Olsen                                 8000.00  
  123. Nanette Cambrault                                 7500.00  
  124. Oliver Tuvault                                    7000.00  
  125. --等54行数据…  
  126.  
  127. /*--------9、列出各个部门中工资最高的员工的信息:名字、部门号、工资。---------*/  
  128.  
  129. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME 
  130.    2         ,SALARY,DEPARTMENT_ID  
  131.    3  FROM EMPLOYEES  
  132.    4  WHERE (DEPARTMENT_ID,SALARY) IN 
  133.    5        (SELECT DEPARTMENT_ID,MAX(SALARY)  
  134.    6         FROM EMPLOYEES  
  135.    7         GROUP BY DEPARTMENT_ID);   
  136.  
  137. NAME                                               SALARY DEPARTMENT_ID  
  138. ---------------------------------------------- ---------- -------------  
  139. Jennifer Whalen                                   4400.00            10  
  140. Michael Hartstein                                13000.00            20  
  141. Den Raphaely                                     11000.00            30  
  142. Susan Mavris                                      6500.00            40  
  143. Adam Fripp                                        8200.00            50  
  144. Alexander Hunold                                  9000.00            60  
  145. Hermann Baer                                     10000.00            70  
  146. John Russell                                     14000.00            80  
  147. Steven King                                      24000.00            90  
  148. Nancy Greenberg                                  12000.00           100  
  149. Shelley Higgins                                  12000.00           110  
  150.    
  151. 11 rows selected  
  152.  
  153. /*--------10、最高的部门平均工资是多少。---------*/  
  154. SQL> SELECT MAX(AVGSALARY)  
  155.   2  FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY  
  156.   3    FROM EMPLOYEES  
  157.   4    GROUP BY DEPARTMENT_ID);  
  158.    
  159. MAX(AVGSALARY)  
  160. --------------  
  161. 21333.33333333 

原文链接:http://www.cnblogs.com/huyong/archive/2011/06/22/2086772.html

【编辑推荐】

  1. PL/SQL开发中动态SQL的使用与过程分页
  2. 详解Oracle的几种分页查询语句
  3. Oracle分页小谈
  4. 简单说说Oracle分区
  5. Oracle数据库日常维护知识总结

 

责任编辑:艾婧 来源: EricHu's Tech Space
相关推荐

2011-06-22 13:26:37

Oracle

2011-06-27 14:00:52

Oracle

2011-06-21 10:28:49

Oracle

2011-06-21 09:14:01

Oracle查询

2017-04-11 09:07:55

AndroidGradle构建变体

2014-03-28 14:13:31

Android开源项目测试工具

2013-05-07 09:31:14

Hyper-V灾难恢复

2020-11-19 07:51:06

StringJoine分隔符使用

2023-05-23 18:11:12

Rust数组元组

2013-10-23 13:25:28

AngularJS应用

2010-10-27 13:25:33

Oracle查询

2012-04-25 16:51:21

2010-09-26 17:09:05

SQL语句

2012-06-12 10:43:20

Windows Pho

2011-07-11 17:44:07

SQLOracle标识符

2023-12-19 13:13:16

人工智能

2011-06-22 15:04:28

JAVA

2011-04-13 13:38:57

选项APIBlackBerry

2011-05-13 11:17:18

javascript

2010-06-08 08:41:08

.NET 4并行编程
点赞
收藏

51CTO技术栈公众号