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

2024-03-29 22:25•css•阅读 1436

　　本文使用ORACLE自带的人力资源（HR）实例数据，本文所用表结构如下：

　　HR.EMPLOYEES员工表结构如下：

　　HR.DEPARTMENTS表结构如下：

　　HR.REGIONS表结构如下：

　　SQL> DESC HR.REGIONS;

　　Name Type Nullable Default Comments

　　----------- ------------ -------- ------- --------

　　REGION_ID NUMBER

　　REGION_NAME VARCHAR2(25) Y

　　--------------------------------------------------------------------------------

　　用SQL完成以下问题列表：

　　1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。

　　2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。

　　3. 找出谁是最高领导，将名字按大写形式显示。

　　4. 找出First_Name 为David，Last_Name为Austin 的直接领导名字。

　　5. First_Name 为Alexander，Last_Name为Hunold领导谁。（谁向David 报告）。

　　6. 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。

　　7. 哪些员工和Chen(LAST_NAME)同部门。

　　8. 哪些员工跟De Haan(LAST_NAME)做一样职位。

　　9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。

　　10. 哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。

　　11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。

　　12. 显示Executive部门有哪些职位。

　　13. 整个公司中，最高工资和最低工资相差多少。

　　14. 提成大于0 的人数。

　　15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

　　16. 整个公司有多少个领导。

　　17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期。

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

　　/*--------1、改变NLS_LANG 的值，让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。---------*/

　　-----在没有设置NLS_LANG的情况下：

　　SQL> SELECT TO_CHAR(SALARY,'L99,999.99')

　　2 FROM HR.EMPLOYEES

　　3 WHERE ROWNUM < 5;

　　TO_CHAR(SALARY,'L99,999.99')

　　----------------------------

　　￥24,000.00

　　￥20,000.00

　　￥9,000.00

　　SQL> SELECT TO_CHAR(SALARY,'$99,999.99')

　　2 FROM HR.EMPLOYEES

　　3 WHERE ROWNUM < 5;

　　TO_CHAR(SALARY,'$99,999.99')

　　----------------------------

　　$24,000.00

　　$20,000.00

　　$9,000.00

　　/*--说明：对于'$99,999.99'格式符：

　　L：表示强制显示当地货币符号

　　$: 表示显示美元符号

　　9: 表示一个数字

　　0: 表示强制0显示

　　.: 表示一个小数点

　　,: 表示一个千位分隔符

　　--------------*/

　　/*--------2、列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。---------*/

　　SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES

　　2 WHERE ROWNUM <=5;

　　FIRST_NAME SALARY ROUND(SALARY*1.08)

　　-------------------- ---------- ------------------

　　Steven 24000.00 25920

　　Neena 20000.00 21600

　　Lex 20000.00 21600

　　Alexander 9000.00 9720

　　Bruce 6000.00 6480

　　/*--------3、找出谁是最高领导，将名字按大写形式显示。---------*/

　　SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME

　　2 FROM HR.EMPLOYEES

　　3 WHERE MANAGER_ID IS NULL;

　　NAME

　　----------------------------------------------

　　STEVEN KING

　　/*--------4、找出David 的直接领导的名字。---------*/

　　SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME

　　2 FROM HR.EMPLOYEES

