Oracle SQL：經典查詢練手第三篇

作者：胡勇 2011-06-22 13:26:37

數據庫 Oracle

PostgreSQL 是一種對象-關系型數據庫管理系統(ORDBMS)，功能強大，特性豐富的自由軟件數據庫系統。這個起源于伯克利(BSD)的數據庫研究計劃目前已經成為一項國際開發項目，并且有非常廣泛的用戶。在靈活的BSD風格許可證下發行。

本文與大家共同討論與分享ORACLE SQL的一些常用經典查詢，歡迎大家補充，同時你認為有那些經典的也可分享出來。在本文中，對每一個問題，你要是認為有什么更好的解決方法也歡迎你及時提出。交流與分享才能共同進步嘛，感謝！

接上兩篇:

Oracle SQL：經典查詢練手第一篇

Oracle SQL：經典查詢練手第二篇

本篇相對上兩篇來說難度有所增加，繼續努力，通過我為大家設立的這個系列，只要你對每一篇，每一個試題都實踐測試，認真練習。我相信你對常用、經典的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. 列出在同一部門入職日期晚但工資高于其他同事的員工：名字、工資、入職日期。

#p#

各試題解答如下（歡迎大家指出不同的方法或建議！）：

/*--------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

#p#

/*--------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

#p#

/*--------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

原文鏈接：http://www.cnblogs.com/huyong/archive/2011/06/20/2085137.html

【編輯推薦】

PL/SQL開發中動態SQL的使用與過程分頁
詳解Oracle的幾種分頁查詢語句
Oracle分頁小談
簡單說說Oracle分區
Oracle數據庫日常維護知識總結

責任編輯：艾婧來源： EricHu's Tech Space

Oracle