ORACLE 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	是	否
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	是	否
2	DEPARTMENT_NAME	VARCHAR2	30	否
3	MANAGER_ID	NUMBER	6	是
4	LOCATION_ID	NUMBER	4	是

表名：JOBS

序号	列名	数据类型	长度	小数位	标识	主键	允许空	默认值	说明
1	JOB_ID	VARCHAR2	10	是	否
2	JOB_TITLE	VARCHAR2	35	否
3	MIN_SALARY	NUMBER	6	是
4	MAX_SALARY	NUMBER	6	是

表名：EMPLOYEES

序号	列名	数据类型	长度	小数位	标识	主键	允许空	默认值	说明
1	EMPLOYEE_ID	NUMBER	6	是	否
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	是
11	DEPARTMENT_ID	NUMBER	4	是

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部门有哪些职位（关联子查询）。 复制代码

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

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

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

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

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

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 复制代码

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

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        );

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

--等等

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

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 复制代码