Baze de Date - Limbajul SQL- Intero gări complexe . Subinterog ă ri -

1. Baze de Date -Limbajul SQL-Interogăricomplexe. Subinterogări- Universitatea din Craiova, Facultatea de Automatica, Calculatoare si Electronica

2. Introducere • În subcapitolul 4.2 s-au prezentat câteva tipuri de interogări de bază în limbajul SQL. • Datorită generalității și capabilităților limbajului SQL, există foarte multe caracteristici adiționale care permit utilizatorilor sa specifice interogări complexe. • Câteva dintre aceste caracteristici vor fi discutate în acest subcapitol.

3. Subinterogări – Operatorul IN • În unele interogări este necesară mai întâi căutarea unor valori, și apoi folosirea acestora în condiții de comparare. • Astfel de interogări pot fi formulate folosind subinterogări, care sunt blocuri select-from-where(subinterogare) în clauza WHERE a altei interogări (interogare externă). • Se introduce operatorul de comparare IN, care compară o valoare v cu o mulțime V de valori și se evaluează cu TRUE dacă v este unul din elementele mulțimii V.

4. Subinterogări - Operatorul IN • Să se afișeze lista cu proiectele (NrProiect) la care lucrează un angajat cu numele 'popescu', ca angajat sau manager la departamentul care controlează proiectul. • --1 • SELECT DISTINCT NrProiect • FROM Proiecte • WHERE NrProiect IN (SELECT NrProiect • FROM PROIECTE, DEPARTAMENT, ANGAJAT • WHERE PDep=NrDep AND ManagerDep=CNP ANDNume='popescu') • OR • NrProiect IN (SELECT PROIECTE.NrProiect • FROM PROIECTE, ANGAJATIPROIECTE, ANGAJAT • WHERE PROIECTE.NrProiect=ANGAJATIPROIECTE.NrProiect AND PCNP=CNP AND Nume ='popescu')

5. Subinterogări- Operatorul IN • Dacă subinterogarea returnează un singur atribut și un singur tuplu, rezultatul interogării va fi o singură valoare (un scalar). • În aceste cazuri este permisă folosirea operatorului de comparare = în loc de IN. • Dar, în general, o subinterogare returnează o relație, care este o colecție de tupluri.

6. Subinterogări-Operatorul IN • Să se afișeze cnp-urile angajaților care lucrează la aceleași proiecte și cu același număr de ore ca și angajatul cu cnp = '1356789000776'. • --2 • SELECT DISTINCT PCNP • FROM AngajatiProiecte • WHERE (NrProiect, NrOre) IN (SELECT NrProiect, NrOre • FROM AngajatiProiecte • WHERE PCNP = '1356789000776'); • Interogarea mai poate fi scrisă: • SELECT DISTINCT A1.PCNP • FROM AngajatiProiecte A1, AngajatiProiecte A2 • WHERE (A1. NrProiect = A2. NrProiect and A1.NrOre = A2.NrOre and A2. PCNP = '1356789000776' ); • În general, o subinterogare scrisă cu blocuri select-from-where și care folosește operatorul de comparație = sau IN poate fi întotdeauna formulată ca o interogare cu un singur bloc.

7. Subinterogări – Operatorii ANY, SOME, ALL • Alți operatori de comparare din SQL sunt: operatorii ANY , SOME returnează TRUE dacă valoarea v este egală cu o valoare din colecția de valori V.Acești operatori sunt echivalenți cu operatorul IN. • Cuvintele cheie ANY și SOME au același înțeles. • Alți operatori care pot fi combinați cu ANY (sau SOME) sunt: >,>=, <, <=, and < >. • Un alt operator este ALL care poate fi de asemenea combinat cu >,>=, <, <=, and < >. De exemplu condiția de comparare (v > ALL V) returnează TRUE dacă valoarea v este mai mare decât toate valorile din V. • Exemplu: v = ANY(V); v>= ANY(V); v<= ANY(V);v<>ANY(V);

