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

更新时间：2024-04-04 08:11:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

oracle数据库推荐度：
相关推荐

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

本文使用ORACLE自带的人力资源（HR）实例数据，本文所用表结构如下：

表名：REGIONS 序号 1 2 列名 REGION_ID 数据类型 NUMBER 长度小数位标识主键允许空默认值 25 是否是说明 REGION_NAME VARCHAR2 表名：COUNTRIES 序号 1 2 3 列名 COUNTRY_ID 数据类型 CHAR 长度小数位标识主键允许空默认值 2 40 是否是是说明 COUNTRY_NAME VARCHAR2 REGION_ID NUMBER 表名：LOCATIONS 序号 1 2 3 4 5 6 列名 LOCATION_ID 数据类型 NUMBER 长度小数位标识主键允许空默认值 4 40 12 30 25 2 0 是否是是否是是说明 STREET_ADDRESS VARCHAR2 POSTAL_CODE CITY VARCHAR2 VARCHAR2 STATE_PROVINCE VARCHAR2 COUNTRY_ID CHAR 表名：DEPARTMENTS 序号 1 2 3 4 列名 DEPARTMENT_ID 数据类型 NUMBER 长度小数位标识主键允许空默认值 4 30 6 4 0 0 0 是否否是是说明 DEPARTMENT_NAME VARCHAR2 MANAGER_ID LOCATION_ID NUMBER NUMBER 表名：JOBS 序号 1 2 3 4 列名 JOB_ID JOB_TITLE 数据类型 VARCHAR2 VARCHAR2 长度 10 35 6 6 小数位 0 0 标识主键是允许空否否是是默认值说明 MIN_SALARY NUMBER MAX_SALARY NUMBER 表名：EMPLOYEES 序号 1 列名 EMPLOYEE_ID 数据类型 NUMBER 长度小数位标识主键允许空默认值 6 0 是否说明 2 3 4 5 6 7 8 9 10 11 FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE VARCHAR2 NUMBER 20 25 25 20 7 10 8 2 6 4 2 2 0 0 是否否是否否是是是是 COMMISSION_PCT NUMBER MANAGER_ID DEPARTMENT_ID NUMBER NUMBER 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. ---------------------------------------------*/

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

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

12. 20 9500 13000 6000

13. 30 4150 11000 2500

14. 40 6500 6500 6500

15. 50 3475.55555 8200 2100

16. 60 5760 9000 4200

17. 70 10000 10000 10000

18. 80 8973.85294 14000 6100

19. 90 21333.3333 24000 20000

20. 100 8600 12000 6900

21. 110 10150 12000 8300

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 T(*)

COUN

148. ------------------------------ ------------------------------ ----------

149. IT Southlake

150. Sales Oxford

151. Finance Seattle

152. Shipping South San Francisco

153. Executive Seattle

154. Purchasing Seattle

6 155.

156. 6 rows selected

157. /*--------6、哪些员工的工资，高于整个公司的平均工资， 158. 列出员工的名字和工资（降序）。---------*/ 159.

160. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY 161. 2 FROM EMPLOYEES 162. 3 WHERE SALARY > (

163. 4 SELECT AVG(SALARY) 164. 5 FROM EMPLOYEES 165. 6 )

166. 7 ORDER BY SALARY DESC;

167. FIRST_NAME||''||LAST_NAME SALARY 168. ---------------------------------------------- ---------- 169. Steven King 24000.00 170. Neena Kochhar 20000.00 171. Lex De Haan 20000.00 172. John Russell 14000.00 173. Karen Partners 13500.00 174. Michael Hartstein 13000.00 175. Nancy Greenberg 12000.00 176. Alberto Errazuriz 12000.00 177. Shelley Higgins 12000.00 178. Lisa Ozer 11500.00 179. Den Raphaely 11000.00 180. Gerald Cambrault 11000.00 181. Ellen Abel 11000.00 182. Eleni Zlotkey 10500.00 183. Clara Vishney 10500.00 184. Peter Tucker 10000.00

185. Janette King 10000.00 186. Harrison Bloom 10000.00 187. Hermann Baer 10000.00 188. Tayler Fox 9600.00 189. --共50条数据 190.

