Fundamentele bazelor de date Instrucțiunea Select SQLÎn articolul din numărul 5/99 am făcut cunoștință cu cîteva instrucțiuni ale limbajului SQL: Create table, Insert, Update, Delete. Aceste instrucțiuni ne-au permis să creăm tabele, să introducem și să actualizăm informații în aceste tabele. În articolul de față prezentăm câteva posibilități de interogare a bazelor de date relaționale cu ajutorul instrucțiunii Select SQL. Deoarece aproape fiecare SGBD relațional implementează un subset al limbajului SQL, plus unele "inovații" care creează uneori confuzii, insistăm doar asupra unor aspecte pe care le considerăm esențiale. Limbajul SQL (Structured Query Language) a fost creat prin anii '70 în laboratoarele din San Jose ale firmei IBM. Sintaxa acestui limbaj este, după cum ne-am putut da seama, destul de apropiată de limba engleză, fiind adecvat programării pe calculator și ușor de asimilat de către utilizatori. SQL nu este un limbaj de programare (așa cum sunt limbajele procedurale cunoscute: C, Pascal, Fortran, Cobol etc.), este destinat manipulării datelor dintr-o bază de date relațională. De aceea el este inclus într-un limbaj procedural cu care este dotat aproape fiecare SGBD relațional. Sintaxa generală a instrucțiunii Select SQL este următoarea: SELECT [DISTINCT] atribut/expresie... FROM tabelă [AS sinonim]... [WHERE condiții de selecție și/sau condiții de legătură...] [GROUP BY atribut...] [HAVING condiții de selecție...] [ORDER BY atribut ASC|DESC...]; Rezultatul selecției este o tabelă ale cărei coloane sunt câmpurile care apar în lista clauzei Select. Pentru a ilustra mai multe posibilități de utilizare a instrucțiunii Select SQL, vom lucra cu cinci exemple de baze de date. Numele tabelelor și ale cîmpurilor au fost alese în așa fel încât să sugereze cât mai bine semnificația lor, precum și să evidențieze cât mai clar legăturile dintre tabele. În articolul următor aceste exemple vor fi reluate și explicate, astfel că în final vom înțelege de ce o bază de date trebuie să fie compusă din așa de multe tabele. Pentru început trebuie să știm că în felul acesta se garantează corectitudinea datelor introduse (sau cel puțin există un control din partea SGBD-ului), se elimină redundanțele (repetițiile inutile), și eventuale actualizări se pot face ușor fără a afecta integritatea informațiilor deja introduse.
Selectarea unor informații dintr-o tabelă Exemplul 1 - o evidență simplificată a studenților: STUDNOTE(Stud,Disc,Nota) Pentru a ilustra utilizarea instrucțiunii Select folosind o singură tabelă, vom face pentru început referire la tabela StudNote din primul exemplu care conține informații despre notele obținute de fiecare student la toate disciplinele pe care le studiază. Structura tabelei este foarte simplă, avînd doar trei coloane (Stud, Disc, Nota), primele două formând cheia primară. Cel mai simplu exemplu de instrucțiune Select este următorul: Select * from StudNote; Această instrucțiune selectează toate informațiile din tabela StudNote. Caracterul '*' se referă la toate coloanele tabelei (sau tabelelor - în cazul general) din lista clauzei from. Putem selecta o parte din coloanele tabelei, ca în exemplul de mai jos: Select Disc from StudNote; Această instrucțiune selectează lista disciplinelor care sunt studiate. Dacă o folosim în forma prezentată mai sus, vom observa că tabela rezultat va avea tot atâtea linii câte are și tabela StudNote, aceasta deoarece fiecare disciplină apare de mai multe ori (o dată pentru fiecare student). Următorul exemplu arată cum se poate obține lista studenților, de data aceasta fără repetiții supărătoare: Select distinct Stud from StudNote; Putem cere selectarea unor linii care îndeplinesc anumite condiții, de exemplu lista notelor sub 5: Select * from StudNote where Nota<5; În clauza where putem preciza condiții complexe de căutare, combinate cu operatorii logici AND, OR și NOT, ordinea de evaluare fiind cea cunoscută. Se pot folosi și paranteze pentru a controla ordinea de evaluare a condițiilor.
Funcții de agregare în interogări cu o singură tabelă Dacă ne interesează care este cea mai mare și cea mai mică notă dintre cele care au fost date, formulăm următoarea interogare: Select min(Nota), max(Nota) from StudNote; Media generală se obține astfel: Select avg(Nota) from StudNote; Folosim funcția count pentru a afla numărul de înregistrări ale tabelei StudNote: Select count(*) from StudNote; Standardul SQL definește următoarele funcții de agregare: - min: valoarea minimă de pe o anumită coloană; - max: valoarea maximă de pe o anumită coloană; - sum: suma valorilor de pe o anumită coloană; - avg: media aritmetică a valorilor de pe o anumită coloană; - count: numărul de înregistrări selectate. Funcțiile sum și avg pot fi folosite doar pentru coloane care au valori numerice, spre deosebire de celelalte care pot opera cu orice tip de valori. Funcția avg poate returna o valoare de tip diferit de cel al atributului căruia i se aplică (de exemplu, media aritmetică a unor valori întregi poate fi o valoare cu zecimale). Sunt necesare câteva precizări în legătură cu aceste funcții. Implicit ele iau în considerare toate valorile aflate pe coloana specificată; dacă se dorește să fie prelucrate doar valorile distincte, atunci numele coloanei trebuie precedat de cuvîntul cheie distinct. Precizăm că funcția count poate fi folosită pentru a număra toate înregistrările - forma count(*), sau pentru a număra valorile distincte ale unei anumite coloane. De exemplu, ne interesează câți studenți avem în evidență: Select count(distinct Stud) from StudNote; Să presupunem că ne interesează notele cele mai mari, dar și care sunt studenții care au primit acele note. Pentru aceasta trebuie să comparăm fiecare notă cu valoarea maximă, care poate fi obținută printr-o interogare. Deducem că trebuie să folosim interogări SQL imbricate: Select * from StudNote where Nota = (Select max(Nota) from StudNote); Comparația este posibilă deoarece instrucțiunea Select interioară returnează o singură valoare. Acestea au fost interogări relativ simple. Dacă dorim să știm care studenți sunt restanțieri, interogarea este de asemenea foarte simplă: Select Distinct Stud from StudNote where Nota<5; Am folosit clauza distinct deoarece, altfel, studenții care au mai mult de o restanță apar de mai multe ori în listă. Dacă în schimb dorim să știm care sunt studenții integraliști, interogarea ne va da serioase dureri de cap. Aceasta deoarece, spre deosebire de cazul precedent, aici trebuie să formulăm altfel condițiile de selecție. Prezentăm în continuare două formulări echivalente: - toate notele obținute de un student sunt de cel puțin 5; - nici o notă obținută de un student nu este sub 5. Ar trebui ca pentru fiecare student să încercăm să selectăm numai înregistrările corespunzătoare acestuia și care au nota sub 5. Se rețin în rezultatul final numai acei studenți pentru care nu s-a selectat nici o înregistrare (rezultatul selecției este vid). În pseudocod această problemă se rezolvă astfel: pentru i=1,n S Ź Æ; pentru j=1,n dacă (StudNote[i].Stud = StudNote[j].Stud și StudNote[j].Nota < 5) S Ź S Ç StudNote[j]; dacă (S = Æ) output(StudNote[i].Stud);
În secvența de mai sus am comparat câmpul Stud din înregistrările StudNote[i] și StudNote[j]. În limbajul SQL nu avem o astfel de posibilitate; putem în schimb să folosim o tabelă sub mai multe nume (sinonime), și să comparăm două câmpuri ca și cum ele ar face parte din tabele diferite. Interogarea se rezolvă astfel: Select Stud from StudNote as S where not exists (Select * from StudNote as T where S.Stud = T.Stud and T.Nota < 5); Remarcăm folosirea expresiei condiționale not exists, care returnează valoarea true dacă tabela transmisă ca argument este vidă. Până acum, funcțiile de agregare au operat asupra tuturor valorilor din rezultatul selcției. Putem să construim și grupe de înregistrări asupra cărora să opereze aceste funcții. Ne interesează media fiecărui student: Select Stud, avg(Nota) from StudNote group by Stud; Putem ordona această situație descrescător după medie și în ordine alfabetică după nume: Select Stud, avg(Nota) from StudNote group by Stud order by 2 desc, 1; Dacă dorim să selectăm doar acei studenți care au media minimum 9, folosim și clauza having: Select Stud, avg(Nota) from StudNote group by Stud having avg(Nota) >= 9;
Selectarea unor informații din mai multe tabele Exemplul 2 - evidența angajaților unei instituții: DEPARTAMENTE(Codd,Dendep,Sefd) PERSONAL(Nrleg,Nume,Dep) Ne interesează să aflăm, pentru fiecare departament, cine este șeful acestuia și câți angajați lucrează la departamentul respectiv: Select Codd, Dendep, Sefd, Nume, (Select count(*) from Personal where Codd = Dep) from Departamente,Personal where Sefd = Nrl; Remarcăm și în acest caz folosirea unei instrucțiuni Select care returnează o valoare. Pentru această instrucțiune Select atributul Codd joacă același rol pe care îl are o variabilă globală într-un program C sau Pascal. Aceasta deoarece ne interesează câți angajați are un anumit departament: dacă am fi inclus în lista clauzei from și tabela Departamente, atunci instrucțiunea Select interioară ar fi pierdut valoarea atributului Codd din exterior (să ne gândim ce se întâmplă atunci când scriem un program în C sau Pascal). Dar lucrurile nu s-ar opri aici: instrucțiunea Select interioară ar efectua produsul cartezian al celor două tabele și ar număra câte înregistrări ale acestui produs îndeplinesc condiția din clauza where. În instrucțiunea Select exterioară se remarcă în clauza where o condiție care se numește condiție de legătură. Aceste condiții de legătură se scriu în general astfel: cheie-primară = cheie-externă
Exemplul 3 - evidența facturilor: CLIENTI(Codcl,Nume,Localit,Adresa) PRODUSE(Codpr,Denpr,Pretu) FACTURI(Nrf,Dataf,Codcl) DETALII(Nrf,Codpr,Cant)
Dorim să știm care este valoarea fiecărei facturi: Select Facturi.Nrf, Dataf, Facturi.Codf, Nume, Localit, Adresa, Sum(Cant*Pretu) from Facturi, Clienti, Detalii, Produse where Facturi.Nrf = Detalii.Nrf and Facturi.Codcl = Clienti.Codcl and Detalii.Codpr = Produse.Codpr group by Facturi.Nrf, Dataf, Facturi.Codc, Nume, Localit, Adresa; Remarcăm mai întâi necesitatea de a califica acele atribute care apar cu același nume în mai multe tabele. În al doilea rând (un fapt nu tocmai plăcut), necesitatea de a include în lista clauzei group by toate câmpurile din clauza Select care nu sunt funcții de agregare. Standardul SQL cere acest lucru aparent lipsit de logică și majoritatea SGBD-urilor care implementează limbajul Select SQL se conformează acestei cerințe. În al treilea rând observăm că în lista clauzei Select putem avea și expresii aritmetice, chiar și ca argumente ale unei funcții de agregare. O altă interogare pe care o putem face este: suma totală pe care a plătit-o fiecare client. Aceasta este ceva mai scurtă decât precedenta: Select Facturi.Codcl, Nume, Localit, Adresa, Sum(Cant*Pretu) from Facturi, Clienti, Detalii, Produse where Facturi.Nrf = Detalii.Nrf and Facturi.Codcl = Clienti.Codcl and Detalii.Codpr = Produse.Codpr group by Facturi.Codf, Nume, Localit, Adresa; Dacă dorim să aflăm căror clienți le-am întocmit facturi care totalizează o valoare maximă, trebuie să procedăm în doi pași. Mai întâi creăm o tabelă nouă în care depunem rezultatele selecției de mai sus. Pentru aceasta folosim instrucțiunea Insert cu următoarea sintaxă, presupunând că avem creată o tabelă Valori (Codf,Nume, Localit,Adresa,Val): Insert into Valori Select Facturi.Codf, Nume, Localit, Adresa, Sum(Cant*Pretu) from Facturi, Clienti, Detalii, Produse where Facturi.Nrf = Detalii.Nrf and Facturi.Codcl = Clienti.Codcl and Detalii.Codpr = Produse.Codpr group by Facturi.Codf, Nume, Localit, Adresa; La pasul al doilea putem extrage informațiile care ne interesează: Select Codcl, Nume, Localit, Adresa, Val from Valori where Val = (Select max(Val) from Valori);
Exemplul 4 - evidența comenzilor FURNIZORI(Codf,Nume,Localit,Adresa) PRODUSE(Codp,Denp) OFERTE(Codf,Codp,Pretu) COMENZI(Nrc,Datac,Codf) DETALII(Nrf,Codp,Cant) Dorim să știm care este valoarea fiecărei comenzi: Select Comenzi.Nrc, Datac, Comenzi.Codf, Nume, Localit, Adresa, Sum(Cant*Pretu) from Furnizori, Oferte, Comenzi, Detalii where Comenzi.Nrc = Detalii.Nrf and Detalii.Codp = Oferte.Codp and Comenzi.Codf = Furnizori.Codf and Detalii.Codp = Oferte.Codp and Comenzi.Nrc = Oferte.Codf group by Comenzi.Nrc, Datac, Comenzi.Codf, Nume, Localit, Adresa; Am fost nevoiți să punem toate cele patru condiții de legătură, deoarece informațiile despre prețurile produselor se află în tabela Oferte - fiecare produs poate avea prețuri diferite de la un furnizor la altul. Ne interesează de unde putem achiziționa fiecare produs la cel mai mic preț: Select Produse.Codp, Denp, Pretu, Furnizori.Codf, Nume, Localit, Adresa from Oferte as O, Furnizori as F, Produse as P where O.Codf = (Select Codf from Oferte as Q where P.Codp = Q.Codp and Pretu = (Select min(Pretu) from Oferte)) and O.Codf = F.Codf and O.Codp = P.Codp;
Exemplul 5 - o evidență detaliată a studenților: STUDENTI(Nrls,Nume,Spec,An) DISCIPLINE(Codd,Dend,Spec,An) SPECIALIZARI(Cods,Dens) NOTELE(Nrls,Codd,Nota) Această evidență presupune gestiunea a patru tabele. Primele trei tabele le numim tabele primare, deoarece datele primare se introduc în tabelele Studenti, Discipline, Specializari. Tabela Notele păstrează informații care presupun o corelare între celelalte trei tabele, deci nu putem introduce oricum datele de intrare. Presupunem că avem toate datele introduse în tabelele primare și dorim să pregătim tabela Notele pentru a putea introduce ulterior și notele de la examene. Folosim o instrucțiune Insert pentru a introduce în tabelă rezultatul unei selecții: Insert into Notele (Nrls, Codd) Select Nrls, Codd from Studenti, Discipline where Studenti.Spec = Discipline.Spec and Studenti.An = Discipline.An; Instrucțiunea Select de mai sus efectuează produsul cartezian al celor două tabele, și selectează acele înregistrări pentru care sunt îndeplinite condițiile din clauza where. Aceste condiții nu sunt de legătură, deoarece între atributele implicate nu există o legătură de tip cheie primară - cheie externă. După ce am introdus notele dorim o situație a mediilor, ordonată pe specializări, ani de studiu, medie (descrescător), nume (alfabetic): Select Notele.Nrls, Nume, Spec, Dens, An, avg(Nota) as Media from Notele, Studenti, Specializari where Notele.Nrls = Studenti.Nrls and Studenti.Spec = Cods group by Notele.Nrls, Nume, Spec, Dens, An order by 3,5,6,2;
Utilizarea instrucțiunii Select SQL în actualizare Am văzut mai sus cum se utilizează instrucțiunea Select SQL în conexiune cu instrucțiunea Insert. Putem cere în plus actualizarea (modificarea sau ștergerea) unor înregistrări care îndeplinesc anumite condiții, și care se exprimă cu ajutorul unor selecții. Un exemplu: în preajma unor sărbători (de Crăciun sau Paște) un furnizor (căruia îi știm numele, dar nu și codul) anunță reduceri de preț cu 15%. Aceste modificări se operează astfel: UPDATE Oferte SET Pretu = Pretu * 0.85 WHERE Codf = (SELECT Codf FROM Furnizori WHERE Nume = "..."); În locul punctelor de suspensie introducem numele furnizorului. În mod asemănător se poate folosi instrucțiunea Select SQL în conexiune cu instrucțiunea Delete.
Ion Cozac este lector la Universitatea "Petru Maior" din Târgu-Mureș și poate fi contactat prin e-mail la adresa cozac@uttgm.ro. Ion Cozac [cuprins] |