Baze de Date - Limbajul SQL-Intero gări complexe. Subinterog ă ri- Universitatea din Craiova,

Baze de Baze de DateDate

Anca IonAnca Ion20201111-201-20122

Baze de Date-Limbajul SQL-Interogări complexe. Subinterogări-

Universitatea din Craiova,Facultatea de Automatica, Calculatoare si Electronica



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.



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.



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.

--1SELECT DISTINCT NrProiectFROM ProiecteWHERE NrProiect IN (SELECT NrProiect FROM PROIECTE, DEPARTAMENT, ANGAJAT WHERE PDep=NrDep AND ManagerDep=CNP AND

Nume='popescu')OR NrProiect IN (SELECT PROIECTE.NrProiectFROM PROIECTE, ANGAJATIPROIECTE, ANGAJATWHERE

PROIECTE.NrProiect=ANGAJATIPROIECTE.NrProiect AND PCNP=CNP AND Nume ='popescu')



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.



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'.

--2SELECT DISTINCT PCNPFROM AngajatiProiecteWHERE (NrProiect, NrOre) IN (SELECT NrProiect, NrOre

FROM AngajatiProiecte WHERE PCNP = '1356789000776');

• Interogarea mai poate fi scrisă:SELECT DISTINCT A1.PCNPFROM AngajatiProiecte A1, AngajatiProiecte A2WHERE (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.



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);


Anca IonAnca Ion20201111-201-20122•De exemplu: Să se afișeze numele angajaților cu salariul

mai mare decât al tuturor angajaților din departamentul 2. --3SELECT Nume, PrenumeFROM Angajat WHERESalariu > ALL (SELECT Salariu FROM Angajat WHERE ADep=2);

Subinterogări


Anca IonAnca Ion20201111-201-20122•Î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.--4SELECT A. Nume, A. PrenumeFROM Angajat AS AWHERE A.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.

Subinterogări


Anca IonAnca Ion20201111-201-20122•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.

Subinterogări


Anca IonAnca Ion20201111-201-20122•De exemplu interogarea precedentă (4) poate fi formulată:

--5

SELECT A. Nume, A. PrenumeFROM Angajat AS A, PersoaneIntretinere AS PWHEREA.CNP = P.CNP and A.Gen = P.Gen and A.prenume=P. NumePersoana

Subinterogări


Anca IonAnca Ion20201111-201-20122•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.

Subinterogări. Funcțiile EXISTS și UNIQUE


Anca IonAnca Ion20201111-201-20122•Să se afișeze numele angajaților care au cel putin o persoană