191. /*--------7、哪些员工的工资，介于50号和80号部门平均工资之间。---------*/ 192.

193. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY 194. 2 FROM EMPLOYEES 195. 3 WHERE SALARY 196. 4 BETWEEN

197. 5 (SELECT AVG(SALARY) FROM EMPLOYEES 198. 6 WHERE DEPARTMENT_ID = 50)

199. 7 AND (SELECT AVG(SALARY) FROM EMPLOYEES 200. 8 WHERE DEPARTMENT_ID = 80); 201.

202. NAME SALARY 203. ---------------------------------------------- ---------- 204. Bruce Ernst 6000.00 205. David Austin 4800.00 206. Valli Pataballa 4800.00 207. Diana Lorentz 4200.00 208. John Chen 8200.00 209. Ismael Sciarra 7700.00 210. Jose Manuel Urman 7800.00 211. Luis Popp 6900.00 212. Matthew Weiss 8000.00 213. Adam Fripp 8200.00 214. Payam Kaufling 7900.00 215. Shanta Vollman 6500.00 216. Kevin Mourgos 5800.00 217. Renske Ladwig 3600.00 218. Trenna Rajs 3500.00 219. Christopher Olsen 8000.00 220. Nanette Cambrault 7500.00 221. Oliver Tuvault 7000.00 222. Lindsey Smith 8000.00 223. Louise Doran 7500.00 224. Sarath Sewall 7000.00 225. Mattea Marvins 7200.00 226. David Lee 6800.00 227. Sundar Ande 6400.00 228. Amit Banda 6200.00

229. William Smith 7400.00 230. Elizabeth Bates 7300.00 231. Sundita Kumar 6100.00 232. Alyssa Hutton 8800.00 233. Jonathon Taylor 8600.00 234. Jack Livingston 8000.00 235. Kimberely Grant 7000.00 236. Charles Johnson 7211.00 237. Nandita Sarchand 4200.00 238. Alexis Bull 4100.00 239. Kelly Chung 3800.00 240. Jennifer Dilly 3600.00 241. Sarah Bell 4000.00 242. Britney Everett 3900.00 243. Jennifer Whalen 4400.00 244. Pat Fay 6000.00 245. Susan Mavris 6500.00 246. William Gietz 8300.00 247.

248. 43 rows selected 249.

250. /*--------8、所在部门平均工资高于5000 的员工名字。---------*/ 251.

252. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY 253. 2 FROM EMPLOYEES

254. 3 WHERE DEPARTMENT_ID IN

255. 4 (SELECT DEPARTMENT_ID FROM EMPLOYEES 256. 5 GROUP BY DEPARTMENT_ID 257. 6 HAVING AVG(SALARY) > 5000); 258.

259. NAME SALARY 260. ---------------------------------------------- ---------- 261. Michael Hartstein 13000.00 262. Pat Fay 6000.00 263. Susan Mavris 6500.00 264. Alexander Hunold 9000.00 265. Bruce Ernst 6000.00 266. David Austin 4800.00 267. Valli Pataballa 4800.00 268. Diana Lorentz 4200.00 269. Hermann Baer 10000.00 270. John Russell 14000.00 271. Karen Partners 13500.00 272. Alberto Errazuriz 12000.00

273. Gerald Cambrault 11000.00 274. Eleni Zlotkey 10500.00 275. Peter Tucker 10000.00 276. David Bernstein 9500.00 277. Peter Hall 9000.00 278. Christopher Olsen 8000.00 279. Nanette Cambrault 7500.00 280. Oliver Tuvault 7000.00 281. --等54行数据… 282.

283. /*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/ 284.

285. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME 286. 2 ,SALARY,DEPARTMENT_ID 287. 3 FROM EMPLOYEES

288. 4 WHERE (DEPARTMENT_ID,SALARY) IN

289. 5 (SELECT DEPARTMENT_ID,MAX(SALARY) 290. 6 FROM EMPLOYEES

291. 7 GROUP BY DEPARTMENT_ID); 292.

293. NAME SALARY DEPARTMENT

_ID

294. ---------------------------------------------- ---------- -------------

295. Jennifer Whalen 4400.00

296. Michael Hartstein 13000.00

297. Den Raphaely 11000.00

298. Susan Mavris 6500.00