Oracle SQL：经典查询练手四篇
/*--------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
0
第1页：经典查询练手第一篇：结构与表第2页：经典查询练手第一篇：解答方法(1)第3页：经典查询练手第一篇：解答方法(2)第4页：经典查询练手第一篇：解答方法(3)第5页：经典查询练手第二篇：结构与表第6页：经典查询练手第二篇：表的现有数据第7页：经典查询练手第二篇：解答方法(1)第8页：经典查询练手第二篇：解答方法(2)第9页：经典查询练手第三篇：表和结构第10页：经典查询练手第三篇：解答方法(1)第11页：经典查询练手第三篇：解答方法(2)第12页：经典查询练手第三篇：解答方法(3)第13页：经典查询练手第三篇：解答方法(4)第14页：经典查询练手第三篇：解答方法(5)第15页：经典查询练手第四篇：表和结构第16页：经典查询练手第四篇：ER图及问题第17页：经典查询练手第四篇：解答方法(1)第18页：经典查询练手第四篇：解答方法(2)第19页：经典查询练手第四篇：解答方法(3)
相关文章
关注我们
