6. Subcereri



Exista situatii cand pentru realizarea unui obiectiv este implicat un lant de doua cereri secventiale in care se foloseste rezultatul unei cereri ca sursa pentru gasirea valorii cautate in cererea principala. Prima cerere este de fapt o subcerere ce ofera valori (initial necunoscute) pe care se bazeaza cererea principala.

Exemplu

Ce angajati au un salariu mai mare ca cel al lui Miller?

Pentru rezolvarea acestei probleme sunt necesare doua cereri Rezolvarea problemei se face prin combinarea celor doua cereri, prin plasarea unei cereri in interiorul celeilalte. Cererea interioara sau subcererea se executa prima si returneaza o valoare care este folosita de catre cererea principala, exterioara.

Sintaxa subcererii

O subcerere este o declaratie SELECT care este cuprinsa in interiorul unei altei declaratii SELECT. Subcererea mai este denumita subSELECT sau SELECT interior. Prin folosirea de subcereri se pot obtine comenzi mai puternice pe baza unora mai simple. Subcererile sunt utile cand se doreste selectarea unor linii dintr-o tabela printr-o conditie ce depinde de datele din acea tabela.
Subcererea se poate plasa sub conditii SQL de tipul:

Astfel intr-o sintaxa de tipul

SELECT coloana1[,coloana2,...]
FROM tabela
WHERE expr operator
               (SELECT coloana1[,coloana2,...];
               FROM tabela);

subcererea este cea cuprinsa intre paranteze (interioara) si se executa inaintea cererii principale (exterioare).
Cuvantul "operator" - include conditii de comparare ce pot fi impartite in doua clase: folositi in functie de continutul in linii returnat de subcerere.

Folosirea unei subcereri

In cadrul exemplului anterior (Ce angajati au un salariu mai mare ca cel al lui Miller?) unde avem de-a face cu o cerere principala simpla si o subcerere simpla, sintaxa este:


Cererea interioara determina salariul lui Miller iar cea exterioara, principala preluand rezultatul anterior conduce la afisarea numelor celor care au salariul mai mare ca cel al lui Miller.

Concluzii

Clasificare subcereri

In functie de continutul returnat, subcererile se pot clasifica in

Deasemenea exista si subcereri multicoloana (ce returneaza mai multe coloane).



Subcereri monolinie

Exemplu

Afisati numele si salariile angajatilor care au aceeasi functie ca si Allen.


Exemplu

Afisati numele, functia si salariul angajatilor care au aceeasi functie ca Adams si salariu mai mare ca acesta.


Mai intai se executa subcererile, prima returnand functie='CLERK' iar cea de-a doua sal=1487. Cererea principala folosind aceste valori returnate ofera rezultatul final.

Folosirea functiilor de grup in subcereri

Se pot utiliza functii de grup in subcereri pentru obtinerea valorii ce va constitui element de comparatie in cererea principala.

Exemplu

Afisati numele, numarul departamentului si salariul angajatilor care au salariul mai mare decat media salariilor de la departamentul 20.


Functia de grup AVG() ofera o medie adica o singura valoare, astfel incat intreaga subcerere returneaza o singura valoare.

Clauza HAVING in subcereri

In scrierea subcererilor pe langa clauza WHERE se poate utiliza si clauza HAVING (dupa ce s-a facut o grupare cu GROUP BY).

Exemplu

Afisati departamentele la care salariul maxim este mai mare decat salariul maxim de la departamentul 20.


Greseli uzuale

Greselile uzuale rezulta din utilizarea necorespunzatoare a operatorilor. De exemplu in cererea principala o instructiune WHERE cu operator monolinie de comparatie ">" iar subcererea intoarce un raspuns de mai multe linii.

Exemplu


Corectarea se face prin punerea in acord a operatorilor cu subcererea adica prin inlocuirea operatorului monolinie ">" din cererea principala cu operatorul multilinie "IN".

Exemplu

Afisati numele si salariul angajatilor care au salariul egal cu unul dintre salariile minime pe departamente.



O alta problema este reprezentata de cazul in care subcererea nu ofera raspuns.

Exemplu

Afisati numele colegilor de departament ai lui Taylor.


Subcererea nu ofera niciun raspuns deoarece Taylor nu se afla in baza de date utilizata, iar cererea principala nu are ce utiliza in clauza WHERE. Astfel aceasta cerere nu ofera raspuns.

Subcereri multilinie

Subcererile multilinie returneaza mai mult decat o linie. Cu astfel de subcereri trebuie folositi operatori multilinie care pot prelucra una sau mai multe valori.
Operatorii utilizati sunt:

Exemplu IN

Aflati angajatii care au salariul egal cu salariul cel mai mare din fiecare departament.


