经典查询练手第五篇(不懂装懂，永世饭桶！)

本文与大家共同讨论与分享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