本文使用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