--或采用以下方法

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 最高工资,