8. Subinterogări • De exemplu: Să se afișeze numele angajaților cu salariul mai mare decât al tuturor angajaților din departamentul 2. • --3 • SELECT Nume, Prenume • FROM Angajat • WHERE • Salariu > ALL (SELECT Salariu FROM Angajat WHERE ADep=2);

9. Subinterogări • În următorul exemplu se ilustrează ambiguitatea de nume a atributelor în subinterogări: • Să se afișeze numele angajaților care au o persoană în întreținere cu același prenume și gen ca și ei. • --4 • SELECT A. Nume, A. Prenume • FROM Angajat AS A • WHEREA.CNP IN (SELECT CNP • FROM PersoaneIntretinere • WHERE (A.Prenume= NumePersoana AND A.Gen=Gen)); • Atributul Gen trebuie redenumit, deoarece el se referă la atributul tabelei Angajat din interogarea externă, iar tabela PersoaneIntretinere are un atribut numit tot Gen. • Toate referințele la atributul Gen din subinterogare se refera la atributul Gen din tabela PersoaneIntretinere. • Nu este nevoie să se redenumească atributele Nume, Prenume, deoarece tabela PersoaneIntretinere nu are atribute cu aceste denumiri, deci nu pot apărea ambiguități. • În general, este recomandată folosirea atributelor tuplu (alias-uri) pentru toate tabelele regerite în interogări pentru a evita erorile și ambiguitățile.

10. Subinterogări • Când o condiție din clauza WHERE a unei subinterogări face referire la anumite atribute din relația declarată în interogarea externă, cele 2 interogări sunt numite corelate. • Interogările corelate trebuie înțelese astfel: subinterogarea este evaluată pentru fiecare tuplu din interogarea externă. Un astfel de exemplu este interogarea de mai sus.

11. Subinterogări • De exemplu interogarea precedentă (4) poate fi formulată: • --5 • SELECT A. Nume, A. Prenume • FROM Angajat AS A, PersoaneIntretinere AS P • WHERE • A.CNP = P.CNP and A.Gen = P.Gen and A.prenume=P. NumePersoana

12. Subinterogări. Funcțiile EXISTS și UNIQUE • Funcția EXISTS în SQL este folosită pentru a verifica dacă rezultatul interogării corelate este vid (nu conține nici un tuplu) sau nevid. • EXISTS(Q) returnează TRUE dacă există cel puțin un tuplu în rezultatul subinterogării Q, și returnează FALSE în rest. • NOT EXISTS(Q) returnează TRUE dacă nu există nici un tuplu în rezultatul subinterogării Q, și returnează FALSE în rest.

13. Subinterogări. Funcțiile EXISTS și UNIQUE • Să se afișeze numele angajaților care au o persoană în întreținere cu același prenume și gen ca și ei. • --6 • SELECT A. Nume, A. Prenume • FROM Angajat AS A • WHERE • EXISTS (SELECT * • FROM PersoaneIntretinere • WHERE (A.CNP=CNP AND A.Prenume=NumePersoana AND A.Gen=Gen)); • Interogarea evaluează pentru fiecare tuplu Angajat subinterogarea care selectează toate persoanele aflate în întreținere cu același CNP, Gen și Prenume ca cele ale tuplului Angajat; dacă cel puțin un tuplu există în rezultatul subinterogării pentru un tuplu Angajat, atunci se selectează acel tuplu Angajat.

14. Subinterogări. Funcțiile EXISTS și UNIQUE • Să se afișeze numele angajaților care nu au persoane în întreținere. • --7 • SELECT Nume, Prenume • FROM Angajat AS A • WHERE • NOT EXISTS (SELECT * • FROM PersoaneIntretinere • WHERE A. CNP=CNP);

15. Subinterogări. Funcțiile EXISTS și UNIQUE • Să se afișeze numele managerilor care au cel puțin o persoană aflată în întreținere. • --8 • SELECT Nume, Prenume • FROM Angajat AS A • WHERE • EXISTS (SELECT * • FROM PersoaneIntretinere • WHERE A.CNP=CNP) • AND • EXISTS (SELECT * • FROM Departament • WHERE A.CNP=ManagerDep); • Funcția SQL- UNIQUE(Q)- returnează TRUE dacă nu există tupluri duplicate în rezultatul interogării Q, altfel retunează FALSE.

