SUBINTEROGARI

Obiectivele acestui capitol sunt, sa invatam cum:

sa construim o subinterogare;

sa folosim o subinterogare intr-o expresie dintr-o instructiune SQL;

sa construim si sa utilizam subinterogari corelate;

sa construim o subinterogare folosind urmatoarele cuvinte cheie intr-o clauza WHERE a unei instructiuni SELECT: ALL, ANY, IN, EXISTS.

O instructiune SELECT imbricata in clauza WHERE a unei instructiuni SELECT INSERT, DELETE sau UPDATE ) se numeste subinterogare (subquery). Fiecare interogare contine in mod obligatoriu o clauza SELECT si o clauza FROM. Fiecare subinterogare trebuie sa fie inchisa intre paranteze pentru ca serverul BD sa o execute mai intai. Asta inseamna ca subinterogarea este o interogare ale carei rezultate sunt transmise altei interogari. Subinterogarea este o modalitate de a face legaturi intre doua sau mai multe interogari. Subinterogarile pot fi corelate sau necorelate. O subinterogare (sau o instructiune SELECT interior ) este corelata cand valoarea produsa de ea depinde de o valoare produsa de o instructiune SELECT exterioara care o contine. Orice alt tip de subinterogare este necorelata.

O caracteristica importanta a unei subinterogari corelate este ca, deoarece ea depinde de o valoare a unui SELECT exterior, ea trebuie sa fie executata in mod repetat, o data pentru fiecare valoare produsa de SELECT-ul exterior. O subinterogare necorelata este executata numai o data.    Putem construi o instructiune SELECT cu o subinterogare care sa inlocuie doua instructiuni SELECT separate. Folosind subinterogarea putem gasi informatii din unul sau mai multe tabele fara a face unirea lor.

Subinterogararile din instructiunile SELECT ne permit sa realizam actiunile urmatoare:

sa comparam o expresie cu rezultatul unei alte instructiuni SELECT;

sa determinam cand rezultatul unei alte instructiuni SELECT include o expresie;

sa determinam cand o alta instructiune SELECT selecteaza anumite linii.

Clauza optionala WHERE dintr-o subinterogare este adesea utilizata ca sa

limiteze conditia de cautare. O subinterogare poate returna nimic, o singura

valoare sau o multime de valori;

daca subintrebarea nu returneaza nici o valoare, ea este echivalenta cu valoarea null atunci intrebarea nu returneaza nici o linie;

daca subinterogarea returneaza o singura valoare care este data fie de o expresie agregat fie exact de o linie compusa dintr-o coloana atunci, ea este echivalenta cu un singur numar sau o valoare de caracter;

daca o subinterbare returneaza o lista sau o multime de valori, valorile

reprezinta fie o linie, fie o coloana;

Constructia unei subinterogari

Fie tabelele r si s care contin coloanele A respectiv B care au acelasi domeniu.

Structura formala a unei subinterogari este:

SELECT

FROM r

WHERE r.A=(SELECT B

FROM s

WHERE B='valoare')

Se executa mai intai subinterogarea dintre paranteze si se selecteaza toate liniile din tabelul s care sunt egale cu A pentru liniile din r.

Exemplul 1

Subinterogarea determina toti clientii care sunt din acelasi oras cu Albu:

SELECT *

FROM clienti

WHERE oras=

(SELECT oras

FROM clienti

WHERE NUME='Albu')

Exemplul 2

Determinati toti clientii din orasul celui cu comanda 1015:

SELECT *

FROM clienti

WHERE oras=

