Oracle SQL 经典查询实例三

nzypk

贡献于2012-11-09

字数:8788 关键词: 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

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档