Platform de e-learning i curricul e-content

pentru nvmntul superior tehnic

Baze de date 1

7. Subcereri n SQL (1)

SUBCERERIO subcerere este o cerere SELECT inclus ntr-o alt cerere SQL.

Astfel de construcii se folosesc n cazul n care rezultatul dorit nu se poate obine cu o singur parcurgere a datelor.

Exemplu: pentru a afla cine este studentul cu

2

Exemplu: pentru a afla cine este studentul cu cel mai mare punctaj sunt necesare dou parcurgeri ale tabelei STUD: prima calculeaz punctajul maxim iar ulterior

a doua afieaz datele dorite despre studentul sau studenii cu acel punctaj.

STUDMATR NUME AN GRUPA DATAN LOC TUTOR PUNCTAJ CODS ---- ------- -- ------ --------- ---------- ----- ------- ----

1456 GEORGE 4 1141A 12-MAR-82 BUCURESTI 2890 11 1325 VASILE 2 1122A 05-OCT-84 PITESTI 1456 390 11 1645 MARIA 3 1131B 17-JUN-83 PLOIESTI 1400 11 3145 ION 1 2112B 24-JAN-85 PLOIESTI 3251 1670 21 2146 STANCA 4 2141A 15-MAY-82 BUCURESTI 620 21 3251 ALEX 5 2153B 07-NOV-81 BRASOV 1570 21 2215 ELENA 2 2122A 29-AUG-84 BUCURESTI 2146 890 21

3

2215 ELENA 2 2122A 29-AUG-84 BUCURESTI 2146 890 21 4311 ADRIAN 3 2431A 31-JUL-83 BUCURESTI 450 24 3514 FLOREA 5 2452B 03-FEB-81 BRASOV 3230 24 1925 OANA 2 2421A 20-DEC-84 BUCURESTI 4311 760 24 2101 MARIUS 1 2412B 02-SEP-85 PITESTI 3514 310 24 4705 VOICU 2 2421B 19-APR-84 BRASOV 4311 1290 24

SPEC si BURSACODS NUME DOMENIU ----- ---------- ---------------

11 MATEMATICA STIINTE EXACTE 21 GEOGRAFIE UMANIST 24 ISTORIE UMANIST

4

TIP PMIN PMAX SUMA -------------------- ----- ----- -----

FARA BURSA 0 399 BURSA SOCIALA 400 899 100 BURSA DE STUDIU 900 1799 150 BURSA DE MERIT 1800 2499 200 BURSA DE EXCEPTIE 2500 9999 300

PARCURGERICum o cerere SELECT specific o singur parcurgere a datelor rezult c pentru rezolvarea problemei sunt necesare dou astfel de cereri, prima

5

necesare dou astfel de cereri, prima (subcererea) furniznd datele necesare pentru a doua (cererea principal).

SUBCERERILE APAR:n expresiile logice din clauzele WHERE i HAVING

n clauza ORDER BY a unei cereri SELECT; valoarea returnat de subcerere pentru fiecare linie a rezultatului va determina

6

fiecare linie a rezultatului va determina ordinea de afiare a acestora.

n clauza SELECT; valoarea returnat de subcerere va fi prezent n rezultatul final.

n clauza FROM; n acest caz ele sunt asimilate unor tabele temporare din care se calculeaz rezultatul cererii care le include.

SUBCERERI IN EXPRESII LOGICE Rezultatul unui SELECT, dac este nevid,

este ntotdeauna o tabel. Din punct de vedere al modului de folosire al unei subcereri exist ns diferene n funcie de forma rezultatului acesteia:

7

1. Subcereri care ntorc o singur valoare 2. Subcereri care ntorc o coloan 3. Subcereri care ntorc o tabel. n continuare este prezentat modul de

utilizare pentru fiecare tip n parte

O VALOARE n acest caz valoarea ntoars de subcerere poate fi

folosit ca oricare alta n comparaii care includ operatorii obinuii: =, = i .

Exemplu: studentul cu cel mai mare punctaj: subcererea ntoarce valoarea maxim a punctajului din tabela STUD iar cererea care o include numele

8

din tabela STUD iar cererea care o include numele studentului sau studenilor care au acel punctaj i valoarea acestuia:

SELECT NUME, PUNCTAJFROM STUDWHERE PUNCTAJ =

(SELECT MAX(PUNCTAJ) FROM STUD)

REZULTATRezultatul obinut este:

NUME PUNCTAJ---------- -------

9

---------- -------

FLOREA 3230

REGULI PENTRU SUBCERERI (1)Subcererea trebuie s fie ntotdeauna n partea dreapt a comparaiei, ca n exemplul de mai sus.

Subcererea se pune obligatoriu ntre paranteze.

10

paranteze.Deoarece rezultatul este folosit pentru calculele cererii principale, ordinea valorilor returnate nu este important. De aceea subcererile nu pot conine clauza ORDER BY.

REGULI PENTRU SUBCERERI (2)Dac subcererea ntoarce mai multe linii se semnaleaz eroarea ORA-01427: single-row subquery returns more than one row.

