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