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 ×î¸ß¹¤×Ê,