Subcererea ofera salariile maxime din fiecare facultate si prin cererea principala se afla angajatii cu aceste salarii.

Exemplu ANY

Aflati angajatii care au salariul mai mare decat vreun angajat al departamentului 20 si nu fac parte din acest departament.


Operatorul ANY (sinonim operatorului SOME) compara o valoare cu fiecare valoare din cele returnate de subcerere.
Astfel,
< ANY inseamna mai mic decat maximul,
> ANY inseamna mai mare decat minimul si
= ANY este echivalent cu IN.

Exemplu ALL

Gasiti angajatii care au salariul mai mare decat oricare (toti) angajatii de la departamentul 20.


Operatorul ALL din cererea principala compara o valoare cu oricare valoare returnata de subcerere. In cazul dat > ALL identifica pe cei care au salarii mai mari decat salariul maxim din facultatea 20.
Astfel,
> ALL inseamna mai mare decat maximul si
< ALL inseamna mai mic decat minimul.

Imbricarea subcererilor

Subcererile pot fi folosite si in interiorul altor subcereri.

Exemplu

Gasiti numele, functia, data angajarii si salariul angajatilor al caror salariu este superior celui mai mare salariu al vreunei persoane angajate dupa data de 05-JUN-1982.


Numarul maxim de imbricari pentru o subcerere este de 255.

Subcereri corelate

O subcerere corelata este o subcerere care se executa o data pentru fiecare linie considerata de cererea principala si care la executie foloseste o valoare dintr-o coloana din cererea exterioara. Ea se poate identifica prin folosirea unei coloane a cererii exterioare in clauza operatorului cererii interioare.

Exemplu

Gasiti angajatii care au un salariu superior salariului mediu al departamentului lor.


Valori de NULL intr-o subcerere

In cazul in care subcererea returneaza vreuna din valori NULL si cererea principala are precum operator NOT IN atunci cererea principala nu va returna niciun rand. Motivul este ca o comparatie cu NULL conduce la un rezultat NULL.

Exemplu

Gasiti angajatii care nu au subordonati.


Astfel ori de cate ori valoarea NULL face parte din raspunsurile subcererii nu trebuie folosit operatorul NOT IN. De fapt operatorul NOT IN este echivalent cu <> ALL.

Returnarea de valori NULL de catre subcerere nu prezinta nici o problema in cazul operatorului IN in cererea principala (in echivalent cu = ALL).

Exemplu

Gasiti angajatii care au subordonati.




In cazul utilizarii operatorului NOT IN in cererea principala trebuie avut grija sa se excluda valorile NULL din raspunsurile subcererii.

Exemplu

Gasiti angajatii care nu au subordonati.


Se mai poate folosi operatorul NOT EXISTS care evalueaza valorile de NULL cu FALSE.

Exercitii

Subiectul consta in a rezolva probleme prin crearea de subcereri pentru a obtine valori (initial necunoscute) pentru comparatii si/sau pentru a gasi valori care exista intr-un set si nu exista intr-un alt set. Se recomanda a verifica daca subcererea ofera raspunsurile anticipate inainte de a scrie cererea principala.

1. Gasiti angajatii care castiga cel mai mare salariu pentru fiecare functie. Sortati in ordinea descrescatoare a salariilor.

JOB ENAME SAL
MANAGER BLAKE 8132
PRESIDENT KING 7638
CLERK SMITH 3631
ANALYST SCOTT 2605
ANALYST FORD 2605
SALESMAN TURNER 2145



2. Gasiti ultimii angajati din fiecare departament. Ordonati dupa data angajarii.

ENAME DEPTNO HIREDATE
FORD 20 24-SEP-81
MILLER 10 01-OCT-83
TURNER 30 17-DEC-84



3. Gasiti primii trei angajati in functie de salariul primit. Listati numele si salariul.

ENAME SAL
JONES 4892
BLAKE 8132
KING 7638


4. Gasiti departamentul cu cea mai mare nota de plata anuala.

DEPTNO PLATI
10 230640



5. Gasiti in ce an s-au angajat cei mai multi oameni. Afisati anul si numarul angajatilor.

AN NUMAR_DE_ANGAJATI
1980 5



6. Afisati numele si departamentul angajatilor care ii sunt subordonati lui Blake.

ENAME DEPTNO
ALLEN 30
WARD 30
MARTIN 30
TURNER 30
JAMES 30



7. Afisati numele si functia pentru fiecare angajat al departamentului Research.

ENAME JOB
SMITH CLERK
JONES MANAGER
ADAMS CLERK
SCOTT ANALYST
FORD ANALYST



Rezultatele exercitiilor





Capitolul precedent Acasa Capitolul urmator