ORACLE SQL：经典查询练手第五篇

最新推荐文章于 2023-11-07 19:00:36 发布

SimminonGarcia

最新推荐文章于 2023-11-07 19:00:36 发布

阅读量126

点赞数

文章标签：数据库

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