Dac subcererea nu ntoarce nici o linie, comparaia n care e implicat se evalueaz la

11

comparaia n care e implicat se evalueaz la FALS.

O cerere poate conine una sau mai multe subcereri, acestea putnd fi pe acelai nivel sau incluse una n alta.

ALTI OPERATORIn afar de operatorii uzuali de comparaie, n cazul acestui tip de subcereri se pot folosi i operatorii BETWEEN, LIKE i IS NULL.

Exemplele urmtoare reprezint cereri

12

Exemplele urmtoare reprezint cereri valide coninnd i BETWEEN sau LIKE. Folosirea lui IS NULL pentru o subcerere este relevant doar n cazul subcererilor corelate prezentate ntr-un alt subcapitol.

SUBCERERI IN BETWEENAfiarea numelui i punctajului pentru studenii avnd un punctaj egal cu cel mediu +/- 30%. Se folosete operatorul BETWEEN avnd ca parametri dou subcereri:

13

SELECT NUME, PUNCTAJFROM STUDWHERE PUNCTAJ BETWEEN

(SELECT AVG(PUNCTAJ) FROM STUD)*0.7 AND(SELECT AVG(PUNCTAJ) FROM STUD)*1.3;

SUBCERERI IN LIKEAfiarea acelorai date ca mai sus pentru studenii avnd un nume care nu ncepe cu aceeai liter cu a studentului cu punctaj maxim:

SELECT NUME, PUNCTAJ

14

SELECT NUME, PUNCTAJ FROM STUDWHERE NUME NOT LIKE SUBSTR((SELECT NUME FROM STUDWHERE PUNCTAJ = (SELECT MAX(PUNCTAJ)

FROM STUD)) , 1, 1) || '%';

SUBCERERI IN LIKE (2)Exist dou niveluri de imbricare:Subcererea de nivel 2 ntoarce valoarea maxim a punctajului.

Subcererea de nivel 1 ntoarce numele studentului cu acel punctaj.

15

studentului cu acel punctaj.n cererea principal este decupat prima liter a acestui nume, folosind funcia SUBSTR(rezultat, 1, 1) i este concatenat cu caracterul % pentru a forma un ablon folosit apoi de condiia NOT LIKE.

ERORI (1)Subcererea ntoarce mai multe valori. n acest caz nu vom obine un rezultat ci mesajul de eroare menionat anterior:

SELECT NUME, PUNCTAJ

16

FROM STUDWHERE PUNCTAJ = (SELECT PUNCTAJ FROM STUD);

ERORI (2)Subcererea nu ntoarce nici o valoare. n acest caz vom obine un rezultat vid (fr nici o linie), condiia evalundu-se la FALS:

17

la FALS:SELECT NUME, PUNCTAJFROM STUDWHERE PUNCTAJ =

(SELECT MAX(PUNCTAJ) FROM STUD WHERE CODS = 100)

O COLOANAn acest caz valorile coloanei ntoarse de subcerere sunt asimilate unei mulimi.

Condiia trebuie s foloseasc

18

Condiia trebuie s foloseasc operatorul IN sau negatul acestuia NOT IN i nu operatori de comparaie.

EXEMPLUCererea din exemplul urmtor afieaz lista tuturor studenilor de la specializarea cu codul 21 care sunt tutori ai altor studeni. Pentru a fi tutor, matricola studentului trebuie s aparin mulimii valorilor aflate pe coloana TUTOR din tabela STUD calculat cu

19

coloana TUTOR din tabela STUD calculat cu ajutorul subcererii:

SELECT NUME, CODS FROM STUDWHERE MATR IN (SELECT TUTOR FROM STUD)AND CODS = 21;

REZULTATRezultatul va conine datele pentru doi studeni:NUME CODS---------- -----

STANCA 21

20

STANCA 21ALEX 21

Observaie: NOT IN returneaz ntotdeauna valoarea fals n cazul n care mulimea conine valori nule.

NOT INDin aceast cauz pentru a obine lista studenilor de la aceast specializare care nu sunt tutori nu se poate folosi cererea:

SELECT NUME, CODS

21

SELECT NUME, CODS FROM STUDWHERE MATR NOT IN (SELECT TUTOR FROM STUD) AND CODS = 21;

deoarece subcererea ntoarce o coloan care conine i valori nule.

NOT IN cont.n astfel de cazuri este necesar eliminarea acestor valori din rezultat prin adugarea unei condiii suplimentare de tip IS NOT NULL:

SELECT NUME, CODSFROM STUD

22

FROM STUDWHERE MATR NOT IN (SELECT TUTOR FROM STUDWHERE TUTOR IS NOT NULL)AND CODS = 21;

SUBCERERI CU GROUP BY n exemplul urmtor subcererea folosete o clauz

GROUP BY pentru a genera punctajele maxime pentru fiecare specializare. Cererea principal afieaz studenii care au un punctaj egal cu vreuna dintre valorile returnate:

SELECT NUME, PUNCTAJ, CODS

23

SELECT NUME, PUNCTAJ, CODSFROM STUDWHERE PUNCTAJ IN

