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

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

本文与大家共同讨论与分享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. 哪些部门的人数比90 号部门的人数多。  
  2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。  
  3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。  
  4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。  
  5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。  
  6. 列出在同一部门共事,入职日期晚但工资高于其他同事的员工:名字、工资、入职日期(关联子查询)。  
  7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。  
  8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。  
  9. Finance部门有哪些职位(非关联子查询)。  
  10. Finance部门有哪些职位(关联子查询)。  

#p#
 

  1. /*--------1、哪些部门的人数比90号部门的人数多。---------*/  
  2.  
  3. SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES  
  4.    2  GROUP BY DEPARTMENT_ID  
  5.    3  HAVING COUNT(*) >  
  6.    4         (SELECT COUNT(*) FROM EMPLOYEES  
  7.    5          WHERE DEPARTMENT_ID = 90  
  8.    6         );  
  9.    
  10. DEPARTMENT_ID   COUNT(*)  
  11. ------------- ----------  
  12.            30          6  
  13.            50         45  
  14.            60          5  
  15.            80         34  
  16.           100          6  
  17.  
  18. /*-------2、Den(FIRST_NAME)、Raphaely(LAST_NAME)的  
  19. 领导是谁(非关联子查询)。---------*/  
  20.  
  21. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  22.    2  FROM EMPLOYEES  
  23.    3  WHERE EMPLOYEE_ID =   
  24.    4        (SELECT MANAGER_ID FROM EMPLOYEES  
  25.    5         WHERE FIRST_NAME = 'Den' 
  26.    6         AND   LAST_NAME  = 'Raphaely' 
  27.    7        );  
  28.    
  29. FIRST_NAME||''||LAST_NAME  
  30. ----------------------------------------------  
  31. Steven King  
  32.  
  33. /*-------3、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(非关联子查询)。---------*/  
  34.  
  35. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  36.    2  FROM EMPLOYEES  
  37.    3  WHERE MANAGER_ID IN 
  38.    4        (SELECT EMPLOYEE_ID FROM EMPLOYEES  
  39.    5         WHERE FIRST_NAME = 'Den' 
  40.    6         AND   LAST_NAME  = 'Raphaely' 
  41.    7        );  
  42.    
  43. FIRST_NAME||''||LAST_NAME  
  44. ----------------------------------------------  
  45. Alexander Khoo  
  46. Shelli Baida  
  47. Sigal Tobias  
  48. Guy Himuro  
  49. Karen Colmenares  
  50.  
  51. --或者  
  52.  
  53. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  54.    2  FROM EMPLOYEES  
  55.    3  WHERE MANAGER_ID =  
  56.    4        (SELECT EMPLOYEE_ID FROM EMPLOYEES  
  57.    5         WHERE FIRST_NAME = 'Den' 
  58.    6         AND   LAST_NAME  = 'Raphaely' 
  59.    7        );  
  60.    
  61. FIRST_NAME||''||LAST_NAME  
  62. ----------------------------------------------  
  63. Alexander Khoo  
  64. Shelli Baida  
  65. Sigal Tobias  
  66. Guy Himuro  
  67. Karen Colmenares  
  68.  
  69. /*-------4、Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(关联子查询)。---------*/  
  70.  
  71. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  72.    2  FROM EMPLOYEES EMP1  
  73.    3  WHERE EXISTS (  
  74.    4        SELECT 1 FROM EMPLOYEES EMP2  
  75.    5        WHERE FIRST_NAME = 'Den' 
  76.    6        AND LAST_NAME  = 'Raphaely' 
  77.    7        AND EMP2.MANAGER_ID = EMP1.EMPLOYEE_ID);  
  78.    
  79. FIRST_NAME||''||LAST_NAME  
  80. ----------------------------------------------  
  81. Steven King  
  82.  
  83. /*-------5、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(关联子查询)。---------*/  
  84.  
  85. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  86.    2  FROM EMPLOYEES EMP1  
  87.    3  WHERE EXISTS (  
  88.    4        SELECT 1 FROM EMPLOYEES EMP2  
  89.    5        WHERE FIRST_NAME = 'Den' 
  90.    6        AND LAST_NAME  = 'Raphaely' 
  91.    7        AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID);   
  92.  
  93. FIRST_NAME||''||LAST_NAME  
  94. ----------------------------------------------  
  95. Alexander Khoo  
  96. Shelli Baida  
  97. Sigal Tobias  
  98. Guy Himuro  
  99. Karen Colmenares 