(SELECT oras

FROM companie

WHERE NR_COM

Exemplul 3

Urmatoarea subinterogare furnizeaza fabricile care au termenul de livrare mai mic decat termenul de livrearea a lui DAEWO.

SELECT *

FROM fabrici

WHERE termen_liv <(SELECT termen_liv

FROM fabrici

WHERE ID_FABRICA="DAEWO")

Exemplul 4

Utlizarea cuvantului cheie ALL inaintea unei subinterogare determina cand comparatia este adevarata pentru orice valoare returnata. Determinati comenzile si articolele si fabricile care contin articole ale caror preturi sunt mai mici decat pretul oricarui articol din comanda 1023.

SELECT NR_COM, CD_ART, ID_FABR, PRET

FROM articole

WHERE PRET<ALL (SELECT PRET FROM aticole

WHERE NR_COM=1023)

Exemplu 5

Utilizarea cuvantului cheie inaintea unei subinterogari determina cand comparatia este adevarata pentru cel putin o valoare returnata.

SELECT DISTINCT NR_COMANDA

FROM articole

WHERE PRET_TOTAL>ANY(SELECT PRET_TOTAL)

FROM articole

WHERE NR_COMANDA=1005

Exemplu 6

Fie tabelele:

articole (ID_ART, DENUMIRE, ID_FABR, PRET_UNIT)

fabrici (ID_FABR, NUME_FABR, ORAS, STRADA, NR)

Determinati articolele care sunt facute de o fabrica din Bucuresti.

SELECT ID_ART, DENUMIRE

FROM articole

WHERE ID_FABR IN (SELECT ID_FABR)

FROM fabrici

WHERE ORAS = 'Bucuresti

Exemplu 7

Fie tabelele:

clienti(ID_CL, NUME_CL, PREN_CL, ORAS, STR, NR)

comenzi(NR_COMANDA, DATA_COM, ID_CL, DATA, LIVRARI)

Determinati clientii care nu au nici o comanda:

SELECT NUME_CL, PREN_CL

FROM clienti

WHERE ID_CL NOT IN (SELECT ID_CL

FROM comenzi)

Exemplu 8

Fie tabelele din exemplu 6. Determinati articolele care au pretul de 10 ori pretul unitar minim.

SELECT ID_ART, DENUMIRE, ID_FABR

FROM articole

WHERE PRET_UNIT > (SELECT 10*MIN(PRET_UNIT)

FROM articole)

Exemplu 9

Aceleasi tabele de la exemplu 6

SELECT ID_ART, DENUMIRE, ID_FABR

FROM articole

WHERE PRET_UNIT < (SELECT AVG(PRET_UNIT)

FROM articole)

Exemple de subinterogari corelate

Exemplu 10

Fie tabelul:

comenzi(NR_COM, DATA_COMENZII, DATA_LIVRARI, ID_CL)

Intrebarea este un exemplu de intrebare corecta ce returneaza o lista a primelor 10 date de livrare din tabelul de comenzi.

SELECT DATA_LIVRARII

FROM comenzi a

WHERE 10>(SELECT COUNT(*)

FROM comenzi b

WHERE b.DATA_LIVRARII>a.DATA_LIVRARII

AND DATA_LIVRARII IS NOT NULL

ORDER BY DATA_LIVRARII

Exemplu 11

Fie tabelul de articole din exemplul din exemplu 4 si tabelul de comenzi din exemplu 8.

Determinati comenzile care au mai mult de 3 articole.

SELECT NR_COM

FROM comenzi c

WHERE 3<(SELECT COUNT(*)

FROM articole a

WHERE c.NR_COM = a.NR_COM)

Exercitiu

Determinati produsele care sunt fabricate de cel putin 2 fabrici.

Exemplu 12

Fie tabelele de comenzi si de articole din exemplul 7.

Determinati toate comenzile de CIELO

SELECT

FROM comenzi c

WHERE CIELO" = (SELECT DENUMIRE

FROM articole a

WHERE c.ID_AR=a.ID_ART)

EXISTS este o functie predicat care acceptata ca paramentru unic a subinterogarii.

Daca o interogarea returneaza o multime de rezultate atunci EXISTS returneaza TRUE in caz contrar FALSE:

Exemplu 13

Fie tabelele:

titlu (ID_TITLU, DENUMIRE_T, SAL_ORA)

salariati(ID_SALARIAT, NUME, PREN, ID_TITLU, ADR)

Determinati titlurile pentru care exista salariati:

SELECT ID_TITLU, DENUMIRE_T

FROM titlu t

WHERE EXISTS (SELECT

FROM salariati s

WHERE t.ID_TITLU=s.ID_TITLU

Exemplu 14

Determinati titlurile pentru care nu exista salariati.

SELECT ID_TITLU, DENUMIRE_T

FROM titlu t

WHERE NOT EXISTS (SELECT

FROM salariati s

WHERE t.ID_TITLU=s.ID_TITLU

Exemplu 15

Determinati fabricile si produsele pentru care nu a existat nici o comanda.

SELECT ID_ART, ID_FABR

FROM produse p

WHERE NOT EXISTS SELECT ID_ART, ID_FABR

FROM articole a

WHERE p.ID_ART = a.ID_ART

AND p.ID_FABR=a.ID.FABR )

Exemplu 16

Aceasta este echivalenta cu cea din exemplu 4.

SELECT NR_COM

FROM articole

WHERE PRET_TOTAL>(SELECT max(PRET_TOTAL))

FROM ARTICOLE

WHERE NRCOM=1023)

Exemplu 17

Aceasta subinterogare determina comenzile care au valoarea mai mica decat valoarea medie:

SELECT c.NR_COM

FROM comenzi c, articole a

WHERE c.NR_COM = s.NR_COM

AND PRET_TOTAL *

(SELECT AVG(PRET_TOTAL))

FROM articole

WHERE c.NR_COM = a.NR_COM)