16. Mulțimi explicite și redenumirea atributelor în interogări • În SQL este posibilă specificarea explicită a unei mulțimi în clauza WHERE, în locul unei subinterogări. O astfel de mulțime este inclusă în paranteze. • Să se afișeze CNP – ul angajaților care lucrează la proiectele cu numerele 100,200, sau 300. • --9 • SELECT DISTINCT PCNP • FROM AngajatiProiecte • WHERE NrProiect IN (100, 200, 300);

17. Mulțimi explicite și redenumirea atributelor în interogări • În SQL este posibilă redenumirea atributelor care apar în rezultatul interogării: Atribut AS AtributNou. • AS poate fi astfel folosit atât pentru redenumirea atributelor cât și a relațiilor. • Să se afișeze numele angajatului și numele supervizorului său. Se vor redenumi numele angajatului ca AngajatNume și numele supervizorului ca SupervizorNume. Aceste nume vor apărea în rezultatul interogării. • SELECT A.Nume, A.Prenume, S. Nume, S.Prenume • FROM Angajat AS A, Angajat AS S • WHERE • A.SCNP=S.CNP; • Devine: • --10 • SELECT A.Nume As AngajatNume, A.Prenume AS AngajatPrenume, S. Nume AS SupervizorNume, S.Prenume AS SupervizorPrenume • FROM Angajat AS A, Angajat AS S • WHERE • A.SCNP=S.CNP;

18. Joncțiunea tabelelor în SQL • Conceptul de joncțiune a tabelelor a fost încorporat în SQL pentru a permite utilizatorilor să specifice o tabelă ca rezultat al unei operații de joncțiune în clauza FROM a unei interogări. • O joncțiune presupune combinarea atributelor din 2 tabele. În standardul SQL, sunt specificate tipuri de joncțiune: INNER JOIN, NATURAL JOIN, OUTER LEFT JOIN, OUTER RIGHT JOIN. • Joncțiunea INNER JOIN (echivalentă cu JOIN) este cel mai utilizat tip de joncțiune folosit în aplicații. • Acest tip de joncțiune între 2 tabele creează o nouă tabelă rezultat prin combinarea valorilor coloanelorpe baza condiției de joncțiune.

19. Joncțiunea tabelelor în SQL • De exemplu, interogarea: să se afișeze numele angajaților care lucrează la departamentul 'Cercetare'. Este mai simplu să se realizeze joncțiunea pe relațiile Angajat și Departament, apoi operația de selecție. • --11 • SELECT Nume, Prenume,Adresa • FROM (Angajat INNER JOIN Departament ON ADep = NrDep) • WHERE • DenumireDep='Cercetare' ; • Clauza FROM conține joncțiunea celor 2 tabele Angajat și Departament cu condiția ADep = NrDep. Interogarea 11 este echivalentă cu interogarea 6 din subcapitolul 4.2: • SELECT A.Nume, A.Prenume, A. Adresa • FROM Angajat A, Departament D • WHERE D.DenumireDep='Cercetare' AND D.NrDep=A.ADep;

20. Joncțiunea tabelelor în SQL • Conceptul de joncțiune a tabelelor permite utilizatorului să specifice și alte tipuri de joncțiune, cum ar fi joncțiunea naturală -NATURAL JOIN- și diferite tipuri de joncțiune externă- OUTER JOIN. • În joncțiunea naturală a 2 relații, R și S, nu se specifică nici o condiție de joncțiune, deoarece jonctiunea de egalitate se realizează implicit pe atributele cu acelasi nume din relațiile R și S. • Atributele cu același nume sunt incluse o singură dată în rezultat. Dacă numele atributelor care trebuie să apară în condiția de joncțiune nu au același nume, ele pot fi redenumite folosind alias-uri.

