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

[推荐]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部门有哪些职位（关联子查询）。

各试题解答如下（欢迎大家指出不同的方法或建议！）：

/*--------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: [e-mail]
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看[置顶]索引贴——（不断更新中）

---