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

posted @ 2011-07-08 11:16  左正  阅读(381)  评论(0编辑  收藏  举报