　　3 WHERE EMPLOYEE_ID IN(

　　4 SELECT MANAGER_ID FROM HR.EMPLOYEES

　　5 WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');

　　NAME

　　----------------------------------------------

　　ALEXANDER HUNOLD

　　--或采用以下方法

　　SQL> SELECT UPPER( EMP1.FIRST_NAME ||' ' || EMP1.LAST_NAME) AS NAME

　　2 FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2

　　3 WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID

　　4 AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME = 'Austin';

　　NAME

　　----------------------------------------------

　　ALEXANDER HUNOLD

　　/*--------5、First_Name 为Alexander，LAST_NAME为Hunold领导谁。（谁向David 报告）。---------*/

　　SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME

　　2 FROM HR.EMPLOYEES

　　3 WHERE MANAGER_ID IN(

　　4 SELECT EMPLOYEE_ID FROM HR.EMPLOYEES

　　5 WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');

　　NAME

　　----------------------------------------------

　　BRUCE ERNST

　　DAVID AUSTIN

　　VALLI PATABALLA

　　DIANA LORENTZ

　　--或采用以下方法

　　SQL> SELECT UPPER( EMP1.FIRST_NAME || ' ' || EMP1.LAST_NAME) AS NAME

　　2 FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2

　　3 WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID

　　4 AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME = 'Hunold';

　　NAME

　　----------------------------------------------

　　BRUCE ERNST

　　DAVID AUSTIN

　　VALLI PATABALLA

　　DIANA LORENTZ

　　/*--------6、哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资。---------*/

　　SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY

　　2 FROM EMPLOYEES E,EMPLOYEES M

　　3 WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;

　　FIRST_NAME SALARY FIRST_NAME SALARY

　　-------------------- ---------- -------------------- ----------

　　Lisa 11500.00 Gerald 11000.00

　　Ellen 11000.00 Eleni 10500.00

　　--要是只列出员工的名字与工资的话，还可以这样：

　　SQL> SELECT E.FIRST_NAME,E.SALARY

　　2 FROM EMPLOYEES E WHERE E.SALARY >

　　3 (SELECT M.SALARY FROM EMPLOYEES M

　　4 WHERE E.MANAGER_ID = M.EMPLOYEE_ID);

　　FIRST_NAME SALARY

　　-------------------- ----------

　　Lisa 11500.00

　　Ellen 11000.00

　　/*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/

　　SQL> SELECT FIRST_NAME FROM EMPLOYEES

　　2 WHERE DEPARTMENT_ID IN

　　3 (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')

　　4 AND LAST_NAME <> 'Chen';

　　FIRST_NAME

　　--------------------

　　Nancy

　　Daniel

　　Ismael

　　Jose Manuel

　　Luis

　　--或者--

　　SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2

　　2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID

　　3 AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';

　　FIRST_NAME

　　--------------------

　　Nancy

　　Daniel

　　Ismael

　　Jose Manuel

　　Luis

　　/*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/

　　SQL> SELECT FIRST_NAME FROM EMPLOYEES

　　2 WHERE JOB_ID IN

　　3 (SELECT JOB_ID FROM EMPLOYEES

　　4 WHERE LAST_NAME = 'De Haan')

　　5 AND LAST_NAME <> 'De Haan';

　　FIRST_NAME

　　--------------------

　　Neena

　　--或者--

　　SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2

　　2 WHERE E1.JOB_ID = E2.JOB_ID

　　3 AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';

　　FIRST_NAME

　　--------------------

　　Neena

　　/*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/

　　SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES

　　2 WHERE DEPARTMENT_ID NOT IN(

　　3 SELECT DEPARTMENT_ID FROM HR.EMPLOYEES

　　4 WHERE LAST_NAME = 'Hall');

　　FIRST_NAME||''||LAST_NAME

　　----------------------------------------------

　　Steven King

　　Neena Kochhar

　　Lex De Haan

　　Alexander Hunold

　　Bruce Ernst

　　David Austin

　　Valli Pataballa

　　Diana Lorentz

　　Nancy Greenberg

　　--...初始有72条数据

　　--或者：

　　SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2

　　2 WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)

　　3 and e2.LAST_NAME(+) = 'Hall'

　　4 and e2.LAST_NAME IS NULL;

　　/*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/

　　SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES

　　2 WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES

　　3 WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');

　　FIRST_NAME||''||LAST_NAME

　　----------------------------------------------

　　Steven King

　　Neena Kochhar

　　Lex De Haan

　　Alexander Hunold

　　----...初始有77条数据

　　--------------------------------------------------------------------------------

　　/*--------11、显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称。---------*/

　　SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,

　　2 E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY

　　3 FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L

　　4 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

　　5 AND D.LOCATION_ID = L.LOCATION_ID

　　6 AND E.COMMISSION_PCT IS NOT NULL;

　　/*--------12、显示Executive部门有哪些职位。---------*/

　　SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D

　　2 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

　　3 AND D.DEPARTMENT_NAME = 'Executive';

　　JOB_ID

　　----------

　　AD_PRES

　　AD_VP

　　/*--------13、整个公司中，最高工资和最低工资相差多少。---------*/

　　SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;

　　MAX(SALARY)-MIN(SALARY)

　　-----------------------

　　21900

　　/*--------14、提成大于0 的人数。---------*/

　　SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES

　　2 WHERE COMMISSION_PCT > 0;

　　提成大小0的人数

　　---------------

　　--或者

　　SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数

　　2 FROM HR.EMPLOYEES

　　3 WHERE COMMISSION_PCT > 0;

　　提成大小0的人数

　　---------------

　　/*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/

　　SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,

　　2 MIN(NVL(SALARY,0)) AS 最低工资,

　　3 SUM(NVL(SALARY,0)) AS 工资总和,

　　4 ROUND(AVG(NVL(SALARY,0))) AS 平均工资

　　5 FROM HR.EMPLOYEES;

　　最高工资最低工资工资总和平均工资

　　---------- ---------- ---------- ---------

　　24000 2100 698011 6523

　　/*--------16、整个公司有多少个领导。---------*/

　　SQL> SELECT COUNT(DISTINCT(MANAGER_ID)) FROM HR.EMPLOYEES

　　2 WHERE MANAGER_ID IS NOT NULL;

　　COUNT(DISTINCT(MANAGER_ID))

　　---------------------------

　　/*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工：

　　名字、工资、入职日期。---------*/

　　SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,

　　2 E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期

　　3 FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2

　　4 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID

　　5 AND E1.HIRE_DATE > E2.HIRE_DATE

　　6 AND E1.SALARY > E2.SALARY

　　7 ORDER BY 工资 DESC;

　　姓名工资入职日期

　　---------------------------------------------- ---------- -----------

　　John Russell 14000.00 1996-10-1

　　Karen Partners 13500.00 1997-1-5

　　Alberto Errazuriz 12000.00 1997-3-10

　　Nancy Greenberg 12000.00 1994-8-17

　　Lisa Ozer 11500.00 1997-3-11

　　Ellen Abel 11000.00 1996-5-11

　　Gerald Cambrault 11000.00 1999-10-15

　　Clara Vishney 10500.00 1997-11-11

　　Eleni Zlotkey 10500.00 2000-1-29

　　Harrison Bloom 10000.00 1998-3-23

　　Peter Tucker 10000.00 1997-1-30

　　Tayler Fox 9600.00 1998-1-24

　　Danielle Greene 9500.00 1999-3-19

　　David Bernstein 9500.00 1997-3-24

　　Peter Hall 9000.00 1997-8-20

　　Alyssa Hutton 8800.00 1997-3-19

　　Jonathon Taylor 8600.00 1998-3-24

　　Adam Fripp 8200.00 1997-4-10

　　Christopher Olsen 8000.00 1998-3-30

　　Jack Livingston 8000.00 1998-4-23

　　Matthew Weiss 8000.00 1996-7-18

　　Jose Manuel Urman 7800.00 1998-3-7

　　Nanette Cambrault 7500.00 1998-12-9

　　William Smith 7400.00 1999-2-23

　　Elizabeth Bates 7300.00 1999-3-24

　　Charles Johnson 7211.00 2000-1-4

　　Mattea Marvins 7200.00 2000-1-24

　　Shanta Vollman 6500.00 1997-10-10

　　Kevin Mourgos 5800.00 1999-11-16

　　Nandita Sarchand 4200.00 1996-1-27

　　Alexis Bull 4100.00 1997-2-20

　　Sarah Bell 4000.00 1996-2-4

　　Britney Everett 3900.00 1997-3-3

　　Kelly Chung 3800.00 1997-6-14

　　Jennifer Dilly 3600.00 1997-8-13

　　Julia Dellinger 3400.00 1998-6-24

　　Laura Bissot 3300.00 1997-8-20

　　Julia Nayer 3200.00 1997-7-16

　　Samuel McCain 3200.00 1998-7-1

　　Stephen Stiles 3200.00 1997-10-26

　　Winston Taylor 3200.00 1998-1-24

　　Alana Walsh 3100.00 1998-4-24

　　Jean Fleaur 3100.00 1998-2-23

　　Anthony Cabrio 3000.00 1999-2-7

　　Kevin Feeney 3000.00 1998-5-23

　　Michael Rogers 2900.00 1998-8-26

　　Shelli Baida 2900.00 1997-12-24

　　Timothy Gates 2900.00 1998-7-11

　　Girard Geoni 2800.00 2000-2-3

　　Mozhe Atkinson 2800.00 1997-10-30

　　Vance Jones 2800.00 1999-3-17

　　Irene Mikkilineni 2700.00 1998-9-28

　　John Seo 2700.00 1998-2-12

　　Donald OConnell 2600.00 1999-6-21

　　Douglas Grant 2600.00 2000-1-13

　　Randall Matos 2600.00 1998-3-15

　　Martha Sullivan 2500.00 1999-6-21

　　Randall Perkins 2500.00 1999-12-19

　　Ki Gee 2400.00 1999-12-12

　　Hazel Philtanker 2200.00 2000-2-6

　　Steven Markle 2200.00 2000-3-8

　　61 rows selected

上一篇 »前端第三篇---前端基础之JavaScript
下一篇 »Oracle数据库查询分页SQL语句

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

相关推荐

ORACLE SQL 强制索引 提升查询效率

Oracle实现Top查询，sql分页

lyt经典版MySQL基础——进阶6：连接查询-sql92语法-内连接

Oracle学习，四_SQL函数

Oracle分页查询sql语句

Oracle查询正在执行的SQL语句

经典SQL数据库面试题以及答案—Oracle版本-SQL全部在plsql开发编写-欢迎提问

简单搞一下 Oracle 存储过程动态SQL之获取查询总数！

ORACLE SQL 强制索引提升查询效率