(SELECT MAX(PUNCTAJ) FROM STUDGROUP BY CODS);

SUBCERERI CU GROUP BY (2)SELECT NUME, PUNCTAJ, CODSFROM STUDWHERE PUNCTAJ IN (SELECT MAX(PUNCTAJ) FROM STUDGROUP BY CODS);

24

GROUP BY CODS);

ntmpltor, rezultatul conine chiar studenii cu cel mai mare punctaj pentru fiecare specializare.

n cazul general ns rezultatul poate conine i studeni care nu au punctajul maxim la specializarea lor dar egal cu maximul unei alte specializri.

SOME/ANY SI ALLEste posibil folosirea operatorilor de comparaie uzuali (, =, etc.) n conjuncie cu o cerere care ntoarce o coloan dac aceasta este prefixat cu unul din operatorii SOME, ANY i ALL.

Semnificaia lor este urmtoarea:

25

Semnificaia lor este urmtoarea: SOME i ANY: condiia este adevrat dac mcar o valoare dintre cele returnate de subcerere verific comparaia respectiv.

ALL: condiia este adevrat dac toate valorile returnate de subcerere verific comparaia respectiv.

EXEMPLE (1)Lista studenilor care au un punctaj mai mare dect al vreunui student de la specializarea cu cod 11:

SELECT NUME, PUNCTAJFROM STUD

26

WHERE PUNCTAJ > SOME(SELECT PUNCTAJ FROM STUD

WHERE CODS = 11);nlocuirea lui SOME cu ANY duce la obinerea aceluiai rezultat, cei doi operatori efectund aceeai operaie

EXEMPLE (2)Lista studenilor care au un punctaj mai mare dect al tuturor studenilor de la specializarea 11:

SELECT NUME, PUNCTAJ

27

FROM STUDWHERE PUNCTAJ >

ALL(SELECT PUNCTAJ FROM STUDWHERE CODS = 11);

O TABELAn cazul n care subcererea ntoarce un rezultat care are mai multe coloane acesta este asimilat cu o mulime de linii i se poate folosi operatorul IN n

28

linii i se poate folosi operatorul IN n urmtorul mod:

WHERE (lista_de_expresii) IN (subcerere)

REGULI1. Lista de expresii trebuie ncadrat de

paranteze rotunde.2. Numrul de coloane din rezultatul

subcererii trebuie s fie egal cu

29

subcererii trebuie s fie egal cu numrul de expresii din list.

3. Corespondena ntre valorile expresiilor din list i coloanele rezultatului este poziional.

REGULI - cont4. Tipurile elementelor corespondente trebuie

s fie aceleai sau convertibile automat unul la cellalt (sistemul Oracle face conversia automat ntre tipurile ir de caractere i numere/date calendaristice).

30

numere/date calendaristice).5. Condiia este adevrat dac rezultatul

subcererii conine mcar o linie format din valorile expresiilor din list.

6. Dac rezultatul subcererii este vid ntreaga condiie este evaluat la fals.

EXEMPLUPentru a afla care sunt studenii cu cel mai mare

punctaj de la fiecare specializare, cererea este urmtoarea:

SELECT NUME, PUNCTAJ, CODSFROM STUDWHERE (CODS, PUNCTAJ) IN

31

WHERE (CODS, PUNCTAJ) IN (SELECT CODS, MAX(PUNCTAJ) FROM STUDGROUP BY CODS);

Codiia va fi adevrat dac punctajul studentului este egal cu un punctaj maxim ntors de subcerere i n acelai timp codul specializrii este al celei pentru care s-a calculat maximul respectiv.

OBSERVATIEObservaie: Din cauza faptului c dou valori nule nu sunt egale ntre ele un cuplu de tipul (NULL, valoare) nu va fi considerat egal cu el nsui.

Din acest motiv, cererea urmtoare nu va

32

Din acest motiv, cererea urmtoare nu va returna date despre studenii care nu au un tutor asociat (au o valoare nul pe aceast coloan) dei n aparen condiia ar trebui s fie adevrat pentru orice student al specializrii avnd codul 11:

EXEMPLU

SELECT MATR, NUME, CODS, TUTORFROM STUDWHERE (MATR, NUME, CODS, TUTOR) IN

33

WHERE (MATR, NUME, CODS, TUTOR) IN(SELECT MATR, NUME, CODS, TUTORFROM STUDWHERE CODS = 11);

REZULTATEMATR NUME CODS TUTOR----- ---------- ----- -----

1325 VASILE 11 1456

dei rezultatul subcererii (executat separat)

34

dei rezultatul subcererii (executat separat) este urmtorul:

MATR NUME CODS TUTOR----- ---------- ----- -----

1456 GEORGE 111325 VASILE 11 14561645 MARIA 11

Bibliografie

1. Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom: Database Systems: The Complete Book, Prentice-Hall, Englewood Cliffs, NJ, 2002.

2. F. Rdulescu : Oracle SQL, PL/SQL, Editura Printech, ISBN 973-718-203-02005

F. Radulescu. Curs: Baze de date 35

718-203-02005