#p#
 

  1. /*-------6、列出在同一部门共事,入职日期晚但工资高于其他同事的员工:  
  2. 名字、工资、入职日期(关联子查询)。---------*/  
  3.  
  4. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS 姓名,  
  5.    2          SALARY AS 工资,HIRE_DATE AS 入职日期  
  6.    3  FROM EMPLOYEES EMP1  
  7.    4  WHERE EXISTS (  
  8.    5        SELECT 1 FROM EMPLOYEES EMP2  
  9.    6        WHERE EMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID  
  10.   7        AND   EMP1.HIRE_DATE > EMP2.HIRE_DATE  
  11.    8        AND   EMP1.SALARY    > EMP2.SALARY  
  12.   9        );  
  13.  
  14. 姓名                                                   工资 入职日期  
  15. ---------------------------------------------- ---------- -----------  
  16. Nancy Greenberg                                  12000.00 1994-8-17  
  17. Jose Manuel Urman                                 7800.00 1998-3-7  
  18. Shelli Baida                                      2900.00 1997-12-24  
  19. Adam Fripp                                        8200.00 1997-4-10  
  20. Matthew Weiss                                     8000.00 1996-7-18  
  21. Jennifer Dilly                                    3600.00 1997-8-13  
  22. Julia Dellinger                                   3400.00 1998-6-24  
  23. Laura Bissot                                      3300.00 1997-8-20  
  24. Kevin Mourgos                                     5800.00 1999-11-16  
  25. Shanta Vollman                                    6500.00 1997-10-10  
  26. Vance Jones                                       2800.00 1999-3-17  
  27. Anthony Cabrio                                    3000.00 1999-2-7  
  28. Girard Geoni                                      2800.00 2000-2-3  
  29. Douglas Grant                                     2600.00 2000-1-13  
  30. Donald OConnell                                   2600.00 1999-6-21  
  31. Randall Perkins                                   2500.00 1999-12-19  
  32. Martha Sullivan                                   2500.00 1999-6-21  
  33. Kevin Feeney                                      3000.00 1998-5-23  
  34. Alana Walsh                                       3100.00 1998-4-24  
  35. Samuel McCain                                     3200.00 1998-7-1  
  36. Timothy Gates                                     2900.00 1998-7-11  
  37. Jean Fleaur                                       3100.00 1998-2-23  
  38. Winston Taylor                                    3200.00 1998-1-24  
  39. Michael Rogers                                    2900.00 1998-8-26  
  40. Britney Everett                                   3900.00 1997-3-3  
  41. Kelly Chung                                       3800.00 1997-6-14  
  42. Alexis Bull                                       4100.00 1997-2-20  
  43. Randall Matos                                     2600.00 1998-3-15  
  44. John Seo                                          2700.00 1998-2-12  
  45. Stephen Stiles                                    3200.00 1997-10-26  
  46. Mozhe Atkinson                                    2800.00 1997-10-30  
  47. Irene Mikkilineni                                 2700.00 1998-9-28  
  48. Julia Nayer                                       3200.00 1997-7-16  
  49. Hazel Philtanker                                  2200.00 2000-2-6  
  50. Ki Gee                                            2400.00 1999-12-12  
  51. Steven Markle                                     2200.00 2000-3-8  
  52. Sarah Bell                                        4000.00 1996-2-4  
  53. Nandita Sarchand                                  4200.00 1996-1-27  
  54. Lisa Ozer                                        11500.00 1997-3-11  
  55. Clara Vishney                                    10500.00 1997-11-11  
  56. Eleni Zlotkey                                    10500.00 2000-1-29   
  57. Gerald Cambrault                                 11000.00 1999-10-15  
  58. Alberto Errazuriz                                12000.00 1997-3-10  
  59. Tayler Fox                                        9600.00 1998-1-24  
  60. Harrison Bloom                                   10000.00 1998-3-23  
  61. Danielle Greene                                   9500.00 1999-3-19  
  62. Charles Johnson                                   7211.00 2000-1-4  
  63. Mattea Marvins                                    7200.00 2000-1-24  
  64. Ellen Abel                                       11000.00 1996-5-11  
  65. Karen Partners                                   13500.00 1997-1-5  
  66. John Russell                                     14000.00 1996-10-1  
  67. Peter Tucker                                     10000.00 1997-1-30  
  68. David Bernstein                                   9500.00 1997-3-24  
  69. Jonathon Taylor                                   8600.00 1998-3-24  
  70. Alyssa Hutton                                     8800.00 1997-3-19  
  71. Peter Hall                                        9000.00 1997-8-20  
  72. Jack Livingston                                   8000.00 1998-4-23  
  73. Christopher Olsen                                 8000.00 1998-3-30  
  74. Elizabeth Bates                                   7300.00 1999-3-24  
  75. William Smith                                     7400.00 1999-2-23  
  76. Nanette Cambrault                                 7500.00 1998-12-9  
  77.    
  78. 61 rows selected  
  79.  
  80. /*-------7、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)  
  81. 不在同一个部门(非关联子查询)。---------*/  
  82.  
  83. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  84.   2  FROM EMPLOYEES  
  85.    3  WHERE DEPARTMENT_ID <>  
  86.   4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  87.    5         WHERE FIRST_NAME = 'Den' 
  88.   6         AND LAST_NAME  = 'Raphaely' 
  89.   7        );  
  90.    
  91. FIRST_NAME||''||LAST_NAME  
  92. ----------------------------------------------  
  93. Steven King  
  94. Neena Kochhar  
  95. Lex De Haan  
  96. Alexander Hunold  
  97. Bruce Ernst  
  98. David Austin  
  99. Valli Pataballa  
  100. --等等  
  101.  
  102. --或者  
  103.  
  104. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  105.    2  FROM EMPLOYEES  
  106.    3  WHERE DEPARTMENT_ID NOT IN 
  107.    4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  108.    5         WHERE FIRST_NAME = 'Den' 
  109.    6         AND LAST_NAME  = 'Raphaely' 
  110.    7        );  
  111.  
  112. /*-------8、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)  
  113. 不在同一个部门(关联子查询)。---------*/  
  114.  
  115. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  116.    2  FROM EMPLOYEES EMP1  
  117.    3  WHERE NOT EXISTS (  
  118.    4        SELECT 1 FROM EMPLOYEES EMP2  
  119.    5        WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID  
  120.    6        AND EMP2.FIRST_NAME =  'Den' 
  121.    7        AND EMP2.LAST_NAME  =  'Raphaely');  
  122.    
  123. FIRST_NAME||''||LAST_NAME  
  124. ----------------------------------------------  
  125. Kimberely Grant 
  126. Lex De Haan  
  127. Neena Kochhar  
  128. Steven King  
  129. Pat Fay  
  130. Michael Hartstein  
  131. Diana Lorentz  
  132. Valli Pataballa  
  133. --等等  
  134.  
  135. /*-------9、Finance部门有哪些职位(非关联子查询)。---------*/  
  136.  
  137. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  138.    2  WHERE DEPARTMENT_ID = (  
  139.    3        SELECT DEPARTMENT_ID FROM DEPARTMENTS  
  140.    4        WHERE DEPARTMENT_NAME = 'Finance');  
  141.    
  142. JOB_ID  
  143. ----------  
  144. FI_ACCOUNT  
  145. FI_MGR  
  146.  
  147. --或者  
  148.  
  149. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  150.    2  WHERE DEPARTMENT_ID IN (  
  151.    3        SELECT DEPARTMENT_ID FROM DEPARTMENTS  
  152.    4        WHERE DEPARTMENT_NAME = 'Finance');  
  153.    
  154. JOB_ID  
  155. ----------  
  156. FI_ACCOUNT  
  157. FI_MGR  
  158.  
  159.  
  160. /*-------10、Finance部门有哪些职位(关联子查询)。---------*/  
  161. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  162.    2  WHERE EXISTS(  
  163.    3        SELECT 1 FROM DEPARTMENTS  
  164.    4        WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID  
  165.    5        AND DEPARTMENTS.DEPARTMENT_NAME = 'Finance');  
  166.    
  167. JOB_ID  
  168. ----------  
  169. FI_ACCOUNT  
  170. FI_MGR 

原文链接:http://www.cnblogs.com/huyong/archive/2011/06/27/2090980.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-23 10:25:38

Oracle

2011-06-21 10:28:49

Oracle

2011-06-21 09:14:01

Oracle查询

2017-04-13 14:55:07

AndroidGradle多模块构建

2014-03-28 14:20:42

Android开源项目优秀个人

2013-04-10 18:07:08

微信公众平台接口开发

2024-01-02 19:20:37

人工智能Anaconda

2013-10-23 13:25:28

AngularJS应用

2019-12-16 17:25:04

Python微信群同步直播

2019-03-26 09:00:43

树莓派编程Linux

2009-09-03 09:57:59

Visual StudVS 2010

2011-05-13 11:27:59

javascript

2012-06-25 10:30:06

Hadoop集群

2010-10-27 13:25:33

Oracle查询

2010-09-26 17:09:05

SQL语句

2011-07-11 18:22:34

2017-01-12 15:37:34

LinuxLXD 2.0镜像管理

2012-06-13 13:01:57

Windows Pho
点赞
收藏

51CTO技术栈公众号