[推荐]ORACLE SQL：经典查询练手第五篇(不懂装懂，永世饭桶！)
本文共 8722 字，大约阅读时间需要 29 分钟。
[推荐]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 | | 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，如需转载请自行联系原作者
你可能感兴趣的文章
ios专题 － 常用设计模式
查看>>
流式标签生成控件
查看>>
Struts2拦截器总结<转>
查看>>
黄聪：php中时间轴开发，即显示为“刚刚”、“5分钟前”、“昨天10:23”等（转）...
查看>>
黄聪：C#操作xml SelectNodes,SelectSingleNode通过 xPath 定位class包含Contains的DIV
查看>>
Linq的条件查询
查看>>
0515Python基础-带参数的装饰器-多个装饰器
查看>>
QQ机器人Java版
查看>>
C#里判断字符串是否为纯数字
查看>>
C语言求字符串长度
查看>>
Maven的setting.xml配置
查看>>
一个不应该犯的错octave
查看>>
linux经典书籍
查看>>
ubuntu 13.10 monodevelop3 安装
查看>>
【python】-- Redis简介、命令、示例
查看>>
遇到的有关iframe的滚动条问题
查看>>
vue的单向数据流
查看>>
重写了一遍授权思路
查看>>
OpenMesh 之向量操作
查看>>
Linux基础－SSH
查看>>