发布时间 : 星期四 文章Oracle+SQL:经典查询练手四篇更新完毕开始阅读
--或采用以下方法
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 最高工资,