當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2024/9/20 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 ORACLE SQL：经典查询练手第三篇小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

　　本文使用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
　　￥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
　　$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的人數
　　---------------
　　35
　　--或者
　　SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人數
　　2?? FROM HR.EMPLOYEES
　　3?? WHERE COMMISSION_PCT > 0;
　　提成大小0的人數
　　---------------
　　35
　　/*--------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))
　　---------------------------
　　18
　　/*--------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

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的ORACLE SQL：经典查询练手第三篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。