Exemplu 18

Aceasta instructiune SELECT returneaza numarul tuturor comenzilor care contin un articol al carui PRET_TOTAL este mai mare decat pretul total al cel putin unui articol din comanda cu numarul 1023. Ea poate fi scrisa cu ajutorul cuvantului cheie ANY sau cu ajutorul functiei MIN

SELECT DISTINCT NR_COM

FROM articole

WHERE PRET_TOTAL > ANY (SELECT PRET_TOTAL

FROM articole

WHERE NR_COM=1023)

Exemplu 19

Aceasta este echivalenta cu 18.

SELECT DISTINCT NR_COM

FROM articole

WHERE PRET_TOTAL >(SELECT MIN(PRET_TOTAL))

FROM articole

WHERE NR_COM=1023)

Utilizarea unei conditii de unire in cauza where

Cand se unesc 2 tabele trebue creata o legatura in clauza WHERE prin cel putin a coloana dintr-un tabel si o coloana din alt tabel.

Efectul este un tabel compus care e temporar, in care fiecare pereche de linii satisface legatura, conditiile de unire formeaza o singura linie. Putem crea autouniri (unirea unui tabel cu el insusi) unirea a 2 tabele sau a unirea a mai multor tabele.

Exemplu 20

Exemplu de unire a 2 tabele:

SELECT NUME, PREN, NR_COM

FROM clienti, comenzi

WHERE clienti.NR_CL=comenzi.NR_CL

Exemplu 21

Autounirea (SELF JOIN) creaza o lista de articole din atibutul stoc care sunt produse de 3 fabrici:

SELECT a.ID_FABR, b.ID_FABR, c.ID_FABR, c.ID_ART

FROM stoc a, stoc b, stoc c

WHERE a. ID_ART= b.ID_ART

AND a. ID_ART= c.ID_ART

AND a. ID_FABR< b.ID_FABR

AND b. ID_FABR< c.ID_FABR)

ORDER BY ID_ART

Exemplu 22

Comenzile care au fost comandate in aceeasi zi de 2 ori se considera duplicate si se elimina una.

SELECT *.NUME_CL, *.DATA_COMENZII

FROM emitere-comenzi x

WHERE 1<(SELECT COUNT(x)

FROM emitere-comenzi y

WHERE x. NUME_CL =y. NUME_CL)

Exercitii

Determinati rezultatul fiecarei secvente de instructiuni

SELECT *

FROM Catalog

WHERE NrLeg=(SELECT NrLeg

FROM Student

WHERE Nume = 'Popescu' AND Grupa='222')

SELECT *

FROM Catalog