21. Joncțiunea tabelelor în SQL • Exemplu de NATURAL JOIN între tabelele Departament și Locatii folosind atributul NrDep: • Să se afișeze departamentele și locațiile lor. • --12 • SELECT * • FROM (Departament NATURAL JOIN Locatii) • Exemplu OUTER JOIN - O joncțiune externă între 2 tabele R și S nu necesită ca fiecare înregistrare din tabela R să se potrivească cu o înregistrare din tabela S. • Să se afișeze toți angajații (chiar dacă nu au supervizor) împreună cu supervizorii lor . • --13 • SELECT A. Nume AS NumeAngajat, S.Nume AS NumeSupervizor • FROM (Angajat AS A LEFT OUTER JOIN Angajat AS S • ON A.SCNP=S.CNP);

22. Funcții de agregare în SQL • În algebra relațională s-a introdus conceptul de funcții de agregare ca și operatie relațională. • Deoarece gruparea și agregarea sunt folosite în multe aplicații cu baze de date, SQL include caracteristici pentru aceste concepte, astfel există următoarele funcții predefinite: COUNT, SUM, MAX, MIN, și AVG. • Funcția COUNT returnează numărul de tupluri sau valori într-o interogare. Funcțiile SUM, MAX, MIN, și AVG sunt aplicate unei mulțimi sau colecții de valori numerice, și returnează suma, valoarea maximă, minimă și media valorilor pe care sunt aplicate. • Aceste funcții sunt aplicate în clauza SELECT sau în clauza HAVING. • Funcțiile MAX și MIN pot fi folosite și pe domenii non-numerice, dacă acele domeniile au definită o relație de ordine.

23. Funcții de agregare în SQL.Exemple • Exemple: • Să se afișeze suma salariilor tuturor angajaților, salariul maxim, salariul minim și salariul mediu. • --14 • SELECT SUM (Salariu), MAX (Salariu), MIN (Salariu),AVG (Salariu) • FROM Angajat; • Să se afișeze suma salariilor, salariul maxim, salariul minim și salariul mediu ale tuturor angajaților din departamentul 'Cercetare'. • --15 • SELECT SUM (Salariu), MAX (Salariu), MIN (Salariu),AVG (Salariu) • FROM Angajat INNER JOIN Departament ON ADep=NrDep • WHERE DenumireDep='Cercetare'.

24. Funcții de agregare în SQL.Exemple • Să se afișeze numărul total de angajați din companie. • --16 • SELECT COUNT(*) • FROM Angajat • Să se afișeze numărul total de angajați din departamentul 'Cercetare'. • --17 • SELECT COUNT(*) • FROM Angajat, Departament • WHERE NrDep=ADep AND DenumireDep='Cercetare'. • COUNT (*) returnează numărul de tupluri. • Să se afișeze numărul de salarii distincte ale angajaților. • --18 • SELECT COUNT (DISTINCT Salariu) • FROM Angajat; • Dacă se calculează COUNT (DISTINCT Salariu) se va afișa numărul de salarii distincte. Nu se vor număra tuplurile care au Salariul nul!!!

25. Funcții de agregare în SQL.Exemple • Să se afișeze numele angajaților care au mai mult de 2 persoane în întreținere. • --19 • SELECT Nume, Prenume • FROM Angajat A • WHERE • (SELECT COUNT (*) • FROM PersoaneIntretinere P • WHERE A.CNP=P. CNP) >= 2;

26. Grupare: Clauzele GROUP BY și HAVING • În multe cazuri dorim aplicarea funcțiilor de agregare unor subgrupuri de tupluri. • Clauza GROUP BY specifică atributele după care se face gruparea. • Să se afișeze pentru fiecare departament, numărul de departament, numărul de angajați și salariul mediu al angajaților. • --20 • SELECT ADep, COUNT (*), AVG (Salariu) • FROM Angajat • GROUP BY ADep • Dacă există valori nule in atributele de grupare, atunci un grup separat este creat cu tuplurile cu valoarea NULL pentru atributele de grupare.

27. Grupare: Clauzele GROUP BY și HAVING • Pentru fiecare proiect să se afișeze numărul de proiect, numele și numărul de angajați care lucrează la fiecare proiect. • --21 • SELECT P.NrProiect, P.DenProiect, COUNT (*) • FROM Proiecte P, AngajatiProiecte AP • WHERE P.NrProiect = AP.NrProiect • GROUP BY P.NrProiect, P.DenProiect

