[推荐]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

本文转自yonghu86 51CTO博客，原文链接：http://blog.51cto.com/yonghu/1321340，如需转载请自行联系原作者

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

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像