Transcript Baze de Date - Universitatea din Craiova

Baze de Date
Anca Ion
2011-2012
Baze de Date
-Limbajul SQL-Interogări complexe. SubinterogăriUniversitatea din Craiova,
Facultatea de Automatica, Calculatoare si Electronica
Introducere
Baze de Date
Anca Ion
2011-2012
• Î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
Baze de Date
Anca Ion
2011-2012
• Î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
Baze de Date
Anca Ion
2011-2012
• 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 AND
Nume='popescu')
OR
NrProiect IN (SELECT PROIECTE.NrProiect
FROM PROIECTE, ANGAJATIPROIECTE, ANGAJAT
WHERE PROIECTE.NrProiect=ANGAJATIPROIECTE.NrProiect
AND PCNP=CNP AND Nume ='popescu')
Subinterogări- Operatorul IN
Baze de Date
Anca Ion
2011-2012
• 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
Baze de Date
Anca Ion
2011-2012
• 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.
Subinterogări – Operatorii ANY, SOME, ALL
Baze de Date
Anca Ion
2011-2012
•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);
Subinterogări
Baze de Date
Anca Ion
2011-2012
•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);
Baze de Date
Anca Ion
2011-2012
•Î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
WHERE 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
Subinterogări
Baze de Date
Anca Ion
2011-2012
•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
Baze de Date
Anca Ion
2011-2012
•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
Subinterogări. Funcțiile EXISTS și UNIQUE
Baze de Date
Anca Ion
2011-2012
•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
Baze de Date
Anca Ion
2011-2012
•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.
--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.
Subinterogări. Funcțiile EXISTS și UNIQUE
Baze de Date
Anca Ion
2011-2012
•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);
Subinterogări. Funcțiile EXISTS și UNIQUE
Baze de Date
Anca Ion
2011-2012
•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.
Mulțimi explicite și redenumirea atributelor în interogări
Baze de Date
Anca Ion
2011-2012
•Î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);
Mulțimi explicite și redenumirea atributelor în interogări
Baze de Date
Anca Ion
2011-2012
 Î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;
Joncțiunea tabelelor în SQL
Baze de Date
Anca Ion
2011-2012
•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
Baze de Date
Anca Ion
2011-2012
•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;
Joncțiunea tabelelor în SQL
Baze de Date
Anca Ion
2011-2012
•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.
Joncțiunea tabelelor în SQL
Baze de Date
Anca Ion
2011-2012
•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);
Funcții de agregare în SQL
Baze de Date
Anca Ion
2011-2012
•Î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.Exemple
Baze de Date
Anca Ion
2011-2012
•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'.
Funcții de agregare în SQL.Exemple
Baze de Date
Anca Ion
2011-2012
•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!!!
Funcții de agregare în SQL.Exemple
Baze de Date
Anca Ion
2011-2012
•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;
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•Î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.
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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 (PCNP)
FROM Proiecte P, AngajatiProiecte AP
WHERE P.NrProiect = AP.NrProiect
GROUP BY P.NrProiect, P.DenProiect
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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;
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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
A.ADep=3
GROUP BY P.NrProiect, P.DenProiect;
AND
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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;
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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.
--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
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.
Grupare: Clauzele GROUP BY și HAVING
Baze de Date
Anca Ion
2011-2012
•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;
Tema
Baze de Date
Anca Ion
2011-2012
•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