/*--------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;
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;
