Baze de Date Anca Ion 2011 2012 Baze
Baze de Date Anca Ion 2011 -2012 Baze de Date -Limbajul SQL-Interogări complexe. Subinterogări. Universitatea 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 (Nr. Proiect) la care lucrează un angajat cu numele 'popescu', ca angajat sau manager la departamentul care controlează proiectul. --1 SELECT DISTINCT Nr. Proiect FROM Proiecte WHERE Nr. Proiect IN (SELECT Nr. Proiect FROM PROIECTE, DEPARTAMENT, ANGAJAT WHERE PDep=Nr. Dep AND Manager. Dep=CNP AND Nume='popescu') OR Nr. Proiect IN (SELECT PROIECTE. Nr. Proiect FROM PROIECTE, ANGAJATIPROIECTE, ANGAJAT WHERE PROIECTE. Nr. Proiect=ANGAJATIPROIECTE. Nr. Proiect 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 Angajati. Proiecte WHERE (Nr. Proiect, Nr. Ore) IN (SELECT Nr. Proiect, Nr. Ore FROM Angajati. Proiecte WHERE PCNP = '1356789000776'); • Interogarea mai poate fi scrisă: SELECT DISTINCT A 1. PCNP FROM Angajati. Proiecte A 1, Angajati. Proiecte A 2 WHERE (A 1. Nr. Proiect = A 2. Nr. Proiect and A 1. Nr. Ore = A 2. Nr. Ore and A 2. 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);
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 Persoane. Intretinere WHERE (A. Prenume= Nume. Persoana AND A. Gen=Gen)); • Atributul Gen trebuie redenumit, deoarece el se referă la atributul tabelei Angajat din interogarea externă, iar tabela Persoane. Intretinere are un atribut numit tot Gen. • Toate referințele la atributul Gen din subinterogare se refera la atributul Gen din tabela Persoane. Intretinere. • Nu este nevoie să se redenumească atributele Nume, Prenume, deoarece tabela Persoane. Intretinere 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, Persoane. Intretinere AS P WHERE A. CNP = P. CNP and A. Gen = P. Gen and A. prenume=P. Nume. Persoana
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 Persoane. Intretinere WHERE (A. CNP=CNP AND A. Prenume=Nume. Persoana 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 Persoane. Intretinere 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 Persoane. Intretinere WHERE A. CNP=CNP) AND EXISTS (SELECT * FROM Departament WHERE A. CNP=Manager. Dep); • 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 Angajati. Proiecte WHERE Nr. Proiect 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 Atribut. Nou. 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 Angajat. Nume și numele supervizorului ca Supervizor. Nume. 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 Angajat. Nume, A. Prenume AS Angajat. Prenume, S. Nume AS Supervizor. Nume, S. Prenume AS Supervizor. Prenume 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 = Nr. Dep) WHERE Denumire. Dep='Cercetare' ; • Clauza FROM conține joncțiunea celor 2 tabele Angajat și Departament cu condiția ADep = Nr. Dep. 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. Denumire. Dep='Cercetare' AND D. Nr. Dep=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 Nr. Dep: • 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 Nume. Angajat, S. Nume AS Nume. Supervizor 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=Nr. Dep WHERE Denumire. Dep='Cercetare'.
Funcții de agregare în SQL. Exemple • Să se afișeze numărul total de angajați din companie. --16 SELECT COUNT(*) FROM Angajat Baze de Date Anca Ion 2011 -2012 • Să se afișeze numărul total de angajați din departamentul 'Cercetare'. --17 SELECT COUNT(*) FROM Angajat, Departament WHERE Nr. Dep=ADep AND Denumire. Dep='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 Persoane. Intretinere 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 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 proiectului și numărul de angajați care lucrează la fiecare proiect. --21 SELECT P. Nr. Proiect, P. Den. Proiect, COUNT (PCNP) FROM Proiecte P, Angajati. Proiecte AP WHERE P. Nr. Proiect = AP. Nr. Proiect GROUP BY P. Nr. Proiect, P. Den. Proiect
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. Nr. Proiect, P. Den. Proiect, COUNT (*) AS NRAngajati FROM Proiecte AS P, Angajati. Proiecte AS AP WHERE P. Nr. Proiect = AP. Nr. Proiect GROUP BY P. Nr. Proiect, P. Den. Proiect 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. Nr. Proiect, P. Den. Proiect , COUNT (*) FROM Proiecte AS P, Angajati. Proiecte AS AP , Angajat AS A WHERE P. Nr. Proiect=AP. Nr. Proiect AND AP. PCNP=A. CNP AND A. ADep=3 GROUP BY P. Nr. Proiect, P. Den. Proiect;
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. Nr. Proiect, P. Den. Proiect, COUNT (*) AS NRAngajati FROM Proiecte AS P, Angajati. Proiecte AS AP WHERE P. Nr. Proiect = AP. Nr. Proiect GROUP BY P. Nr. Proiect, P. Den. Proiect 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 Nr. Dep, COUNT (*) FROM Departament, Angajat WHERE Nr. Dep=ADep and Salariu >3000000 GROUP BY Nr. Dep HAVING COUNT (*) >=2; • Interogarea este incorectă deoarece se selectează mai întâi departamentele în care lucrează angajații care au salarii 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 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 mai mult de 2 angajați să se afișeze numarul de departament și numărul de angajați cu salarii mari de 3000000. --26 SELECT Nr. Dep, COUNT (*) FROM Departament, Angajat WHERE Nr. Dep=ADep AND Salariu >3000000 AND Nr. Dep IN (SELECT ADep FROM Angajat GROUP BY ADep HAVING COUNT (*) >= 2) GROUP BY Nr. Dep;
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
- Slides: 33