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

数据库 Oracle
PostgreSQL 是一种对象-关系型数据库管理系统(ORDBMS),功能强大,特性丰富的自由软件数据库系统。这个起源于伯克利(BSD)的数据库研究计划目前已经成为一项国际开发项目,并且有非常广泛的用户。在灵活的BSD风格许可证下发行。

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

 接上两篇:

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

本篇相对上两篇来说难度有所增加,继续努力,通过我为大家设立的这个系列,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的SQL已能熟能生巧,信手拈来!

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

HR.EMPLOYEES员工表结构如下:

 

HR.DEPARTMENTS表结构如下:

HR.REGIONS表结构如下:

  1. SQL> DESC HR.REGIONS;  
  2. Name        Type         Nullable Default Comments   
  3. ----------- ------------ -------- ------- --------   
  4. REGION_ID   NUMBER                                   
  5. REGION_NAME VARCHAR2(25) Y 

用SQL完成以下问题列表:

1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 输出结果的货币单位是¥和$。

2. 列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。

3. 找出谁是最高领导,将名字按大写形式显示。

4. 找出First_Name 为David,Last_Name为Austin 的直接领导名字。

5. First_Name 为Alexander,Last_Name为Hunold领导谁。(谁向David 报告)。

6. 哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。

7. 哪些员工和Chen(LAST_NAME)同部门。

8. 哪些员工跟De Haan(LAST_NAME)做一样职位。

9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。

10. 哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。

11. 显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。

12. 显示Executive部门有哪些职位。

13. 整个公司中,最高工资和最低工资相差多少。

14. 提成大于0 的人数。

15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

16. 整个公司有多少个领导。

17. 列出在同一部门入职日期晚但工资高于其他同事的员工:名字、工资、入职日期。

