CAPITOLUL 9
                                       
   
   
SUBCERERI

   
   
   In acest capitol se vor prezenta alte caracteristici ale declaratiei
   SELECT, cum ar fi cereri continute in clauza WHERE sau HAVING ale
   altei decla- ratii SQL.
   
  Subcereri ascunse
  
   
   
    O subcerere este o declaratie SELECT care este ascunsa in interiorul
   altei declaratii SELECT si care intoarce rezultate intermediare.
   
   
   
   De exemplu:

     SELECT column1, column2, ...
     FROM table
     WHERE column =
                    (SELECT column
                     FROM table
                     WHERE condition)


   
   
   Subcererea este adesea referita ca un subselect sau ca un SELECT
   interior; in general, subcererea se executa prima si rezultatul sau
   este folosit pentru a completa conditia cererii principale sau a
   cererii externe.Folosirea sub- cererilor permite consruirea de comenzi
   puternice pornind de la unele simple. Cererea ascunsa poate fi foarte
   folositoare cind este nevoie sa fie selectate linii dintr-o tabela
   printr-o conditie ce depinde de date din propria tabela.
   
  Subcereri ce intorc o linie
  
   
   
    Pentru a gasi angajatul care cistiga salariul minim din companie
   (salariul minim este o cantitate necunoscuta), trebuie parcursi doi
   pasi:
   
    1. Gasirea salariului minim:

     SELECT MIN(SAL)
     FROM EMP;

     MIN(SAL)
    ----------
       800
   
    2. Gasirea angajatului care cistiga salariul minim:


     SELECT ENAME, JOB, SAL
     FROM EMP
     WHERE SAL = (cel mai mic salariu care este cunoscut)


   
   
   Putem combina cele doua cereri ca o subcerere ascunsa:

     SELECT ENAME, JOB, SAL
     FROM EMP
     WHERE SAL = (SELECT MIN(SAL)
                  FROM EMP);

     ENAME     JOB      SAL
    -------   ------   -----
    SMITH     CLERK     800

      Cum sint prelucrate subcererile ascunse ?
      
   
   
    O declarartie SELECT poate fi considerata ca un bloc de
   cerere.Exemplul de mai sus consta din doua blocuri de cerere - cererea
   principala si cererea in- terioara.
   
   Declaratia SELECT interioara sau blocul de cerere este executata
   prima, producind un rezultat al cererii: 800.Blocul de cerere
   principal este apoi prelucrat si foloseste valoarea intoarsa de
   cererea interioara pentru a com- pleta conditia de cautare.Deci,
   cererea principala in final va arata in felul urmator:

     SELECT ENAME, SAL, DEPTNO
     FROM EMP
     WHERE SAL = 800;


   
   
   In exemplul de mai sus, 800 este o valoare unica.Subcererea care a
   intors valoarea 800 se numeste subcerere ce intoarce o singura
   linie.Cind o subcerere intoarce doar o linie, va fi folosit un
   operator logic sau un operator de com- paratie.De exemplu: =, , Pentru
   a gasi toti angajatii ce au aceeasi functie ca BLAKE, vom introduce:

     SELECT ENAME, JOB
     FROM EMP
     WHERE JOB = (SELECT JOB
                  FROM EMP
                  WHERE ENAME = 'BLAKE');

     ENAME       JOB
    -------     -----
     JONES     MANAGER
     BLAKE     MANAGER
     CLARK     MANAGER

   
   
   Cererea interioara intoarce functia lui BLAKE, care este folosita in
   partea dreapta a clauzei WHERE di cererea principala (dupa operatorul
   de comparatie).
   
  Subcereri ce intorc mai mult de o linie
  
   
   
    Urmatoarea cerere gaseste angajatii care au salariul egal cu salariul
   minim din fiecare departament:

     SELECT ENAME, SAL, DEPTNO
     FROM EMP
     WHERE SAL IN
                  (SELECT MIN(SAL)
                   FROM EMP
                   GROUP BY DEPTNO);




     ENAME        SAL        DEPTNO
    -------      -----      ---------
    SMITH         800          20
    JAMES         950          30
    MILLER       1300          10

   
   
   Observati ca, cererea interioara are clauza GROUP BY.Aceasta inseamna
   ca va intoarce mai mult decit o valoare.Deci este nevoie sa folosim un
   operator de comparatie multi linie.In acest caz, operatorul IN trebuie
   sa fie folosit, deoarece rezulta o lista de valori.
   
   Rezultatul obtinut nu arata si departamentul in care lucreaza
   angajatul.
   Deoarece se compara doar valorile salariilor, cererea interioara poate
   intoar- ce o valoare simpla, pentru ca el cauta cel mai mic salariu
   pentru unul din departamente, nu in mod necesar departamentul propriu
   al angajatului.Prin urmare, cererea poate fi rescrisa pentru a gasi
   combinatia salariul angaja- tului si numarul departamentului cu
   salariul minim si numarul departamentului:
   
    Compararea a mai multor valori
    
   
   Urmatoarea cerere ggaseste angajatii care cistiga salariul cel mai mic
   din departamentul lor:

     SELECT ENAME, SAL, DEPTNO
     FROM EMP
     WHERE (SAL, DEPTNO) IN
                             (SELECT MIN(SAL), DEPTNO
                              FROM EMP
                              GROUP BY DEPTNO);

     ENAME       SAL      DEPTNO
    -------     -----    ---------
     SMITH       800        20
     JAMES       950        30
     MILLER     1300        10

   
   
   Cererea de mai sus compara o pereche de coloane.
   
   Observatie : coloanele din partea stinga a conditiei de cautare sint
   in pa- ranteze si fiecare coloana este separata printr-o virggula.
   
   Coloanele listate in clauza SELECT a subcererii trebuie sa fie in
   aceeasi ordine ca si lista coloanelor dintre paranteze din clauza
   WHERE a cererii externe.
   
   Coloanele intoarse de cererea interioara trebuie, de asemenea sa se
   potri- veasca in numar si tip de date cu coloanele cu care ele sint
   comparate in cererea externa.
   
   De exemplu :

     ...WHERE (numcolumn, charcolumn) =
              (SELECT datecolumn, numcolumn, charcolumn ...


   
   nu este permis.
   
  Erori intilnite
  
   
   
    Cind o subcerere intoarce mai mult decit o linie si este folosit un
   opera- tor de comparatie pt o singura linie, SQL*Plus da urmatorul
   mesaj de eroare:

     SELECT ENAME, SAL, DEPTNO
     FROM EMP
     WHERE SAL = (SELECT MIN(SAL)
                  FROM EMP
                  GROUP BY DEPTNO);


   
   
   Eroare : subcerere ce trebuie sa intoarca o singura linie intoarce mai
   mult decit o linie.
   
   Nu este selectata nici o inregistrare.
   
   Daca cererea interioara nu intoarce nici o linie, va fi dat urmatorul
   mesaj de eroare :

     SELECT ENAME, JOB
     FROM EMP
     WHERE JOB = (SELECT JOB
                  FROM EMP
                  WHERE ENAME = 'SMITHE');


   
   
   Eroare : subcerere ce trebuie sa intoarca o singura linie nu intoarce
   nici o linie.
   
   Nu este selectata nici o inregistrare.
   
  Operatorii SOME/ANY, ALL
  
   
   Operatorii ANY sau ALL pot fi folositi pentru subcererile care intorc
   mai mult de o linie.Ei sint folositi in clauzele WHERE sau HAVING in
   legatura cu operatorii logici (=, !=, , >=, ANY (sau sinonimul sau
   SOME) compara o valoare cu fiecare valoare intoarsa de o subcerere.
   Pentru a afisa angajatii care cistiga mai mult decit cel mai mic
   salariu din departamentul 30, introducem :

     SELECT ENAME, SAL, JOB, DEPTNO
     FROM EMP
     WHERE SAL > SOME (SELECT DISTINCT SAL
                       FROM EMP
                       WHERE DEPTNO = 30)
     ORDER BY SAL DESC;

     ENAME       SAL        JOB        DEPTNO
    -------     -----      -----      --------
     KING       5000     PRESIDENT       10
     SCOTT      3000     ANALYST         20
     FORD       3000     ANALYST         20
     JONES      2975     MANAGER         20
     BLAKE      2850     MANAGER         30
     CLARK      2450     MANAGER         10
     ALLEN      1600     SALESMAN        30
     MILLER     1300     CLERK           10
     WARD       1250     SALESMAN        30
     ADAMS      1100     CLERK           20

   
   
   Cel mai mic salariu din departamentul 30 este 950$ (a lui
   James).Cererea principala intoarce angajatii care cistiga un salariu
   mai mare ca salariul minim din departamentul 30.Asa ca '> ANY'
   inseamna mai mare ca minim.'=ANY' este echivalent cu IN.
   
   Cind se foloseste SOME/ANY, DISTINCT este frecvent folosit pentru a
   impiedica sa se selecteze liniile de mai multe ori.
   ALL compara o valoare cu fiecare valoare intoarsa de o subcerere.
   Urmatoarea cerere gaseste angajatii care cistiga mai mult ca fiecare
   angajat din departamentul 30 :

     SELECT ENAME, SAL, JOB, DEPTNO
     FROM EMP
     WHERE SAL > ALL (SELECT DISTINCT SAL
                      FROM EMP
                      WHERE  DEPTNO = 30)
     ORDER BY SAL DESC;

     ENAME       SAL          JOB        DEPTNO
    -------     -----        -----      ---------
     KING        5000      PRESIDENT       10
     SCOTT       3000      ANALYST         20
     FORD        3000      ANALYST         20
     JONES       2975      MANAGER         20

   
   
   Cel mai mare salariu din departamentul 30 este 250$ (a lui Blake), asa
   ca cererea intoarce acei angajati ai caror salariu este mai mare ca
   salariul maxim din departamentul 30, prin urmare mai mare ca fiecare
   salariu din de- partament.
   
   Operatorul NOT poate fi folosit cu IN, ANY sau ALL.
   
  Clauza HAVING cu subcereri ascunse
     
    Subcererile ascunse pot fi folosite de asemenea in clauza HAVING.
   
   (Observatie : clauza WHERE se refera la o singura linie si clauza
   HAVING la grupuri de linii specificate in clauza GROUP BY.)
   
   De exemplu, pentru a afisa departamentul(ele) care au un salariu mediu
   mai mare ca departamentul 30, introducem :

     SELECT DEPTNO, AVG(SAL)
     FROM EMP
     HAVING AVG(SAL) > (SELECT AVG(SAL)
                        FROM EMP
                        WHERE DEPTNO = 30)
     GROUP BY DEPTNO;

     DEPTNO      AVG(SAL)
    --------    ----------
      10         2916.66667
      20         2175

   
   
   Pentru a construi o cerere care gaseste functia cu cel mai mare
   salariu mediu, introducem :


     SELECT JOB, AVG(SAL)
     FROM EMP
     GROUP BY JOB
     HAVING AVG(SAL) = (SELECT MAX(AVG(SAL))
                        FROM EMP
                        GROUP BY JOB);

     JOB          AVG(SAL)
    -----        ----------
     PRESIDENT     5000

   
   
   Mai intii cererea interioara gaseste salariul mediu pentru fiecare
   grup de functii diferit si functia MAX alege cel mai mare salariu
   mediu.Acea valoare (5000) este folosita in clauza HAVING.Clauza GROUP
   BY din cererea principala este necesara pentru ca lista ce urmeaza
   dupa SELECT-ul din cererea principala contine atit o coloana agregat
   cit si o coloana non-agragat.
   
  Ordonarea datelor cu subcereri
  
   
       Nu poate exista o clauza ORDER BY intr-o subcerere.
   
   Regula este ca poate exista doar o singura clauza ORDER BY pentru o
   declaRatie SELECT si, daca este specificata, trebuie sa fie ultima
   clauza din comanda SELECT.
   
  Subcereri ascunse
  
   
   
    Subcererile pot fi ascunse (folosite in interiorul unei subcereri) :
   
   Afisati numele, functia si data angajarii pentru angajatii al caror
   salariu este mai mare ca cel mai mare salariu din orice departament de
   vinzari.

     SELECT ENAME, JOB, HIREDATE, SAL
     FROM EMP
     WHERE SAL > (SELECT MAX(SAL)
                  FROM EMP
                  WHERE DEPTNO = (SELECT DEPTNO
                                  FROM DEPT
                                  WHERE DNAME = 'SALES'));

     ENAME        JOB       HIREDATE       SAL
    -------      -----     ----------     -----
     JONES      MANAGER     02-APR-81      2975
     SCOTT      ANALYST     09-DEC-82      3000
     KING       PRESIDENT   17-NOV-81      5000
     FORD       ANALYST     03-DEC-81      3000

    Limitele de imbricare
    
   
   
    Limita nivelelor de imbricare pentru o subcerere este 255. 
   
  Reguli de scriere a cererilor
  
   
     * cererea interioara trebuie sa fie inclusa intre paranteze si
       trebuie sa fie in partea dreapta a conditiei.
     * subcererile nu pot avea clauza ORDER BY.
     * clauza ORDER BY apare la sfirsitul declaratiei SELECT principale.
     * coloanele multiple din lista din SELECT a cererii interioare
       trebuie sa fie in aceeasi ordine ca si coloanele ce apar in
       conditia din clauza cere- rii principale.De asemenea mai trebuie
       sa corespunda si tipul si numarul coloanelor listate.
     * subcererile sint intotdeauna executate de la cea mai adinca
       imbricare pina la nivelul principal de imbricare, daca nu sint
       subcereri corelate (acestea vor fi discutate mai tirziu).
     * pot fi folositi operatorii logici si SQL la fel de bine ca si ANY
       si ALL.
     * subcererile pot
          + intoarce una sau mai multe linii;
          + intoarce una sau mai multe coloane;
          + folosi GROUP BY sau functii de grup;
          + fi folosite inlantuite cu predicate multiple AND sau OR in
            aceesi cerere externa.
          + uni tabele.
          + recupera dintr-o tabela diferita de cea a cererii exterioare.
          + apare in declaratii SELECT, UPDATE, DELETE, INSERT, CREATE
            TABLE.
          + fi corelate cu o cerere exterioara.
          + folosi operatori de multimi.
            
   
   
  Subcereri corelate
  
   
   
   O subcerere corelata este o subcerere care este executata o data
   pentru fiecare linie candidat considerata de cererea principala si
   care la executie foloseste o valoare dintr-o coloana din cererea
   exterioara.Aceasta determina ca subcererea corelata sa fie prelucrata
   intr-un mod diferit de subcererea ascunsa obisnuita.
   
   O subcerere corelata este identificata prin folosirea unei coloane a
   cererii exterioare in clauza predicatului cererii interioare.
   
   Cu o subcerere ascunsa obisnuita, SELECT-ul interior ruleaza primul si
   se executa o data, intorcind valori ce vor fi folosite de cererea
   principala.Pe de alta parte, o subcerere corelata se executa o data
   pentru fiecare linie candidat considerata de cererea externa.Cererea
   interioara este dirijata de cererea externa.
   
   Pasii de executie ai unei subcereri corelate :
    1. Se obtine linia candidat. (obtinuta de cererea exterioara)
    2. Se executa cererea interioara folosind valoarea liniei candidat.
    3. Se folosesc valorile rezultate din cererea interioara pentru a
       pastra sau pentru a inlatura linia candidat.
    4. Se repeta pina nu mai ramine nici o linie candidat.
       
   
   
   Desi subcererea corelata se executa repetat, o data pentru fiecare
   linie in cererea principala, aceasta nu inseamna ca subcererile
   corelate sint mai putin eficiente ca subcererile necorelate
   obisnuite.Se va vorbi despre eficienta mai tirziu in acest capitol.
   
   Putem folosi o subcerere corelata pentru a gasi angajatii care cistiga
   un salariu mai mare ca salariul mediu al departamentului lor :

     SELECT EMPNO, ENAME, SAL, DEPTNO
     FROM EMP E
     WHERE SAL > (SELECT AVG(SAL)
                  FROM EMP
                  WHERE DEPTNO = E.DEPTNO)
     ORDER BY DEPTNO;

     EMPNO      ENAME       SAL       DEPTNO
    -------    -------     ------    --------
     7839       KING        5000        10
     7566       JONES       2975        20
     7788       SCOTT       3000        20
     7902       FORD        3000        20
     7499       ALLEN       1600        30
     7698       BLAKE       2850        30

   
   
   Putem observa imediat ca este o cerere corelata pentru ca am folosit o
   coloana din SELECT-ul extern in clauza WHERE din SELECT-ul interior.
   
   
   Observati ca alias-ul este necesar doar pentru a indeparta
   ambiguitatea pentru numele coloanelor.
   
   Sa analizam exemplul de mai sus folosind tabela EMP :
   
   Cererea principala
    1. Se selecteaza prima linie candidat - Smith din departamentul 20
       cistiga 800 œ.
    2. EMP in clauza FROM are alias-ul E care obtine coloana DEPTNO
       referita in clauza WHERE a cererii interioare.
    3. Clauza WHERE compara 800 cu valoarea intoarsa de cererea
       interioara.
       
       
       Cererea interioara
    4. Calculeaza AVG(SAL) pentru departamentul angajatului.
    5. Valoarea departamentului din clauza WHERE este departamentul
       candidatului (E.DEPTNO), valoare transmisa cererii interioare din
       coloana DEPTNO a ce- rerii exterioare.
    6. AVG(SAL) pentru departamentul lui Smith - 20 - este 2175 œ.
    7. Linia candidat nu indeplineste conditia, asa ca este indepartata.
    8. Se repeta de la pasul 1 pentru urmatoarea linie candidat; ALLEN
       din de- partamentul 30 cistiga 1600 œ.
       
   
   
   Selectia liniilor candidat continua cu verificarea conditiei ce apare
   in re- zultatul cererii.
   
   Observatie : o subcerere corelata este semnalata de un nume de
   coloana, un nume de tabela sau un alias de tabela in clauza WHERE,
   care se refera la va- loarea unei coloane in fiecare linie candidat
   din SELECT-ul exterior.De asemenea subcererea corelata se executa
   repetat pentru fiecare linie candidat in cererea principala.
   
   Comenzile UPDATE pot contine subcereri corelate :

     UPDATE EMP E
     SET (SAL, COMM) = (SELECT AVG(SAL) * 1.1, AVG(COMM)
                        FROM EMP
                        WHERE DEPTNO = E.DEPTNO)
         HIREDATE = '11-JUN-85';


   
   
    Operatori
    
   
   
   
   
   Cind se folosesc declaratii SELECT ascunse, operatorii logici, precum
   si ANY si ALL sint toti valizi.In plus poate fi folosit operatorul
   EXISTS.
   
      Operatorul EXISTS
      
   
   
    Operatorul EXISTS este frecvent folosit cu subcererile corelate.El
   testeaza daca o valoare exista (NOT EXISTS specifica daca ceva nu
   exista).Daca valoarea exista se intoarce TRUE; daca valoarea nu exista
   se intoarce FALSE.
   
   Pentru a gasi angajatii ce au cel putin un subordonat, introducem :

     SELECT EMPNO, ENAME, JOB, DEPTNO
     FROM EMP E
     WHERE EXISTS (SELECT EMPNO
                   FROM EMP
                   WHERE EMP.MGR = E.EMPNO)
     ORDER BY EMPNO;



     EMPNO     ENAME      JOB       DEPTNO
    -------   -------    -----     --------
     7566      JONES    MANAGER       20
     7698      BLAKE    MANAGER       30
     7782      CLARK    MANAGER       10
     7788      SCOTT    ANALYST       20
     7839      KING     PRESIDENT     10
     7902      FORD     ANALYST       20

   
   
   Sa gasim toti angajatii al caror departament nu este in tabela DEPT :

     SELECT EMPNO, ENAME, DEPTNO
     FROM EMP
     WHERE NOT EXISTS (SELECT DEPTNO
                       FROM DEPT
                       WHERE DEPT.DEPTNO = EMP.DEPTNO);


   
   
   Nu va fi selectata nici o inregistrare.
   
   Alt mod de a gasi departamentul care nu are nici un angajat este :

     SELECT DEPTNO, DNAME
     FROM DEPT D
     WHERE NOT EXISTS (SELECT 1
                       FROM EMP E
                       WHERE E.DEPTNO = D.DEPTNO);

     DEPTNO        DNAME
    --------      -------
      40         OPERATIONS

   
   
   Observati ca SELECT-ul interior nu este necesar sa intoarca o valoare
   spe- cifica, asa ca poate fi selectata o cifra.
   
      De ce sa folosim o subcerere corelata ?
      
   
   
    Subcererea corelata este un mod de a 'citi' fiecare linie din tabela
   si de a compara valorile din fiecare linie cu datele inrudite.Aceasta
   este folosita oricind o subcerere trebuie sa intoarca un rezultat
   diferit sau o multime de rezultate pentru fiecare linie candidat
   considerata de cererea principala.Cu alte cuvinte, o subcerere
   corelata este folosita pentru a raspunde la intre- bari cu mai multe
   subpuncte al caror raspuns depinde de valoarea din fiecare linie din
   cererea parinte.
   
   SELECT-ul interior este executat normal o data pentru fiecare linie
   candi- dat.
   
      Considerente de eficienta
      
   
   
    Vom examina cele doua tipuri de subcereri.Trebuie mentionat ca
   subcererea corelata (cu EXISTS) poate fi cel mai performant mod pentru
   unele cereri.
   
   Performanta va depinde de folosirea indexarilor, de numarul liniilor
   in- toarse de cereri, de dimensiunea tabelelor si daca sint necesare
   tabelele temporare pentru a evalua rezultatele temporare.Tabelele
   temporare generate de ORACLE nu sint indexate si acest lucru conduce
   la degradarea performantelor pentru subcererile ce folosesc IN, ANY si
   ALL.
   
   Cele de mai sus sint puncte de vedere generale.Performantele sint
   discutate mai in detaliu in alte cursuri.
   
      NOT EXISTS contra NOT IN
      
   
   
    Desi intr-o subcerere o operatie NOT IN poate fi la fel de eficienta
   ca si NOT EXISTS, NOT EXISTS este mult mai sigur daca subcererea
   intoarce niste valori NULL, fata de de NOT IN pentru care conditia se
   evalueaza la FALSE cind in lista de comparatii sint incluse valori
   NULL.
   
   Considerind urmatoarea cerere care gaseste angajatii ce nu au nici un
   su- bordonat :

     SELECT ENAME, JOB
     FROM EMP
     WHERE EMPNO NOT IN (SELECT MGR
                         FROM EMP);


   
   
   Nici o linie nu va fi intoarsa de cererea de mai sus, deoarece coloana
   MGR contine o valoare NULL.
   
   Cererea corecta este :

     SELECT ENAME, JOB
     FROM EMP E
     WHERE NOT EXISTS (SELECT MGR
                       FROM EMP
                       WHERE MGR = E.EMPNO);

     ENAME       JOB
    -------     -----
     SMITH       CLERK
     ADAMS       CLERK
     ALLEN       SALESMAN
     WARD        SALESMAN
     MARTIN      SALESMAN
     TURNER      SALESMAN
     JAMES       CLERK
     MILLER      CLERK

   
   
  Exercitii - Subcereri
  
   
   
   Aceste exercitii va vor permite sa scrieti cereri complete folosind
   SELECT- uri ascunse si SELECT-uri corelate.
   
    Tema
   
    1. Gasiti angajatii care cistiga cell mai mare salariu pentru fiecare
       tip de functie.Sortati in ordinea descrescatoare a salariului.

     JOB         ENAME          SAL
    -----       -------        ------
    PRESIDENT    KING         5,000.00
    ANALYST      SCOTT        3,000.00
    ANALYST      FORD         3,000.00
    MANAGER      JONES        2,975.00
    SALESMAN     ALLEN        1,600.00
    CLERK        MILLER       1,300.00

    Vor fi 6 inregistarri selectate.
   
    2. Gasiti angajatii care cistiga salariul minim pentru functia
       lor.Afisati rezultatul in ordine crescatoare a salariului.

     ENAME        JOB           SAL
    -------      -----         -----
     SMITH       CLERK         800.00
     WARD        SALESMAN     1,250.00
     MARTIN      SALESMAN     1,250.00
     CLARK       MANAGER      2,450.00
     SCOTT       ANALYST      3,000.00
     FORD        ANALYST      3,000.00
     KING        PRESIDENT    5,000.00

     Vor fi 7 inregistari selectate.
   
    3. Gasiti cei mai recenti angajati din fiecare departament.Ordonati
       dupa data angajarii.


     DEPTNO     ENAME     HIREDATE
    --------   -------   ----------
       20       ADAMS     04-JUN-84
       10       KING      09-JUL-84
       30       JAMES     23-JUL-B4
   
    4. Afisati urmatoarele detalii pentru orice angajat care cistiga un
       salariu mai mare ca media pentru departamentul lor.Sortati dupa
       numarul departa- mentului.

     ENAME      SALARY      DEPTNO
    -------    --------    --------
     KING        5000         10
     JONES       2975         20
     SCOTT       3000         20
     FORD        3000         20
     ALLEN       1600         30
     BLAKE       2850         30

     Vor fi selectate 6 inregistrari.
   
    5. Listati toate departamentele care nu au angajati (folositi o
       subcerere).

     DEPTNO       DNAME
    --------     -------
       40       OPERATIONS
   
    6. Afisati urmatoarele informatii pentru departamentul cu cea mai
       mare nota de plata anuala.

     DEPTNO       COMPENSATION
    --------     ---------------
       20           130500
   
    7. Care sint primii trei angajati in functie de salariul cistigat ?
       Afisati numele lor si salariul.



      ENAME          SAL
     -------        -----
      SCOTT        3,000.00
      KING         5,000.00
      FORD         3,000.00
   
    8. In ce an s-au angajat cei mai multi in companie ? Afisati anul si
       numarul angajatilor.

     YEAR        NUMBER OF EMPS
    ------      ----------------
     1984               8
   
    9. Modificati intrebarea 4 pentru a afisa si salariul mediu pentru
       departa- ment.

     ENAME         SAL       DEPTNO       DEPTAVG
    -------       -----     --------     ----------
     ALLEN         1600        30        1566.66667
     BLAKE         2850        30        1566.66667
     JONES         2975        20              2175
     FORD          3000        20              2175
     SCOTT         3000        20              2175
     KING          5000        10        2916.66667

     Vor fi selectate 6 inregistrari.
   
   10. Scrieti o subcerere care afiseaza o '*' linga linia celui mai
       recent angajat.Afisati ENAME, HIREDATE si coloana (maxdate)
       completata cu '*'.


      ENAME        HIREDATE      MAXDATE
     -------      ----------    ---------
      SMITH        13-JUN-83
      ADAMS        04-JUN-84
      ALLEN        15-AUG-83
      BLAKE        11-JUN-84
      CLARK        14-MAY-84
      FORD         05-DEC-83
      JAMES        23-JUL-84        *
      JONES        31-OCT-83
      KING         09-JUL-84
      MARTIN       05-DEC-83
      MILLER       21-NOV-83
      SCOTT        05-MAR-84
      TURNER       04-JUN-84
      WARD         26-MAR-84

      Vor fi selectate 14 inregistrari.

   
   
    Subcereri
    
   
    1.

     SELECT JOB, ENAME, SAL
     FROM EMP
     WHERE (SAL, JOB) IN (SELECT MAX(SAL), JOB
                          FROM EMP
                          GROUP BY JOB)
     ORDER BY SAL DESC;
   
    2.

     SELECT ENAME, JOB, SAL
     FROM EMP
     WHERE (SAL, JOB) IN (SELECT MIN(SAL), JOB
                          FROM EMP
                          GROUP BY JOB)
     ORDER BY SAL;
   
    3.

     SELECT DEPTNO, ENAME, HIREDATE
     FROM EMP
     WHERE (HIREDATE, DEPTNO) IN (SELECT MAX(HIREDATE), DEPTNO
                                  FROM EMP
                                  GROUP BY DEPTNO)
     ORDER BY HIREDATE;
   
       
    4.

     SELECT ENAME, SAL SALARY, DEPTNO
     FROM EMP E
     WHERE SAL > (SELECT AVG(SAL)
                  FROM EMP
                  WHERE DEPTNO = E.DEPTNO)
     ORDER BY DEPTNO;
   
    5.

     SELECT DEPTNO, DNAME
     FROM DEPT D
     WHERE NOT EXISTS (SELECT 'anything'
                       FROM EMP
                       WHERE DEPTNO = D.DEPTNO);
   
    6.

     DEFINE REM = SAL * 12 + NVL(COMM, 0)
     SELECT DEPTNO, SUM(&REM) COMPENSATION
     FROM EMP
     GROUP BY DEPTNO
     HAVING SUM(&REM) = (SELECT MAX(SUM(&REM))
                         FROM EMP
                         GROUP BY DEPTNO);
   
    7.

     SELECT ENAME, SAL
     FROM EMP E
     WHERE 3 > (SELECT COUNT(*)
                FROM EMP
                WHERE E.SAL


     SELECT TO_CHAR(HIREDATE, 'YYYY') YEAR,
            COUNT(EMPNO) NUMBER_OF_EMPS
     FROM EMP
     GROUP BY TO_CHAR(HIREDATE, 'YYYY')
     HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
                            FROM EMP
                            GROUP BY TO_CHAR(HIREDATE, 'YYYY'));



     SELECT E.ENAME ENAME, E.SAL, E.DEPTNO, AVG(A.SAL) DEPT_AVG
     FROM EMP A, EMP E
     WHERE E.DEPTNO = A.DEPTNO
     AND E.SAL > (SELECT AVG(SAL)
                  FROM EMP
                  WHERE DEPTNO = E.DEPTNO)
     GROUP BY E.ENAME, E.SAL, E.DEPTNO
     ORDER BY AVG(A.SAL);



      SELECT ENAME, HIREDATE, '*' MAXDATE
      FROM EMP
      WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP)
      UNION
      SELECT ENAME, HIREDATE, ' '
      FROM EMP
      WHERE HIREDATE  (SELECT MAX(HIREDATE) FROM EMP);

Inapoi la cuprins.

BogSoft 1999