WHERE NrLeg>(SELECT NrLeg

FROM Student

WHERE Nume = 'Popescu' AND Grupa='222')

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as N

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg=(SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

Eroare subinterogarea returneaza mai mult de 1 inregistrare

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg IN (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg = ANY (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND    

D.cod_disciplina>3)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as N

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg > any (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND EXISTS (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND EXISTS (SELECT N1.NrLeg FROM

Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND

D.cod_disciplina>8)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg <> ALL (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

SELECT S.NrLeg, Nume +' '+Prenume Student,Grupa, cod_disciplina, Nota

FROM Student as S, Catalog as C

WHERE S.NrLeg=C.NrLeg

AND S.NrLeg >= all (SELECT N1.NrLeg

FROM Catalog N1, Discipline D

WHERE N1.cod_disciplina=D.cod_disciplina AND D.cod_disciplina>3)

Notele studentilor al caror nume nu se termina in 'escu'

SELECT S.NrLeg, Nume,Prenume ,Denumire, Nota,Data

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nume in (SELECT Nume

FROM Student

WHERE Nume not like '%escu')

SELECT S.NrLeg, Nume,Prenume ,Denumire, Nota,Data

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nume =any (SELECT Nume

FROM Student

WHERE Nume not like '%escu')

SELECT S.NrLeg, Nume,Prenume ,Denumire, Nota,Data

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nume <> all (SELECT Nume

FROM Student

WHERE Nume like '%escu')

Notele studentilor al caror nume se termina in 'escu'

SELECT S.NrLeg, Nume,Prenume ,Denumire, Nota,Data

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nume <> all (SELECT Nume

FROM Student

WHERE Nume not like '%escu')

Mediile studentilor al caror nume incepe cu 'Pop'

SELECT S.NrLeg Legitimatie, Nume+' '+Prenume Student, AVG(Nota)Media

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nume in (SELECT Nume

FROM Student

WHERE Nume like 'Pop%')

GROUP by S.NrLeg,Nume+' '+Prenume

Studentii care au note mai mari decat media tuturor studentilor

SELECT S.NrLeg Legitimatie, Nume+' '+Prenume Student, Nota

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

AND Nota > (SELECT AVG(Nota)

FROM Catalog )

Order by S.NrLeg

Studentii care au media mai mare decat media tuturor studentilor

SELECT S.NrLeg Legitimatie, Nume+' '+Prenume Student, AVG(Nota) Media

FROM Student S,Catalog C, Discipline D

WHERE S.Nrleg=C.NrLeg

AND D.cod_disciplina=C.cod_disciplina

GROUP BY S.NrLeg,Nume+' '+Prenume

HAVING AVG(Nota) > (SELECT AVG(Nota)

FROM Catalog )

Order by S.NrLeg

Subinterogari iImbricate

Studentii care au note mai mari decat media tuturor studentilor

SELECT *

FROM Student S

WHERE S.NrLeg in (SELECT S1.NrLeg

FROM Student S1, Catalog C

WHERE S1.NrLeg=C.NrLeg

AND Nota>(SELECT avg(Nota)

FROM Catalog ))

Studentii care au media mai mare decat media tuturor studentilor

SELECT *

FROM Student S

WHERE S.NrLeg in (SELECT S1.NrLeg

FROM Student S1, Catalog C

WHERE S1.NrLeg=C.NrLeg

GROUP by S1.NrLeg

HAVING avg(C.Nota)>(SELECT avg(Nota)

FROM Catalog ) )

Subinterogari corelate -ce permit folosirea unei referinte externe

Catalogle studentilor care au numele 'Popescu'

SELECT *

FROM Catalog C

WHERE 'Popescu'=(SELECT Nume

FROM Student S

WHERE S.NrLeg=C.NrLeg)

echivalent cu ..

SELECT C.*

FROM Student S, Catalog C

WHERE S.NrLeg=C.NrLeg

AND Nume='Popescu'

--calculul unei medii a Cataloglor ce depasesc madia pe fiecare student

SELECT S.NrLeg, avg(C.Nota) Media, Count(S.NrLeg) NrCatalog

FROM Student S, Catalog C

WHERE S.NrLeg=C.NrLeg

AND C.Nota> =(SELECT avg(C1.Nota)

FROM Student S1,Catalog C1

WHERE S1.NrLeg=C1.NrLeg AND C1.NrLeg=S.NrLeg)

GROUP by S.NrLeg

--afisarea pt fiecare student a Catalogi maxime mai mare decat media sa

SELECT S.NrLeg, max(C.Nota) Nota_Max, Count(S.NrLeg) NrCatalog

FROM Student S, Catalog C

WHERE S.NrLeg=C.NrLeg

GROUP by S.NrLeg

HAVING max(C.Nota)>=(SELECT avg(C1.Nota)

FROM Student S1,Catalog C1

WHERE S1.NrLeg=C1.NrLeg AND C1.NrLeg=S.NrLeg)

Afisarea mediei fiecarui studentpt comparare cu exemplul anterior

SELECT S1.NrLeg, avg(C1.Nota) Media

FROM Student S1,Catalog C1

WHERE S1.NrLeg=C1.NrLeg

GROUP by S1.NrLeg