oracle,[推荐]ORACLE SQL：经典查询练手第五篇(不懂装懂，永世饭桶！)

on 2011-6-27 in 博文摘选 | 0 Comment
[推荐]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图：
[推荐]ORACLE SQL：经典查询练手第五篇(不懂装懂，永世饭桶！)oracle


用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部门有哪些职位（关联子查询）。
各试题解答如下（欢迎大家指出不同的方法或建议！）：
/*--------1、哪些部门的人数比90号部门的人数多。---------*/ SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES 2 GROUP BY DEPARTMENT_ID 3 HAVING COUNT(*) > 4 (SELECT COUNT(*) FROM EMPLOYEES 5 WHERE DEPARTMENT_ID = 90 6 ); DEPARTMENT_ID COUNT(*) ------------- ---------- 30 6 50 45 60 5 80 34 100 6 /*-------2、Den(FIRST_NAME)、Raphaely(LAST_NAME)的 领导是谁（非关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES 3 WHERE EMPLOYEE_ID = 4 (SELECT MANAGER_ID FROM EMPLOYEES 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 ); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Steven King /*-------3、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁（非关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES 3 WHERE MANAGER_ID IN 4 (SELECT EMPLOYEE_ID FROM EMPLOYEES 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 ); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares --或者 SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES 3 WHERE MANAGER_ID = 4 (SELECT EMPLOYEE_ID FROM EMPLOYEES 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 ); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares /*-------4、Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁（关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES EMP1 3 WHERE EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 AND EMP2.MANAGER_ID = EMP1.EMPLOYEE_ID); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Steven King /*-------5、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁（关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES EMP1 3 WHERE EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares
/*-------6、列出在同一部门共事，入职日期晚但工资高于其他同事的员工： 名字、工资、入职日期（关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS 姓名, 2 SALARY AS 工资,HIRE_DATE AS 入职日期 3 FROM EMPLOYEES EMP1 4 WHERE EXISTS ( 5 SELECT 1 FROM EMPLOYEES EMP2 6 WHERE EMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID 7 AND EMP1.HIRE_DATE > EMP2.HIRE_DATE 8 AND EMP1.SALARY > EMP2.SALARY 9 ); 姓名 工资 入职日期 ---------------------------------------------- ---------- ----------- Nancy Greenberg 12000.00 1994-8-17 Jose Manuel Urman 7800.00 1998-3-7 Shelli Baida 2900.00 1997-12-24 Adam Fripp 8200.00 1997-4-10 Matthew Weiss 8000.00 1996-7-18 Jennifer Dilly 3600.00 1997-8-13 Julia Dellinger 3400.00 1998-6-24 Laura Bissot 3300.00 1997-8-20 Kevin Mourgos 5800.00 1999-11-16 Shanta Vollman 6500.00 1997-10-10 Vance Jones 2800.00 1999-3-17 Anthony Cabrio 3000.00 1999-2-7 Girard Geoni 2800.00 2000-2-3 Douglas Grant 2600.00 2000-1-13 Donald OConnell 2600.00 1999-6-21 Randall Perkins 2500.00 1999-12-19 Martha Sullivan 2500.00 1999-6-21 Kevin Feeney 3000.00 1998-5-23 Alana Walsh 3100.00 1998-4-24 Samuel McCain 3200.00 1998-7-1 Timothy Gates 2900.00 1998-7-11 Jean Fleaur 3100.00 1998-2-23 Winston Taylor 3200.00 1998-1-24 Michael Rogers 2900.00 1998-8-26 Britney Everett 3900.00 1997-3-3 Kelly Chung 3800.00 1997-6-14 Alexis Bull 4100.00 1997-2-20 Randall Matos 2600.00 1998-3-15 John Seo 2700.00 1998-2-12 Stephen Stiles 3200.00 1997-10-26 Mozhe Atkinson 2800.00 1997-10-30 Irene Mikkilineni 2700.00 1998-9-28 Julia Nayer 3200.00 1997-7-16 Hazel Philtanker 2200.00 2000-2-6 Ki Gee 2400.00 1999-12-12 Steven Markle 2200.00 2000-3-8 Sarah Bell 4000.00 1996-2-4 Nandita Sarchand 4200.00 1996-1-27 Lisa Ozer 11500.00 1997-3-11 Clara Vishney 10500.00 1997-11-11 Eleni Zlotkey 10500.00 2000-1-29 Gerald Cambrault 11000.00 1999-10-15 Alberto Errazuriz 12000.00 1997-3-10 Tayler Fox 9600.00 1998-1-24 Harrison Bloom 10000.00 1998-3-23 Danielle Greene 9500.00 1999-3-19 Charles Johnson 7211.00 2000-1-4 Mattea Marvins 7200.00 2000-1-24 Ellen Abel 11000.00 1996-5-11 Karen Partners 13500.00 1997-1-5 John Russell 14000.00 1996-10-1 Peter Tucker 10000.00 1997-1-30 David Bernstein 9500.00 1997-3-24 Jonathon Taylor 8600.00 1998-3-24 Alyssa Hutton 8800.00 1997-3-19 Peter Hall 9000.00 1997-8-20 Jack Livingston 8000.00 1998-4-23 Christopher Olsen 8000.00 1998-3-30 Elizabeth Bates 7300.00 1999-3-24 William Smith 7400.00 1999-2-23 Nanette Cambrault 7500.00 1998-12-9 61 rows selected /*-------7、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME) 不在同一个部门（非关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID <> 4 (SELECT DEPARTMENT_ID FROM EMPLOYEES 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 ); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Steven King Neena Kochhar Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa --等等 --或者 SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES 3 WHERE DEPARTMENT_ID NOT IN 4 (SELECT DEPARTMENT_ID FROM EMPLOYEES 5 WHERE FIRST_NAME = 'Den' 6 AND LAST_NAME = 'Raphaely' 7 ); /*-------8、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME) 不在同一个部门（关联子查询）。---------*/ SQL> SELECT FIRST_NAME || ' ' || LAST_NAME 2 FROM EMPLOYEES EMP1 3 WHERE NOT EXISTS ( 4 SELECT 1 FROM EMPLOYEES EMP2 5 WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID 6 AND EMP2.FIRST_NAME = 'Den' 7 AND EMP2.LAST_NAME = 'Raphaely'); FIRST_NAME||''||LAST_NAME ---------------------------------------------- Kimberely Grant Lex De Haan Neena Kochhar Steven King Pat Fay Michael Hartstein Diana Lorentz Valli Pataballa --等等 /*-------9、Finance部门有哪些职位（非关联子查询）。---------*/ SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES 2 WHERE DEPARTMENT_ID = ( 3 SELECT DEPARTMENT_ID FROM DEPARTMENTS 4 WHERE DEPARTMENT_NAME = 'Finance'); JOB_ID ---------- FI_ACCOUNT FI_MGR --或者 SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES 2 WHERE DEPARTMENT_ID IN ( 3 SELECT DEPARTMENT_ID FROM DEPARTMENTS 4 WHERE DEPARTMENT_NAME = 'Finance'); JOB_ID ---------- FI_ACCOUNT FI_MGR /*-------10、Finance部门有哪些职位（关联子查询）。---------*/ SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES 2 WHERE EXISTS( 3 SELECT 1 FROM DEPARTMENTS 4 WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID 5 AND DEPARTMENTS.DEPARTMENT_NAME = 'Finance'); JOB_ID ---------- FI_ACCOUNT FI_MGR

© 2011 EricHu
原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------
cnBlobs:http://www.cnblogs.com/huyong/ CSDN：http://blog.csdn.net/chinahuyong

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等） 出处：http://www.cnblogs.com/huyong/
Q Q：80368704 E-Mail: [email protected] 本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。 更多文章请看 [置顶]索引贴——（不断更新中）


Tags:  oracle

延伸阅读

最新评论

发表评论