Jose Manuel Luis --或者--
SQL>SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 2WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
3AND 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 2WHERE JOB_ID IN
3 (SELECT JOB_ID FROM EMPLOYEES 4WHERE LAST_NAME ='De Haan') 5AND LAST_NAME <>'De Haan';
FIRST_NAME
-------------------- Neena
--或者--
SQL>SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2 2WHERE E1.JOB_ID = E2.JOB_ID
3AND 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 2WHERE DEPARTMENT_ID NOTIN(
3SELECT DEPARTMENT_ID FROM HR.EMPLOYEES 4WHERE 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 2WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+) 3and e2.LAST_NAME(+) ='Hall' 4and e2.LAST_NAME ISNULL;
/*-------10、哪些员工跟William（FIRST_NAME）、Smith(LAST_NAME)做不一样的职位。--------*/
SQL>SELECT FIRST_NAME ||' '|| LAST_NAME FROM HR.EMPLOYEES 2WHERE JOB_ID <> (SELECTDISTINCT JOB_ID FROM EMPLOYEES 3WHERE 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
3FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L 4WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 5AND D.LOCATION_ID = L.LOCATION_ID
6AND E.COMMISSION_PCT ISNOTNULL;
/*--------12、显示Executive部门有哪些职位。---------*/
SQL>SELECTDISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D 2WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID 3AND D.DEPARTMENT_NAME ='Executive'; JOB_ID ---------- AD_PRES AD_VP
/*--------13、整个公司中，最高工资和最低工资相差多少。---------*/ SQL>SELECTMAX(SALARY) -MIN(SALARY) FROM HR.EMPLOYEES;
MAX(SALARY)-MIN(SALARY) ----------------------- 21900
/*--------14、提成大于0 的人数。---------*/
SQL>SELECTCOUNT(*) AS提成大小0的人数FROM HR.EMPLOYEES 2WHERE COMMISSION_PCT >0;
提成大小0的人数 --------------- 35 --或者
SQL>SELECTCOUNT(COMMISSION_PCT) AS提成大小0的人数 2FROM HR.EMPLOYEES
3WHERE COMMISSION_PCT >0; 提成大小0的人数 --------------- 35
/*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/
SQL>SELECTMAX(NVL(SALARY,0)) AS最高工资, 2MIN(NVL(SALARY,0)) AS最低工资, 3SUM(NVL(SALARY,0)) AS工资总和, 4ROUND(AVG(NVL(SALARY,0))) AS平均工资 5FROM HR.EMPLOYEES;
最高工资最低工资工资总和平均工资
---------- ---------- ---------- --------- 2400021006980116523
/*--------16、整个公司有多少个领导。---------*/
SQL>SELECTCOUNT(DISTINCT(MANAGER_ID)) FROM HR.EMPLOYEES 2WHERE MANAGER_ID ISNOTNULL;
COUNT(DISTINCT(MANAGER_ID)) --------------------------- 18
/*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工： 名字、工资、入职日期。---------*/
SQL>SELECTDISTINCT E1.FIRST_NAME ||' '|| E1.LAST_NAME AS姓名, 2 E1.SALARY AS工资,E1.HIRE_DATE AS入职日期 3FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2 4WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID 5AND E1.HIRE_DATE > E2.HIRE_DATE 6AND E1.SALARY > E2.SALARY 7ORDERBY工资DESC;
姓名工资入职日期
---------------------------------------------- ---------- ----------- John Russell 14000.001996-10-1 Karen Partners 13500.001997-1-5 Alberto Errazuriz 12000.001997-3-10 Nancy Greenberg 12000.001994-8-17 Lisa Ozer 11500.001997-3-11 Ellen Abel 11000.001996-5-11
Gerald Cambrault 11000.001999-10-15 Clara Vishney 10500.001997-11-11 Eleni Zlotkey 10500.002000-1-29 Harrison Bloom 10000.001998-3-23 Peter Tucker 10000.001997-1-30 Tayler Fox 9600.001998-1-24 Danielle Greene 9500.001999-3-19 David Bernstein 9500.001997-3-24 Peter Hall 9000.001997-8-20 Alyssa Hutton 8800.001997-3-19 Jonathon Taylor 8600.001998-3-24 Adam Fripp 8200.001997-4-10
Christopher Olsen 8000.001998-3-30 Jack Livingston 8000.001998-4-23
Matthew Weiss 8000.001996-7-18 Jose Manuel Urman 7800.001998-3-7 Nanette Cambrault 7500.001998-12-9 William Smith 7400.001999-2-23 Elizabeth Bates 7300.001999-3-24 Charles Johnson 7211.002000-1-4 Mattea Marvins 7200.002000-1-24 Shanta Vollman 6500.001997-10-10 Kevin Mourgos 5800.001999-11-16 Nandita Sarchand 4200.001996-1-27 Alexis Bull 4100.001997-2-20 Sarah Bell 4000.001996-2-4 Britney Everett 3900.001997-3-3 Kelly Chung 3800.001997-6-14 Jennifer Dilly 3600.001997-8-13 Julia Dellinger 3400.001998-6-24 Laura Bissot 3300.001997-8-20 Julia Nayer 3200.001997-7-16 Samuel McCain 3200.001998-7-1 Stephen Stiles 3200.001997-10-26 Winston Taylor 3200.001998-1-24 Alana Walsh 3100.001998-4-24 Jean Fleaur 3100.001998-2-23 Anthony Cabrio 3000.001999-2-7 Kevin Feeney 3000.001998-5-23 Michael Rogers 2900.001998-8-26 Shelli Baida 2900.001997-12-24 Timothy Gates 2900.001998-7-11 Girard Geoni 2800.002000-2-3 Mozhe Atkinson 2800.001997-10-30 Vance Jones 2800.001999-3-17 Irene Mikkilineni 2700.001998-9-28 John Seo 2700.001998-2-12
Donald OConnell 2600.001999-6-21 Douglas Grant2600.002000-1-13 Randall Matos 2600.001998-3-15 Martha Sullivan 2500.001999-6-21 Randall Perkins 2500.001999-12-19 Ki Gee 2400.001999-12-12
Hazel Philtanker 2200.002000-2-6 Steven Markle 2200.002000-3-8
61 rows selected