28. Grupare: Clauzele GROUP BY și HAVING • De exemplu, să presupunem că dorim modificarea interogării precedente astfel încât să se afișeze proiectele la care lucrează minim 2 angajați. • SQL oferă clauza HAVING care apare împreună cu clauza GROUP BY. Cu ajutorul clauzei HAVING se pot pune condiții pe grupurile de tupluri asociate cu fiecare valoare a atributelor de grupare. Doar grupurile care satisfac condiția sunt afișate în rezultatul interogării. • --22 • SELECT P.NrProiect, P.DenProiect, COUNT (*) AS NRAngajati • FROM Proiecte AS P, AngajatiProiecte AS AP • WHERE P.NrProiect = AP.NrProiect • GROUP BY P.NrProiect, P.DenProiect • HAVING COUNT (*) >=2;

29. Grupare: Clauzele GROUP BY și HAVING • Pentru fiecare proiect, să se afișeze numărul proiectului, numele proiectului, și numărul de angajați din departamentul 3 care lucrează la proiect. • --23 • SELECT P.NrProiect, P.DenProiect , COUNT (*) • FROM Proiecte AS P, AngajatiProiecte AS AP , Angajat AS A • WHERE P.NrProiect=AP.NrProiect AND AP.PCNP=A.CNP AND A.ADep=3 • GROUP BY P.NrProiect, P.DenProiect;

30. Grupare: Clauzele GROUP BY și HAVING • De exemplu, să presupunem că dorim modificarea interogării precedente astfel încât să se afișeze proiectele la care lucrează minim 2 angajați. • SQL oferă clauza HAVING care apare împreună cu clauza GROUP BY. Cu ajutorul clauzei HAVING se pot pune condiții pe grupurile de tupluri asociate cu fiecare valoare a atributelor de grupare. Doar grupurile care satisfac condiția sunt afișate în rezultatul interogării. • --24 • SELECT P.NrProiect, P.DenProiect, COUNT (*) AS NRAngajati • FROM Proiecte AS P, AngajatiProiecte AS AP • WHERE P.NrProiect = AP.NrProiect • GROUP BY P.NrProiect, P.DenProiect • HAVING COUNT (*) >=2;

31. Grupare: Clauzele GROUP BY și HAVING • Să se afișeze numărul total de angajați ale căror salarii depășesc 3000000 în fiecare departament, dar numai pentru departamentele care au peste 2 angajați. • --25 • SELECT NrDep, COUNT (*) • FROM Departament, Angajat • WHERE NrDep=ADep and Salariu >3000000 • GROUP BY NrDep • HAVING COUNT (*) >=2; • Interogarea este incorectă deoarece se selectează mai întâi departamanetele în care lucrează angajații care au salarii mai mari de 3000000 (mai întâi se executa claza WHERE). Clauza HAVING este executată ulterior pentru a selecta grupuri individuale. Astfel se numără din fiecare departament angajații care au salarii mai mari de 3000000.

32. Grupare: Clauzele GROUP BY și HAVING • Formularea corectă a interogării de mai sus este: • Pentru fiecare departament care are mai mult de 2 angajați să se afișeze numarul de departament și numărul de angajați cu salarii mai mari de 3000000. • --26 • SELECT NrDep,COUNT (*) • FROM Departament, Angajat • WHERE NrDep=ADep AND Salariu >3000000 AND • NrDep IN (SELECT NrDep • FROM Angajat • GROUP BY ADep • HAVING COUNT (*) >= 2) • GROUP BY NrDep;

33. Tema • Să se formuleze interogari si subinterogari cu: • Operatorii IN, ANY, ALL • Functiile EXISTS, NOT EXISTS, UNIQUE • Operatorii de jonctiune: INNER JOIN, NATURAL JOIN, OUTER JOIN • Functiile de agregare SUM, COUNT, MAX, MIN, clauza GROUP BY, HAVING