#p#

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

  1. /*--------1、改变NLS_LANG 的值,让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和$。---------*/  
  2. -----在没有设置NLS_LANG的情况下:  
  3.  
  4. SQL> SELECT TO_CHAR(SALARY,'L99,999.99')  
  5.    2  FROM HR.EMPLOYEES  
  6.    3  WHERE ROWNUM < 5;  
  7.  
  8. TO_CHAR(SALARY,'L99,999.99')  
  9. ----------------------------  
  10.          ¥24,000.00  
  11.          ¥20,000.00  
  12.          ¥20,000.00  
  13.           ¥9,000.00  
  14.  
  15. SQL> SELECT TO_CHAR(SALARY,'$99,999.99')  
  16.    2  FROM HR.EMPLOYEES  
  17.    3  WHERE ROWNUM < 5;  
  18.    
  19. TO_CHAR(SALARY,'$99,999.99')  
  20. ----------------------------  
  21.  $24,000.00  
  22.  $20,000.00  
  23.  $20,000.00  
  24.   $9,000.00  
  25.  
  26. /*--说明:对于'$99,999.99'格式符:  
  27. L:表示强制显示当地货币符号  
  28. $: 表示显示美元符号  
  29. 9: 表示一个数字  
  30. 0: 表示强制0显示  
  31. .: 表示一个小数点  
  32. ,: 表示一个千位分隔符  
  33. --------------*/  
  34.  
  35. /*--------2、列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。---------*/  
  36.  
  37. SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES  
  38.    2  WHERE ROWNUM <=5;  
  39.    
  40. FIRST_NAME               SALARY ROUND(SALARY*1.08)  
  41. -------------------- ---------- ------------------  
  42. Steven                 24000.00              25920  
  43. Neena                  20000.00              21600  
  44. Lex                    20000.00              21600  
  45. Alexander               9000.00               9720  
  46. Bruce                   6000.00               6480  
  47.  
  48. /*--------3、找出谁是最高领导,将名字按大写形式显示。---------*/  
  49. SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME 
  50.    2  FROM HR.EMPLOYEES  
  51.    3  WHERE MANAGER_ID IS NULL;  
  52.    
  53. NAME 
  54. ----------------------------------------------  
  55. STEVEN KING  
  56.  
  57. /*--------4、找出David 的直接领导的名字。---------*/  
  58. SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME 
  59.    2  FROM HR.EMPLOYEES  
  60.    3  WHERE EMPLOYEE_ID IN(  
  61.    4  SELECT MANAGER_ID FROM HR.EMPLOYEES  
  62.    5  WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');  
  63.    
  64. NAME 
  65. ----------------------------------------------  
  66. ALEXANDER HUNOLD  
  67.  
  68. --或采用以下方法  
  69.  
  70. SQL> SELECT UPPER( EMP1.FIRST_NAME ||' ' ||  EMP1.LAST_NAME) AS NAME 
  71.    2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2  
  72.    3  WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID  
  73.    4  AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME =  'Austin';   
  74.  
  75. NAME 
  76. ----------------------------------------------  
  77. ALEXANDER HUNOLD  
  78.    
  79. /*--------5、First_Name 为Alexander,LAST_NAME为Hunold领导谁。(谁向David 报告)。---------*/  
  80. SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME 
  81.    2  FROM HR.EMPLOYEES  
  82.    3  WHERE MANAGER_ID IN(  
  83.    4  SELECT EMPLOYEE_ID FROM HR.EMPLOYEES  
  84.    5  WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');  
  85.    
  86. NAME 
  87. ----------------------------------------------  
  88. BRUCE ERNST  
  89. DAVID AUSTIN  
  90. VALLI PATABALLA  
  91. DIANA LORENTZ  
  92.  
  93. --或采用以下方法  
  94.  
  95. SQL> SELECT UPPER( EMP1.FIRST_NAME || ' ' ||  EMP1.LAST_NAME) AS NAME 
  96.   2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2  
  97.   3  WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID  
  98.   4  AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME =  'Hunold';  
  99.    
  100. NAME 
  101. ----------------------------------------------  
  102. BRUCE ERNST  
  103. DAVID AUSTIN  
  104. VALLI PATABALLA  
  105. DIANA LORENTZ  
  106.  
  107. /*--------6、哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。---------*/  
  108.  
  109. SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY  
  110.   2  FROM EMPLOYEES E,EMPLOYEES M  
  111.   3  WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;  
  112.    
  113. FIRST_NAME               SALARY FIRST_NAME               SALARY  
  114. -------------------- ---------- -------------------- ----------  
  115. Lisa                   11500.00 Gerald                 11000.00  
  116. Ellen                  11000.00 Eleni                  10500.00  
  117.  
  118. --要是只列出员工的名字与工资的话,还可以这样:  
  119.  
  120. SQL> SELECT E.FIRST_NAME,E.SALARY  
  121.   2  FROM EMPLOYEES E WHERE E.SALARY >  
  122.   3 (SELECT M.SALARY FROM EMPLOYEES M   
  123.   4  WHERE E.MANAGER_ID = M.EMPLOYEE_ID);  
  124.    
  125. FIRST_NAME               SALARY  
  126. -------------------- ----------  
  127. Lisa                   11500.00  
  128. Ellen                  11000.00 

#p#

  1. /*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/  
  2.  
  3. SQL> SELECT FIRST_NAME FROM EMPLOYEES  
  4.   2  WHERE DEPARTMENT_ID IN 
  5.   3  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')  
  6.   4  AND LAST_NAME <> 'Chen';  
  7.    
  8. FIRST_NAME  
  9. --------------------  
  10. Nancy  
  11. Daniel  
  12. Ismael  
  13. Jose Manuel  
  14. Luis  
  15. --或者--  
  16.  
  17. SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2  
  18.   2  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID  
  19.   3  AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';  
  20.    
  21. FIRST_NAME  
  22. --------------------  
  23. Nancy  
  24. Daniel  
  25. Ismael  
  26. Jose Manuel  
  27. Luis  
  28.  
  29. /*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/  
  30. SQL> SELECT FIRST_NAME FROM EMPLOYEES  
  31.   2  WHERE JOB_ID IN 
  32.   3  (SELECT JOB_ID FROM EMPLOYEES  
  33.   4  WHERE LAST_NAME = 'De Haan')  
  34.   5  AND LAST_NAME <> 'De Haan';  
  35.    
  36. FIRST_NAME  
  37. --------------------  
  38. Neena  
  39.  
  40. --或者--  
  41.  
  42. SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2  
  43.   2  WHERE E1.JOB_ID = E2.JOB_ID   
  44.   3  AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';  
  45.    
  46. FIRST_NAME  
  47. --------------------  
  48. Neena  
  49.  
  50. /*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/  
  51.  
  52. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES  
  53.   2  WHERE DEPARTMENT_ID NOT IN(  
  54.   3  SELECT DEPARTMENT_ID FROM HR.EMPLOYEES  
  55.   4  WHERE LAST_NAME = 'Hall');  
  56.    
  57. FIRST_NAME||''||LAST_NAME  
  58. ----------------------------------------------  
  59. Steven King  
  60. Neena Kochhar  
  61. Lex De Haan  
  62. Alexander Hunold  
  63. Bruce Ernst  
  64. David Austin  
  65. Valli Pataballa  
  66. Diana Lorentz  
  67. Nancy Greenberg  
  68. --...初始有72条数据  
  69.  
  70. --或者:  
  71.  
  72. SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2  
  73.   2  WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)  
  74.   3  and e2.LAST_NAME(+) = 'Hall' 
  75.   4  and e2.LAST_NAME IS NULL;  
  76.  
  77. /*-------10、哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。--------*/  
  78.  
  79. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES  
  80.   2  WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  81.   3  WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');   
  82.  
  83. FIRST_NAME||''||LAST_NAME  
  84. ----------------------------------------------  
  85. Steven King  
  86. Neena Kochhar  
  87. Lex De Haan  
  88. Alexander Hunold  
  89. ----...初始有77条数据  
  90.  
  91. /*--------11、显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。---------*/  
  92.  
  93. SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,  
  94.   2  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY  
  95.   3  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L  
  96.   4  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID  
  97.   5  AND D.LOCATION_ID = L.LOCATION_ID  
  98.   6  AND E.COMMISSION_PCT IS NOT NULL;  
  99.  
  100. /*--------12、显示Executive部门有哪些职位。---------*/  
  101. SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D  
  102.   2  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID  
  103.   3  AND D.DEPARTMENT_NAME = 'Executive';  
  104.    
  105. JOB_ID  
  106. ----------  
  107. AD_PRES  
  108. AD_VP  

#p#

  1. /*--------13、整个公司中,最高工资和最低工资相差多少。---------*/  
  2. SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;  
  3.    
  4. MAX(SALARY)-MIN(SALARY)  
  5. -----------------------  
  6.                   21900  
  7.  
  8. /*--------14、提成大于0 的人数。---------*/  
  9. SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES  
  10.   2  WHERE COMMISSION_PCT > 0;  
  11.    
  12.        提成大小0的人数  
  13. ---------------  
  14.              35  
  15. --或者  
  16.  
  17. SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数    
  18.   2   FROM HR.EMPLOYEES  
  19.   3   WHERE COMMISSION_PCT > 0;  
  20.        提成大小0的人数  
  21. ---------------  
  22.              35  
  23.  
  24. /*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/  
  25.  
  26. SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,  
  27.   2           MIN(NVL(SALARY,0)) AS 最低工资,  
  28.   3           SUM(NVL(SALARY,0)) AS 工资总和,  
  29.   4           ROUND(AVG(NVL(SALARY,0))) AS 平均工资  
  30.   5  FROM HR.EMPLOYEES;  
  31.  
  32.       最高工资       最低工资       工资总和       平均工资  
  33.       ----------    ----------      ----------     ---------  
  34.       24000          2100           698011         6523  
  35.  
  36. /*--------16、整个公司有多少个领导。---------*/  
  37. SQL> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES  
  38.    2  WHERE MANAGER_ID IS NOT NULL;  
  39.    
  40. COUNT(DISTINCT(MANAGER_ID))  
  41. ---------------------------  
  42.                          18  
  43.  
  44. /*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工:  
  45. 名字、工资、入职日期。---------*/  
  46.  
  47. SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,  
  48.   2         E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期  
  49.   3  FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2  
  50.   4  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID  
  51.   5  AND E1.HIRE_DATE > E2.HIRE_DATE  
  52.   6  AND E1.SALARY > E2.SALARY  
  53.   7  ORDER BY 工资 DESC;  
  54.  
  55. 姓名                                                   工资 入职日期  
  56. ---------------------------------------------- ---------- -----------  
  57. John Russell                                     14000.00 1996-10-1  
  58. Karen Partners                                   13500.00 1997-1-5  
  59. Alberto Errazuriz                                12000.00 1997-3-10  
  60. Nancy Greenberg                                  12000.00 1994-8-17  
  61. Lisa Ozer                                        11500.00 1997-3-11  
  62. Ellen Abel                                       11000.00 1996-5-11  
  63. Gerald Cambrault                                 11000.00 1999-10-15  
  64. Clara Vishney                                    10500.00 1997-11-11  
  65. Eleni Zlotkey                                    10500.00 2000-1-29  
  66. Harrison Bloom                                   10000.00 1998-3-23  
  67. Peter Tucker                                     10000.00 1997-1-30  
  68. Tayler Fox                                        9600.00 1998-1-24  
  69. Danielle Greene                                   9500.00 1999-3-19  
  70. David Bernstein                                   9500.00 1997-3-24  
  71. Peter Hall                                        9000.00 1997-8-20  
  72. Alyssa Hutton                                     8800.00 1997-3-19  
  73. Jonathon Taylor                                   8600.00 1998-3-24  
  74. Adam Fripp                                        8200.00 1997-4-10  
  75. Christopher Olsen                                 8000.00 1998-3-30  
  76. Jack Livingston                                   8000.00 1998-4-23   
  77. Matthew Weiss                                     8000.00 1996-7-18  
  78. Jose Manuel Urman                                 7800.00 1998-3-7  
  79. Nanette Cambrault                                 7500.00 1998-12-9  
  80. William Smith                                     7400.00 1999-2-23  
  81. Elizabeth Bates                                   7300.00 1999-3-24  
  82. Charles Johnson                                   7211.00 2000-1-4  
  83. Mattea Marvins                                    7200.00 2000-1-24  
  84. Shanta Vollman                                    6500.00 1997-10-10  
  85. Kevin Mourgos                                     5800.00 1999-11-16  
  86. Nandita Sarchand                                  4200.00 1996-1-27  
  87. Alexis Bull                                       4100.00 1997-2-20  
  88. Sarah Bell                                        4000.00 1996-2-4  
  89. Britney Everett                                   3900.00 1997-3-3  
  90. Kelly Chung                                       3800.00 1997-6-14  
  91. Jennifer Dilly                                    3600.00 1997-8-13  
  92. Julia Dellinger                                   3400.00 1998-6-24  
  93. Laura Bissot                                      3300.00 1997-8-20  
  94. Julia Nayer                                       3200.00 1997-7-16  
  95. Samuel McCain                                     3200.00 1998-7-1  
  96. Stephen Stiles                                    3200.00 1997-10-26  
  97. Winston Taylor                                    3200.00 1998-1-24   
  98. Alana Walsh                                       3100.00 1998-4-24  
  99. Jean Fleaur                                       3100.00 1998-2-23  
  100. Anthony Cabrio                                    3000.00 1999-2-7  
  101. Kevin Feeney                                      3000.00 1998-5-23  
  102. Michael Rogers                                    2900.00 1998-8-26  
  103. Shelli Baida                                      2900.00 1997-12-24  
  104. Timothy Gates                                     2900.00 1998-7-11  
  105. Girard Geoni                                      2800.00 2000-2-3  
  106. Mozhe Atkinson                                    2800.00 1997-10-30  
  107. Vance Jones                                       2800.00 1999-3-17  
  108. Irene Mikkilineni                                 2700.00 1998-9-28  
  109. John Seo                                          2700.00 1998-2-12  
  110. Donald OConnell                                   2600.00 1999-6-21  
  111. Douglas Grant                                     2600.00 2000-1-13  
  112. Randall Matos                                     2600.00 1998-3-15  
  113. Martha Sullivan                                   2500.00 1999-6-21  
  114. Randall Perkins                                   2500.00 1999-12-19  
  115. Ki Gee                                            2400.00 1999-12-12  
  116. Hazel Philtanker                                  2200.00 2000-2-6  
  117. Steven Markle                                     2200.00 2000-3-8  
  118.    
  119. 61 rows selected 

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

【编辑推荐】

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

 

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

2011-06-23 10:25:38

Oracle

2011-06-27 14:00:52

Oracle

2011-06-21 10:28:49

Oracle

2011-06-21 09:14:01

Oracle查询

2017-04-10 17:35:54

AndroidGradle依赖管理

2014-03-28 14:00:23

Android开源项目优秀项目

2022-08-03 10:26:34

网络协议网络TCP

2010-10-27 13:25:33

Oracle查询

2010-09-26 17:09:05

SQL语句

2011-04-11 14:29:44

交换排序冒泡排序排序

2011-07-11 16:47:57

数据库移植SQL ServerOracle

2010-10-27 13:35:15

Oracle查询

2019-07-16 16:26:16

Ansible桌面设置Linux

2012-04-25 16:46:25

ASO图片视频

2011-08-19 09:30:42

分页查询SQL ServerMySQL

2010-10-27 13:39:34

Oracle查询

2009-10-20 18:32:25

Oracle 10g

2020-04-09 11:00:38

Python编程语言代码

2009-11-18 16:16:51

Oracle数据库

2018-06-01 15:41:21

点赞
收藏

51CTO技术栈公众号