但行好事
莫论前程❤

Oracle数据库sql语句练习(5)

本篇数据查询属于复杂业务,难度比较高

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

img

img

ER图:

img

用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

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
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
赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle数据库sql语句练习(5)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