Subconsultari

Subconsultari

Una dintre cele mai importante facilitati oferite de SQL consta in includerea unei consultari in alta, pe doua sau mai multe niveluri - cu alte cuvinte, utilizarea subconsultarilor. Prin aceste subconsultari se obtin tabele temporare intermediare ce vor fi folosite drept "argumente" in frazele SELECT superioare.

In materie de subconsultari, cel mai utilizat operator este IN, pe care deja l-am intalnit in capitolul precedent, dar intr-o cu totul alta ipostaza - testarea incadrarii valorii unui atribut intr-o lista de constante.

In continuare, domeniul de testare este alcatuit din liniile unei tabele.

Revenim la exemplul din algebra relationala: Ce studenti studiaza la aceeasi sectie cu "Dinescu Monica"?

SELECT nume

FROM STUDENT

WHERE sectia IN

(SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica")

In cazul acestei interogari, executia se realizeaza in doi timpi. Mai intai se executa subconsultarea

SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica"

obtinandu-se o tabela intermediara ce contine o singura linie (pe care apare CIG) si o singura coloana (sectia), ca in figura 4.24.

Figura 4.24. Sectia la care studiaza Dinescu Monica

In cel de-al doilea pas sunt selectate liniile tabelei STUDENT care indeplinesc conditia sectia = "CIG"

Figura 4.25. Studentii care studiaza la aceeasi sectie cu Dinescu Monica

Observam ca in rezultat a fost inclus si studentul de referinta - Dinescu Monica. Daca se doreste excluderea din rezultat a acestui student, fraza SELECT se modifica astfel:

SELECT nume

FROM STUDENT

WHERE sectia IN

(SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica")

AND nume < > "Dinescu Monica"

Ce studenti studiaza la alte sectii decat "Dinescu Monica"?

SELECT nume

FROM STUDENT

WHERE sectia NOT IN

(SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica")

Figura 4.26. Studentii care studiaza la alte sectii

decat la cea la ca studiaza Dinescu Monica

In ce camin sunt cazati studentii care studiaza la aceeasi sectie cu "Dinescu Monica"?

SELECT DISTINCT nume_camin

FROM STUDCAMIN

WHERE nr_matricol IN

(SELECT nr_matricol

FROM STUDENT

WHERE sectia IN

SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica"))

Figura 4.27. Caminul in care stau studentii care

studiaza la aceeasi sectie cu Dinescu Monica

Rezultatul prezentat in figura 4.27. este obtinut prin folosirea a trei niveluri de interogare (fraza principala, o subconsultare si o sub-subconsultare).

In Visual Fox Pro aceasta ultima interogare face simtita prezenta uneia dintre cele mai serioase limitari SQL - maxim 2 niveluri de consultare (fraza principala si o interogare subordonata). Mesajul de eroare are numarul 1842: SQL: Subquery nesting in too deep.

Astfel, pentru a raspunde la intrebare, solutia de interogare trebuie reformulata:

SELECT nume_camin

FROM STUDENT INNER JOIN STUDCAMIN;

ON STUDENT.nr_matricol = STUDCAMIN.nr_matricol;

WHERE sectia IN

(SELECT sectia

FROM STUDENT

WHERE nume = "Dinescu Monica")

Din ce judete sunt studentii care au imprumutat carti scrise de autori de nationalitate romana?

Am ales acest exemplu pentru a folosi in subconsultari cat mai multe tabele ale bazei de date

SELECT judet

FROM LOCALITATE

WHERE cod_localitate IN

(SELECT cod_localitate

FROM STUDENT

WHERE nr_matricol IN

(SELECT nr_matricol

FROM STUDCARTE

WHERE cota_carte IN

(SELECT cota_carte

FROM CARTE

WHERE cota_carte IN

(SELECT cota_carte

FROM AUTOR_CARTE

WHERE nume_autor IN

SELECT nume_autor

FROM AUTOR

WHERE nationalitate = "roman")))))

Atunci cand rezultatul unei subconsultari se concretizeaza intr-o tabela cu o singura coloana si o singura linie, corelarea poate fi facuta si cu operatorii de comparatie obisnuiti: =, >, >=, <, <=.

In continuare vom ilustra aceasta facilitate prin cateva exemple.

Care este cel mai mic pret unitar la care s-a cumparat o carte?

SELECT MIN (pret_unitar) AS maxim

FROM CARTE

Figura 4.28. Cel mai mic pret unitar

Care este cel mai mic pret unitar si care este cartea, respectiv studentul care a imprumutat cartea cu pretul respectiv?

SELECT titlu, pret_unitar, nume

FROM CARTE C, STUDCARTE SC, STUDENT S

WHERE S.nr_matricol = SC.nr_matricol

AND SC.cota_carte=C.cota_carte AND

pret_unitar =(SELECT MIN (pret_unitar)

FROM CARTE)

Figura 4.29. Titlu cartii cu cel mai mic pret

Care sunt cele mai mari cinci preturi unitare, cartile si studentii care au imprumutat cartile care au cele cinci preturi maxime?

SELECT titlu, pret_unitar, nume

FROM STUDENT INNER JOIN STUDCARTE ON STUDENT.nr_matricol = STUDCARTE.nr_matricol

INNER JOIN CARTE ON STUDCARTE.cota_carte = CARTE.cota_carte

WHERE pret_unitar >

(SELECT MAX (pret_unitar)

FROM CARTE

WHERE pret_unitar <

(SELECT MAX (pret_unitar)

FROM CARTE

WHERE pret_unitar <

(SELECT MAX (pret_unitar)

FROM CARTE

WHERE pret_unitar <

(SELECT MAX (pret_unitar)

FROM CARTE

WHERE pret_unitar <

(SELECT MAX (pret_unitar)

FROM CARTE)

)

)

)

)

ORDER BY pret_unitar DESC

Avand in vedere nivelurile de interogare, pentru exemplul de mai sus, in Visual FoxPro nu se poate formula interogarea in aceasta forma.  Avem insa la indemana o solutie neverosimil de simpla bazata pe clauza TOP

SELECT TOP 5 titlu, pret_unitar, nume

FROM STUDENT INNER JOIN STUDCARTE ON STUDENT.nr_matricol = STUDCARTE.nr_matricol

INNER JOIN CARTE ON STUDCARTE.cota_carte = CARTE.cota_carte

ORDER BY pret_unitar DESC

Figura 4.30. Primele 5 preturi unitare

Liniile tabelei sunt ordonate descrescator dupa pretul unitar, iar in rezultatul final sunt extrase doar primele cinci (prin TOP 5)