în întreținere cu același prenume și gen ca și ei.--6SELECT A. Nume, A. PrenumeFROM Angajat AS AWHEREEXISTS (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.



Anca IonAnca Ion20201111-201-20122•Să se afișeze numele angajaților care nu au persoane în

întreținere.--7SELECT Nume, PrenumeFROM Angajat AS AWHERE

NOT EXISTS (SELECT *FROM PersoaneIntretinereWHERE A. CNP=CNP);



Anca IonAnca Ion20201111-201-20122•Să se afișeze numele managerilor care au cel puțin o

persoană aflată în întreținere.--8SELECT Nume, PrenumeFROM Angajat AS AWHERE

EXISTS (SELECT *FROM PersoaneIntretinereWHERE A.CNP=CNP)

ANDEXISTS (SELECT *

FROM DepartamentWHERE A.CNP=ManagerDep);

•Funcția SQL- UNIQUE(Q)- returnează TRUE dacă nu există tupluri duplicate în rezultatul interogării Q, altfel retunează FALSE.



Anca IonAnca Ion20201111-201-20122•Î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. --9SELECT DISTINCT PCNPFROM AngajatiProiecteWHERE NrProiect IN (100, 200, 300);

Mulțimi explicite și redenumirea atributelor în interogări


Anca IonAnca Ion20201111-201-20122 Î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.PrenumeFROM Angajat AS A, Angajat AS SWHEREA.SCNP=S.CNP;

Devine:--10

SELECT A.Nume As AngajatNume, A.Prenume AS AngajatPrenume, S. Nume AS SupervizorNume, S.Prenume AS SupervizorPrenumeFROM Angajat AS A, Angajat AS SWHEREA.SCNP=S.CNP;

Mulțimi explicite și redenumirea atributelor în interogări


Anca IonAnca Ion20201111-201-20122•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 coloanelor pe baza condiției de joncțiune.

Joncțiunea tabelelor în SQL


Anca IonAnca Ion20201111-201-20122•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. --11SELECT Nume, Prenume,AdresaFROM (Angajat INNER JOIN Departament ON ADep = NrDep)WHEREDenumireDep='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. AdresaFROM Angajat A, Departament DWHERE D.DenumireDep='Cercetare' AND D.NrDep=A.ADep;



Anca IonAnca Ion20201111-201-20122•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.



Anca IonAnca Ion20201111-201-20122•Exemplu de NATURAL JOIN între tabelele Departament și

Locatii folosind atributul NrDep:

•Să se afișeze departamentele și locațiile lor.--12SELECT *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 .--13SELECT A. Nume AS NumeAngajat, S.Nume AS NumeSupervizorFROM (Angajat AS A LEFT OUTER JOIN Angajat AS SON A.SCNP=S.CNP);



Anca IonAnca Ion20201111-201-20122•Î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.

Funcții de agregare în SQL


Anca IonAnca Ion20201111-201-20122•Exemple:

•Să se afișeze suma salariilor tuturor angajaților, salariul maxim, salariul minim și salariul mediu.--14SELECT 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'.--15SELECT SUM (Salariu), MAX (Salariu), MIN (Salariu),AVG (Salariu)FROM Angajat INNER JOIN Departament ON ADep=NrDepWHERE DenumireDep='Cercetare'.

Funcții de agregare în SQL.Exemple


Anca IonAnca Ion20201111-201-20122•Să se afișeze numărul total de angajați din companie.

--16SELECT COUNT(*)FROM Angajat

•Să se afișeze numărul total de angajați din departamentul 'Cercetare'.--17SELECT COUNT(*)FROM Angajat, DepartamentWHERE NrDep=ADep AND DenumireDep='Cercetare'.

•COUNT (*) returnează numărul de tupluri.

•Să se afișeze numărul de salarii distincte ale angajaților.--18SELECT 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!!!



Anca IonAnca Ion20201111-201-20122•Să se afișeze numele angajaților care au mai mult de 2

persoane în întreținere. •--19SELECT Nume, PrenumeFROM Angajat AWHERE (SELECT COUNT (*)FROM PersoaneIntretinere PWHERE A.CNP=P. CNP) >= 2;



Anca IonAnca Ion20201111-201-20122•Î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 angajați și salariul mediu al angajaților.--20SELECT 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.

Grupare: Clauzele GROUP BY și HAVING


Anca IonAnca Ion20201111-201-20122•Pentru fiecare proiect să se afișeze numărul de proiect,

numele proiectului și numărul de angajați care lucrează la fiecare proiect.--21SELECT P.NrProiect, P.DenProiect, COUNT (PCNP)FROM Proiecte P, AngajatiProiecte APWHERE P.NrProiect = AP.NrProiectGROUP BY P.NrProiect, P.DenProiect



Anca IonAnca Ion20201111-201-20122•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 NRAngajatiFROM Proiecte AS P, AngajatiProiecte AS APWHERE P.NrProiect = AP.NrProiectGROUP BY P.NrProiect, P.DenProiectHAVING COUNT (*) >=2;



Anca IonAnca Ion20201111-201-20122•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 AWHERE P.NrProiect=AP.NrProiect AND AP.PCNP=A.CNP AND A.ADep=3GROUP BY P.NrProiect, P.DenProiect;



Anca IonAnca Ion20201111-201-20122•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 NRAngajatiFROM Proiecte AS P, AngajatiProiecte AS APWHERE P.NrProiect = AP.NrProiectGROUP BY P.NrProiect, P.DenProiectHAVING COUNT (*) >=2;



Anca IonAnca Ion20201111-201-20122•Pentru fiecare departament, să se afișeze numărul total de

angajați ale căror salarii depășesc 3000000, dar numai pentru departamentele care au peste 2 angajați.--25SELECT NrDep, COUNT (*)FROM Departament, AngajatWHERE NrDep=ADep and Salariu >3000000GROUP BY NrDepHAVING COUNT (*) >=2;

•Interogarea este incorectă deoarece se selectează mai întâi departamentele î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.



Anca IonAnca Ion20201111-201-20122•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.--26SELECT NrDep,COUNT (*)FROM Departament, AngajatWHERE NrDep=ADep AND Salariu >3000000 ANDNrDep IN (SELECT ADepFROM AngajatGROUP BY ADepHAVING COUNT (*) >= 2)GROUP BY NrDep;



Anca IonAnca Ion20201111-201-20122•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

Tema

Baze de Date - Limbajul SQL-Intero gări complexe. Subinterog ă ri- Universitatea din Craiova,

Documents

Transcript of Baze de Date - Limbajul SQL-Intero gări complexe. Subinterog ă ri- Universitatea din Craiova,