oracle经典查询练手第三篇(3)

--或采用以下方法

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、哪些员工的工资高于他直接上司的