isa us6

128 Instrumente software pentru afaceri

Unitatea de studiu 6

INTEROGAREA BAZELOR DE DATE. LIMBAJUL SQL

CUPRINS 6.1. Prezentare general a limbajului SQL ....................................................................................... 129 6.2. Crearea tabelelor i declararea restriciilor ................................................................................ 132 6.3. Comenzi de actualizare a tabelelor ........................................................................................... 135 6.4. Structura de baz a frazei SELECT........................................................................................... 138 6.5. Jonciuni .................................................................................................................................. 141 6.6. Funcii-agregat: COUNT, SUM, AVG, MAX, MIN ................................................................. 143 6.7. Sub-consultri. Operatorul IN................................................................................................... 144 6.8. Reuniune, intersecie, diferen................................................................................................. 145 6.9. Gruparea tuplurilor. Clauzele GROUP BY i HAVING ............................................................ 145

Obiectivele unitii de studiu 6 Expunerea, n termeni generali, a problematicii standardizrii limbajelor de lucru cu bazele de

date. Punerea n eviden a importanei SQL n lumea bazelor de date Prezentarea principalelor tipuri de date ce pot fi gestionate prin SQL i a restriciilor ce pot fi

declarate. Expunerea gradual a principalelor clauze ale interogrilor

Competene dobndite:

Crearea unei baze de date relaionale, inclusiv a restriciilor folosind comenzi i clauze SQL:

CREATE TABLE, ALTER TABLE, DROP TABLE. Cunoaterea comenzilor pentru editarea unei tabele; comenzile INSERT, UPDATE, DELETE Cunoaterea celor mai importani operatori i clauze ai interogrilor SQL: fraza SELECT,

operatorii de comparaie obinuii i cei specifici: BETWEEN, IN, IS NULL, LIKE. Folosirea clauzelor GROUP BY i HAVING i a funciilor-agregat: COUNT, SUM, AVG,

MIN, MAX pentru calcularea unor parametri sintetici din baza de date.

Instrumente software pentru afaceri 129

6.1. PREZENTARE GENERAL A LIMBAJULUI SQL

Dup dou capitole de lupt surd cu bazele de date, a devenit aproape evident c folosim bazele de date pentru c avem memoria prea scurt. Deoarece suntem o lume aezat pe un morman de hrtii & hroage, ne este cu neputin s reconstituim ceea ce am fcut adineaori, dar-mi-te ieri, sptmna trecut sau acum un an sau cinci. Necazul e c, de cele mai multe ori, trebuie s tim nu numai ce-am fcut noi, dar ce-au fcut i colegii i partenerii de afaceri. Dac la oameni mai putem trece cu vederea, n cazul bazelor de date ncrederea este elementul cheie (primar, strin...). Aa nct i n capitolul 4 i n capitolul 5 am fost foarte interesai s aflm cu definim ct mai multe restricii, astfel s diminum riscul prelurii n baza de date a unor informaii eronate. Apoi am vzut cum inserm, modificm i tergem date ntr-o baz. Tangenial am abordat i cteva modaliti de a obine informaii de o manier asistat, grafic.

Chiar dac am ncercat s pstrm discuia la un nivel general, exemplificrile au fost fcute pe calapodul ACCESS-ului. Cei care au experien n ACCESS i vor s treac, forai de mprejurri, pe un SGBD mai performant, gen PostgreSQL, Oracle etc. (trecere care echivaleaz vor descoperi c trecerea de la amatori la semi sau chiar profesioniti de-a binelea) vor fi bruscai de diferenele de interfa, opiuni etc.

Pentru atenuarea ocurilor trecerilor de la un SGBD la altul, specialitii bazelor de date s-au gndit la un limbaj universal dedicat crerii tabelelor, definirii restriciilor, crerii utilizatorilor i grupurilor de utilizatori, definirii drepturilor fiecrui utilizator/grup la obiecte din baz, actualizrii nregistrrilor din tabele (inserare, modificare, tergere) i, mai ales, extragerii i prelucrrii datelor din baz. Acest limbaj a fost standardizat nc din 1986 (n SUA) i 1989 (la nivel mondial ISO) i se numete SQL.

Revenind la prima interogare n modul proiectare (Design) din capitolul 5 prin care ne propunem s aflm rspunsul la ntrebarea Care sunt crile publicate dup 2003 i intrate n bibliotec n 2009? (a se vedea figura 5.38), vom vizualiza codul aferent prin apelarea din meniul Design a grupului de butoane View, din care selectm opiunea SQL View (figura 6.1.)

Figura 6. 1. O interogare SQL de-a gata


SELECT CARTI.Nr_inv, CARTI.Autori, CARTI.Titlu, CARTI.Vol, CARTI.An_pub, CARTI.Pret, CARTI.Data_in FROM CARTI WHERE (((CARTI.An_pub)>=2003) AND ((Year([Data_in]))=2009));

Ei bine, acest SELECT este chiar o comand SQL. Dar s-o lum la pas temeinic. Un mobil determinant al succesului bazelor de date relaionale l-a constituit, fr nici o ndoial, SQL. De la nceputurile bazelor de date, s-a pus problema elaborrii unui limbaj universal special dedicat bazelor de date, limbaj care s permit, n egal msur, definirea relaiilor (tabelelor), declararea restriciilor, modificarea datelor din tabele, precum i extragerea informaiilor din cele mai diverse din datele existente n baz. n 1970, E.F.Codd sugera "adoptarea unui model relaional pentru organizarea datelor [...] care s permit punerea la punct a unui sub-limbaj universal pentru gestiunea acestora, sub-limbaj care s fie, n fapt, o form aplicat de calcul asupra predicatelor".

De atunci pn n prezent au fost propuse numeroase limbaje pentru lucrul cu bazele de date, denumite, n general, limbaje de interogare. Dintre cele teoretice, cele mai cunoscute sunt algebra relaional i calculul relaional, iar dintre cele practice (comerciale) Quel, QBE i SQL. De departe, cel mai important este SQL, fundamentat de algebra relaional.

Dup muli autori, momentul decisiv n naterea SQL ca limbaj l constituie lansarea proiectului System /R de ctre firma IBM, eveniment ce a avut loc n 1974. Tot n 1974 Chamberlin i Boyce au publicat o lucrare n care este prezentat forma unui limbaj structurat de interogare, "botezat" SEQUEL (Structured English as QUEry Language). n 1975 Chamberlin, Boyce, King i Hammer public un articol n care prezint sub-limbajul SQUARE, asemntor SEQUEL-ului, dar care utiliza expresii matematice i nu cuvinte din limba englez. Autorii celor dou studii au demonstrat c limbajele SEQUEL i SQUARE sunt complete din punct de vedere relaional.

Un colectiv de autori, condus de Chamberlin, elaboreaz n 1976 o nou lucrare n care se face referire la SEQUEL 2, acesta fiind "preluat" ca limbaj de interogare al SGBD-ului System /R al firmei IBM. n 1980 Chamberlin schimb denumirea SEQUEL n SQL - Structured Query Language (Limbaj Structurat de Interogare), dar i astzi muli specialiti pronun SQL ca pe predecesorul su. Anii urmtori au nregistrat apariia a o serie ntreag de lucrri care l-au perfecionat, ultimul deceniu consacrndu-l ca pe cel mai rspndit limbaj de interogare a BDR, fiind prezent n numeroase "dialecte" specifice tuturor SGBDR-urilor actuale, de la DB2 la Microsoft SQL Server, de la Oracle la FoxPro i ACCESS.

American National Standard Institute public n 1986 standardul SQL ANSI X3.136-1986. Este un standard care se bazeaz, ntr-o mare msur, pe "dialectul" SQL al produsului DB2 de la IBM. n 1989 are loc revizuirea extinderea acestui standard, "nscndu-se" SQL-89, care mai este denumit i SQL1. Dei recunoscut ca baz a multor SGBDR-uri comerciale, SQL1 i-a atras numeroase critici. n plus, variantele comercializate de diferiii productori, dei asemntoare n esen, erau (i sunt) incompatibile la nivel de detaliu. Pentru a umple golurile SQL1, ANSI a elaborat n 1992 "versiunea" SQL2, specificaiile fiind prezentate la un nivel mult mai detaliat (dac SQL1 se ntindea pe numai 100 de pagini, SQL2 a fost publicat n aproape 600). IBM a avut un aport incontestabil la apariia i maturizarea SQL, fiind un productor cu mare influen n "lumea" SGBD-urilor, iar produsul su, DB2, este unul din standardele de facto ale SQL.

Standardul SQL:1999 a fost amnat de cteva ori pn la publicarea sa, iar cea mai recent versiune este SQL:2003. Principalele orientri ale SQL vizeaza transformarea acestuia ntr-un limbaj complet, n vederea definirii si gestionarii obiectelor complexe si persistente. Aceasta include: generalizare si specializare, mosteniri multiple, polimorfism, ncapsulare, tipuri de date definite de utilizator, triggere (declansatoare) si proceduri stocate, suport pentru sisteme bazate pe gestiunea cunostintelor, expresii privind interogari recursive si instrumente adecvate de administrare a datelor.

La momentul actual, SQL reprezint cel mai important limbaj actual n domeniul bazelor de date, att prin gama comenzilor i opiunilor de care dispune, dar mai ales datorit faptului c s-a


reuit standardizarea sa i portarea pe toate Sistemele de Gestiune a Bazelor de date semnificative. Cu att mai mult, cu ct, spre deosebire de majoritatea covritoare a altor limbaje, poate fi deprins relativ uor de neinformaticieni i utilizat pentru chestiuni de mare finee de ctre profesioniti. Acest capitol se dorete a fi o prezentare a elementelor eseniale prin care, dat fiind structura unei baze de date relaionale, pot fi formulate interogri (fraze SELECT) prin care se obin rspunsuri la gam eterogen de ntrebri. n plus, sunt evocate pe scurt comenzile pentru actualizarea unei tabele (INSERT, UPDATE, DELETE), precum i cele de declarare a structurii bazei de date (CREATE TABLE).

Din punctul de vedere al utilizatorului final, obiectivul principal al SQL const n a oferi utilizatorului mijloacele necesare formulrii unei consultri numai prin descrierea rezultatului dorit, cu ajutorul unei aseriuni (expresie logic), fr a fi necesar i explicitarea modului efectiv n care se face cutarea n BD. Altfel spus, utilizatorul calific (specific) rezultatul iar sistemul se ocup de procedura de cutare.

Dei este referit, n primul rnd, ca un limbaj de interogare, SQL este mult mai mult dect un instrument de consultare a bazelor de date, deoarece permite, n egal msur: Definirea datelor Consultarea BD Manipularea datelor din baz Controlul accesului Partajarea bazei ntre mai muli utilizatori ai acesteia Meninerea integritii BD.

Desi toate clasificarile l ncadreaza la limbaje de generatia a IV-a, SQL nu este, totusi, un limbaj de programare propriu-zis, prin comparatie cu Basic, Pascal, C, COBOL etc. SQL nu contine (pna la SQL3) instructiuni/comenzi pentru codificarea secventelor alternative si repetitive, cu att mai putin facilitati de lucru cu obiecte vizuale, specifice formularelor de preluare a datelor (casute-text, butoane radio, liste, butoane de comanda etc.). Din acest punct de vedere, poate fi referit ca sub-limbaj orientat pe lucrul cu bazele de date. Comenzile sale pot fi, nsa, inserate n programe redactate n limbaje de programare "clasice".

Principalele atuuri ale SQL sunt: 1. Independena de productor, nefiind o tehnologie "proprietar". 2. Portabilitate ntre diferite sisteme de operare. 3. Este standardizat. 4. "Filosofia" sa se bazeaz pe modelul relaional de organizare a datelor. 5. Este un limbaj de nivel nalt, cu structur ce apropie de limba englez. 6. Furnizeaz rspunsuri la numeroase nterogri simple, ad-hoc, neprevzute iniial. 7. Constituie suportul programatic pentru accesul la BD. 8. Permite multiple imagini asupra datelor bazei. 9. Este un limbaj relaional complet. 10. Permite definirea dinamic a datelor, n sensul modificrii structurii bazei chiar n timp ce o parte

din utilizatori sunt conectai la BD. 11. Constituie un excelent suport pentru implementarea arhitecturilor client-server.

Principalele comenzi ale SQL, care se regsesc, ntr-o form sau alta, n multe dintre SGBDR-urile actuale sunt prezentate n tabelul 6.1.

Tabel 6.1. Clase de comenzi SQL

Comand Scop Pentru manipularea datelor

SELECT Extragerea datelor din BD INSERT Adugarea de noi linii ntr-o tabel DELETE tergerea de linii dintr-o tabel


UPDATE Modificarea valorilor unor atribute Pentru definirea bazei de date

CREATE TABLE Adugarea unei noi tabele n BD DROP TABLE tergerea unei tabele din baz ALTER TABLE Modificarea structurii unei tabele CREATE VIEW Crearea unei tabele virtuale DROP VIEW tergerea unei tabele virtuale

Pentru controlul accesului la BD GRANT Acordarea unor drepturi pentru utilizatori REVOKE Revocarea unor drepturi pentru utilizatori

Pentru controlul tranzaciilor COMMIT Marcheaz sfritul unei tranzacii ROLLBACK Abandoneaz tranzacia n curs

6.2. CREAREA TABELELOR I DECLARAREA RESTRICIILOR

Limbajul SQL prezint o serie ntreag de opiuni care permit crearea tabelelor i modificarea valorilor unor atribute pentru tuplurile unei relaii. Mai mult, standardul SQL dispune de optiuni clare privind specificarea unor restricii legate de cheile primare, cheile strine etc. Baza de date pe care o vom lua n discuie pe parcursul acestui capitol este CLIENI cu structura din figura 6.2.

Figura 6.2. Structura i relaiile dintre tabelelei bazei de date VINZARI


Pentru crearea unei tabele comanda SQL este, natural, CREATE TABLE prin care se declar

numele tabelei, numele, tipul i lungimea fiecrui atribut, precum i restriciile. Astfel, primele dou tabele din baza de date, CODPOST_LOC i CLIENI pot fi create astfel:

CREATE TABLE codPost_loc (

CodPostal CHAR(6) CONSTRAINT pk_cp PRIMARY KEY, Localitate CHAR (35) NOT NULL, Judet CHAR(25) NOT NULL ) ;

CREATE TABLE clienti ( CodClient INTEGER CONSTRAINT pk_cp PRIMARY KEY, NumeClient CHAR (30) NOT NULL CONSTRAINT un_numeclient UNIQUE, Adresa CHAR(60), CodPostal CHAR(6) NOT NULL CONSTRAINT ref_cl_codpost

REFERENCES codpost_loc (CodPostal) ) ;

Se pot observa cu uurin clauzele PRIMARY KEY folosite pentru declararea cheilor primare, UNIQUE pentru cheile alternative, NOT NULL pentru interzicerea valorilor nule, precum i REFERENCES pentru declararea cheii strine. n plus, opiunea CONSTRAINT ne ajut s botezm fiecare restricie dup cum dorim.

Spre deosebire de alte SGBD-uri, n ACCESS comenzile nu pot fi introduse direct, ci incluse n modul program. Iat modulul CREARETABELE(): Sub CreareTabele() Dim dbs As Database 'Set dbs = OpenDatabase("Z:\Medii_2006_ID\BD\vinzari.mdb") Set dbs = CurrentDb dbs.Execute "CREATE TABLE codPost_loc " _ & "(CodPostal CHAR(6) CONSTRAINT pk_cp PRIMARY KEY, " _ & " Localitate CHAR (35) NOT NULL, " _ & " Judet CHAR(25) NOT NULL) ; " dbs.Execute "CREATE TABLE clienti " _ & "(CodClient INTEGER CONSTRAINT pk_cp PRIMARY KEY, " _ & " NumeClient CHAR (30) NOT NULL CONSTRAINT un_numeclient UNIQUE, " _ & " Adresa CHAR(60), " _ & " CodPostal CHAR(6) NOT NULL CONSTRAINT ref_cl_codpost REFERENCES codpost_loc (CodPostal) ) ;" dbs.Execute "CREATE TABLE facturi " _ & "(NrFact INTEGER CONSTRAINT pk_facturi PRIMARY KEY, " _ & " CodClient INTEGER NOT NULL CONSTRAINT ref_fact_cl REFERENCES clienti (CodClient), " _ & " Data DATETIME NOT NULL, " _ & " ValoareTotala NUMERIC NOT NULL, " _ & " TVAColectata NUMERIC NOT NULL) ;" End Sub

Dei n capitolul 4 spuneam c structura unei baze de date este constant, exist situaii n care trebuie s:

- adugm un atribut; - eliminm un atribut; - schimbm tipul unui atribut; - modificm lungimea unui atribut; - s declarm o nou restricie - s anulm o restricie n vigoare.

Deoarece baza de date este n uz, nu ne putem permite s tergem i apoi s recrem tabelele, pentru c aceasta echivaleaz cu pierderea iremediabil a nregistrrilor existente. Aa c este necesar


folosirea comenzii ALTER TABLE. Dac n tabela CLIENI se dorete pstrarea i a codului fiscal al fiecrui furnizor, este necesar adugarea atributului CodFiscal, care este un ir de caractere (un numr precedat de litera R, dac clientul respectiv este pltitor de TVA) de lungime 10 caractere. Comanda utilizat este:

ALTER TABLE CLIENI ADD CodFiscal CHAR(10) n SQL tergerea unei tabele din baza de date este realizabil cu ajutorul comenzii DROP

TABLE. Iat cum ar putea lansate comenzile de tergere ale celor trei tabele pe care abia le-am creat acum jumtate de pagin: Sub StergeTabele() Dim dbs As Database Set dbs = OpenDatabase("Z:\ Medii_2006_ID\BD\vinzari.mdb") dbs.Execute "DROP TABLE facturi ;" dbs.Execute "DROP TABLE clienti ;" dbs.Execute "DROP TABLE codpost_loc ;" End Sub

Am trecut n revist pn n acest moment principalele clauze ale comenzii CREATE TABLE (i ALTER TABLE) pentru declararea cheilor primare, alternative i strine, i valorilor nenule. Nemeritat, a fost omis clauza CHECK prin care putem defini restricii utilizator sub forma regulilor de validare la nivel de atribut sau nregistrare. Astfel, n tabela CLIENI valorile atributului CodClient trebuie s nceap de la 1001, iar numele clientului se dorete a fi scris cu majuscule ntotdeauna. Aceste dou reguli de validare pot fi definite n SQL att n momentul crerii:

CREATE TABLE clienti ( CodClient INTEGER CONSTRAINT pk_cp PRIMARY KEY

CONSTRAINT ck_codclient CHECK (CocClient > 1000), NumeClient CHAR (30) NOT NULL CONSTRAINT un_numeclient UNIQUE

CONSTRAINT ck_numeclient CHECK (NumeClient = UPPER(NumeClient)), Adresa CHAR(60), CodPostal CHAR(6) NOT NULL CONSTRAINT ref_cl_codpost

REFERENCES codpost_loc (CodPostal) ) ;

ct i ulterior prin ALTER TABLE. Din pcate, ACCESSul nu este prea ngduitor n aceast privin, clauza CHECK fiind interzis. Singura modalitate de declarare a regulilor este cea procedural: Sub Reguli_Atribute() Dim dbs As Database, tdf As TableDef, fld As Field Set dbs = CurrentDb

' CLIENTI.CodClient > 1000 Set tdf = dbs.TableDefs("clienti") Set fld = tdf.Fields("CodClient") fld.ValidationRule = "[CodClient] > 1000" fld.ValidationText = "Cel mai mic cod de client acceptat este 1001 !"

' CLIENTI.NumeClient se scrie numai cu majuscule Set tdf = dbs.TableDefs("clienti") Set fld = tdf.Fields("NumeClient") fld.ValidationRule = "StrComp(UCase([NumeClient]), [NumeClient], 0) = 0" fld.ValidationText = "Literele din numele clientului sunt obligatoriu majuscule !" ' Prima litera din CLIENTI.Adresa este majuscula. Restul, la alegere ! Set tdf = dbs.TableDefs("clienti") Set fld = tdf.Fields("Adresa") fld.ValidationRule = "StrComp(LEFT(UCase([Adresa]),1), LEFT([Adresa],1), 0) = 0" fld.ValidationText = "Prima litera din adresa clientului trebuie sa fie majuscula !" 'Data facturii


Set tdf = dbs.TableDefs("facturi") Set fld = tdf.Fields("Data") fld.ValidationRule = "[Data] BETWEEN #1-1-2005# AND #12-31-2010# " fld.ValidationText = "Data facturii trebuie sa se incadreze in intervalul 1 ian.2005 - 31 dec.2010 !" End Sub

Despre reguli la nivel de nregistrare, ce s mai vorbim... Astfel, dac n tabela FACTURI s-ar dori instituirea regulii dup care TVA-ul poate fi cel mult egal cu 0,19/1,19 din valoarea total a fiecrei facturi (prietenii tiu de ce !), restricia ar putea fi definit la creare astfel: CREATE TABLE facturi (

NrFact INTEGER CONSTRAINT pk_facturi PRIMARY KEY, CodClient INTEGER NOT NULL CONSTRAINT ref_fact_cl REFERENCES clienti (CodClient), Data DATE NOT NULL, ValoareTotala NUMERIC NOT NULL, TVAColectata NUMERIC NOT NULL), CONSTRAINT ck_tva_valtot CHECK (TVAColectata


Coninutul celor trei tabele din finalul capitolului 4 a fost realizat n ACCESS prin modulul urmtor n care comanda INSERT este argumentul unei comenzi DoCmd.RunSQL: Sub Inserturi()

' CODPOST_LOC DoCmd.RunSQL ("INSERT INTO codPost_loc VALUES ('706600', 'Iasi', 'Iasi') ;") DoCmd.RunSQL ("INSERT INTO codPost_loc VALUES ('706610', 'Iasi', 'Iasi') ;") DoCmd.RunSQL ("INSERT INTO codPost_loc VALUES ('705300', 'Focsani', 'Vrancea') ;") DoCmd.RunSQL ("INSERT INTO codPost_loc VALUES ('705725', 'Pascani', 'Iasi') ;") DoCmd.RunSQL ("INSERT INTO codPost_loc VALUES ('706750', 'Tg.Frumos', 'Iasi') ;")

' CLIENTI DoCmd.RunSQL ("INSERT INTO clienti VALUES (1001, 'TEXTILA SA', 'Bld. Copou, 87', '706600' );") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1002, 'MODERN SRL', 'Bld. Grii, 22', '705300' ) ;") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1003, 'OCCO SRL', NULL, '706610') ;") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1004, 'FILATURA SA', 'Bld. Unirii, 145', '705300' ) ;") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1005, 'INTEGRATA SA', 'I.V.Viteazu, 115', '705725' ) ;") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1006, 'AMI SRL', 'Galatiului, 72', '706750' );") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1007, 'AXON SRL', 'Silvestru, 2', '706610' ) ;") DoCmd.RunSQL ("INSERT INTO clienti VALUES (1008, 'ALFA SRL', 'Prosperittii, 15', '705725' ) ;")

'FACTURI DoCmd.RunSQL ("INSERT INTO facturi VALUES (111111, 1003, #6-17-2005#, 17000, 0) ;") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111112, 1001, #6-17-2005#, 2850, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111113, 1004, #6-18-2005#, 5850, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111114, 1003, #6-18-2005#, 2850, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111115, 1008, #6-18-2005#, 35700, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111116, 1008, #6-19-2005#, 8700, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111117, 1006, #6-20-2005#, 1100, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111118, 1007, #6-23-2005#, 15000, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111119, 1005, #6-24-2005#, 4720, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111120, 1003, #6-24-2005#, 3000, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111121, 1001, #6-24-2005#, 4250, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111122, 1007, #6-24-2005#, 8750, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111123, 1006, #6-25-2005#, 66000, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111124, 1004, #6-25-2005#, 38600, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111125, 1003, #6-30-2005#, 1280, 0);") DoCmd.RunSQL ("INSERT INTO facturi VALUES (111126, 1002, #6-01-2005#, 54250, 0);")

End Sub

Dup cum se observ, deliberat TVA colectat este declarat zero la toate liniile inserate n FACTURI. Aceasta pentru a avea motiv de modificare (UPDATE) vezi paragraful pe peste linia curent.

6.3.2. Modificarea valorilor unor atribute

Pentru modificarea valorilor unuia sau multor atribute dintr-o tabel, comanda utilizat este UPDATE care are formatul general: UPDATE tabel SET atribut = expresie WHERE predicat

Ca rezultat, vor fi modificate valorile atributului specificat, noile valori ale acestuia fiind cele care rezult n urma evalurii expresiei; modificarea se va produce pe toate liniile tabelei care ndeplinesc condiia specificat n predicat. Astfel comanda: UPDATE facturi SET TVAColectata = INT(ValoareTotala * 19 / 1.19) / 100

va stabili valoarea TVA colectat pentru toate facturile (lipsete clauza WHERE, deci vor fi afectate toate liniile din tabela FACTURI) pe 0.19/1.19 din valoarea total a fiecrei facturi. Funcia INT


extrage doar partea ntreag dintr-un numr real (se elimin, deci, partea fracionar), iar prin mprirea rezultatului la 100 ne asigurm c TVA va avea dou poziii la partea fracionar. n ACCESS aceast actualizare se poate face n dou moduri. Grafic, putem creea o interogare de tip pe calapodul clasic al construirii machetelor (prezentat n paragraful 5.4), dup aducerea tabelei FACTURI n machet, selectnd din grupul Query Type a opiunii Update vezi partea stng a figurii 6.3. Partea dreapt a figurii prezint noua form a machetei, n care apare linia Update To i dispar Sort i Show. La rubrica Update To se introduce expresia de calcul a valorilor atributului TVAColectat. Cea de-a dou modalitate este cea procedural, comanda prin care se lanseaz UPDATE-rile fiind, ca i in cazul inserrii, DoCmd.RunSQL. Sub Updateuri() DoCmd.RunSQL ("UPDATE facturi SET TVAColectata = INT(ValoareTotala * 19 / 1.19) / 100 ;") DoCmd.RunSQL ("UPDATE facturi SET TVAColectata = 0 WHERE NrFact = 111117 ;") DoCmd.RunSQL ("UPDATE facturi SET TVAColectata = INT(ValoareTotala * 9 / 1.09) / 100 " & _ " WHERE NrFact IN (111118, 111122) ;") End Sub

Figura 6.3. Interogare de modificare

Modulul de mai sus, specific ACCESSului conine, pe lng comanda UPDATE explicat,

alte dou, prin care facturii 111117 i este declarat procentul zero de TVA, iar a doua indic faptul c toate produsele/serviciile din facturile 111118 i 111122 au procentul de TVA de 9%. Dei cei mai muli utilizatori ar opta pentru varianta grafic, va asigurm c varianta procedural este mai productiv atunci cnd sunt necesare actualizri repetate, aa cum este cazul de mai sus.

6.3.3. tergeri

Operaiunea de eliminarea a una sau mai multe linii dintr-o tabel, pe baza unui predicat, se realizeaz n SQL prin comanda DELETE care are sintaxa: DELETE FROM nume-tabel WHERE predicat

Dac am dori s eliminm din tabela CLIENI linia aferent clientului MODERN SRL (cod 1002), comanda ar fi: DELETE FROM clienti WHERE CodClient = 1002


n ACCESS, similar interogrilor pentru modificare pot fi create i interogri pentru tergere, n care se specific criteriul pe care trebuie s-l satisfac liniile pentru a fi terse din tabela indicat (fiind n criz de spaiu, nu vom mai prezenta nici o figur n acest scop). Prin program, dac am dori eliminarea tuturor nregistrrilor din tabelele bazei, apoi re-adugarea i re-modificarea lor, ne-am putea servi de blocul urmtor:

Sub Stergeri()

DoCmd.RunSQL ("DELETE FROM facturi ;") DoCmd.RunSQL ("DELETE FROM clienti ;") DoCmd.RunSQL ("DELETE FROM codPost_loc ;") Call Inserturi Call Updateuri

End Sub

6.4. STRUCTURA DE BAZ A FRAZEI SELECT

Fr ndoial, cea mai gustat parte din SQL este cea legat de interogarea bazei, adic de obinerea de informaii din cele mai diverse, prin prelucrri, grupri etc. Ceea ce a fost prezentat n paragraful 5.4 este doar o prticic din ceea ce poate fi stors dintr-o baz de date. Aadar, n SQL o interogare se formuleaz printr-o fraz SELECT. Aceasta prezint trei clauze principale: SELECT, FROM i WHERE.

SELECT este utilizat pentru desemnarea listei de atribute (coloanele) din rezultat; FROM este cea care permite enumerarea relaiilor din care vor fi extrase informaiile aferente

consultrii; prin WHERE se desemneaz condiia (predicatul), simpl sau complex, pe care trebuie s le

ndeplineasc liniile tabelelor (enumerate n clauza FROM) pentru a fi extrase n rezultat. La modul general (si simplist) o consultare simpl n SQL poate fi prezentat astfel: SELECT

C1, C2, ..., Cn FROM R1, R2, ..., Rm WHERE P. Execuia unei fraze SELECT se concretizeaz n obinerea unui rezultat de forma unei tabele

(relaii). Cnd clauza WHERE este omis, se consider implicit c predicatul P are valoarea logic "adevrat". Dac n locul coloanelor C1, C2, ... Cn apare simbolul "*", n tabela-rezultat vor fi incluse toate coloanele (atributele) din toate relaiile specificate n clauza FROM. De asemenea, n tabela-rezultat, nu este obligatoriu ca atributele s prezinte nume identic cu cel din tabela enumerat n clauza FROM. Schimbarea numelui se realizeaz prin opiunea AS.

Uneori, rezultatul interogrii "ncalca" poruncile modelului relational. Conform restrictiei de entitate, ntr-o relatie nu pot exista doua linii identice. Or, n SQL, rezultatul unei consultri poate contine doua sau mai multe tupluri identice. Pentru eliminarea liniilor identice este necesar utilizarea opiunii DISTINCT: SELECT DISTINCT C1, C2, ..., Cn FROM R1, R2, ..., Rm WHERE P

Am nceput acest capitol prin a arta fraza SELECT care se ascunde n spatele unei consultri grafice ACCESS. Fraza: SELECT facturi.NrFact, facturi.Data, [ValoareTotala]-[TVAColectata] AS ValFaraTVA,

facturi.TVAColectata, facturi.ValoareTotala FROM facturi WHERE (((facturi.Data)>#6/20/2005#));

conine i un cmp calculat ValFrTVA, al crui nume se specific prin clauza AS, i ale crui valori se determin prin relaia ValoareTotal TVAColectat. Predicatul de selecie (clauza WHERE) asigur extragerea n rezultat doar a facturilor emise dup 20 iunie 2005.

Cum se poate introduce o interogare n ACCESS fr a o desena cu proiectantul de machete

pentru interogri prezentat n paragraful 5.4 ? Mai nti crem o interogare foarte simpl s-i zicem


INTEROG. Dup modelul indicat n stnga figurii 6.3 i vizualizm definiia folosind opiunea SQL View, iar apoi n fereastra care apare nlocuim fraza SELECT cu cea care ne intereseaz (fraz care nu are, probabil, nici o legtur cu definiia actual a interogrii), dup care se apas butonul Run (semnul mirrii).

Iar dac vrem s facem acelai lucru prin program, folosim modulul urmtor: Sub interogareBETWEEN() Dim consultare As QueryDef Set consultare = CurrentDb.QueryDefs("interog") consultare.SQL = "SELECT * FROM facturi WHERE nrfact BETWEEN 111120 AND 111124 ; " DoCmd.OpenQuery ("interog") End Sub

Modulul interogareBETWEEN() declar (prin Dim) obiectul consultare ca fiind definiia unei interogri (QueryDef), preia (prin comanda Set) n consultare fraza SELECT care constituie definiia interogrii create anterior interog , modific definiia acestea prin linia consultare.SQL = SELECT i, n final, execut noua variant a interogrii, rezultatul fiind similar variantei din figura 6.4.

Figura 6.4. Schimbarea definiiei unei interogri i re-execuia sa

Firete, noile definiii pot fi salvate sau se poate renuna la ele, mai ales atunci lucrm cu module ce pot apelate ori de cte ori este nevoie.

Operatorul BETWEEN Fraza SELECT de mai sus conine operatorul BETWEEN care va extrage din tabel numai

facturile cu numerele cuprinse ntre 111120 i 111124. Fr BETWEEN predicatul ar trebuit scris WHERE nrfact >= 111120 AND nrfact


MITA, MATSUSHITA etc.). Despre semnul "%" (sau *) se spune c este un specificator multiplu, joker sau masc. Un alt specificator multiplu utilizat n multe versiuni SQL este liniua-de-subliniere ("_") sau semnul de ntrebare (?). Spre deosebire de "%", "_" substituie un singur caracter. Diferena dintre cei doi specificatori multipli este pus n eviden n continuare. Astfel, dac intereseaz care sunt clienii ai cror nume ncepe cu litera A i sunt societi cu rspundere limitat (SRL-uri), fraza SELECT care furnizeaz rspunsul este:

SELECT * FROM CLIENI WHERE NumeClient LIKE "A__ SRL%" (vezi partea stng a figurii 6.4). Dac s-ar fi utilizat simbolul "%" de maniera urmtoare:

SELECT * FROM CLIENI WHERE NumeClient LIKE "A%SRL%"

rezultatul ar fi fost cel din partea dreapt a figurii.

Figura 6.5. Folosirea specificatorilor multipli

n concluzie, "_" sau ? nlocuiesc (substituie) un singur caracter, n timp ce "%" sau * nlocuiesc un ir de caractere de lungime variabil (ntre 0 i n caractere). Cei doi specificatori multipli pot fi utilizai mpreun.

Operatorul IN Un alt operator util este IN cu formatul general: expresie1 IN (expresie2, expresie3, ...).

Rezultatul evalurii unui predicat ce conine acest operator va fi "adevrat" dac valoarea expresiei1 este egal cu (cel puin) una din valorile: expresie2, expresie3, ...

Spre exemplu, pentru a afla care sunt clienii din localitile din judeele Iai i Vaslui, fr utilizarea operatorului IN se scrie: SELECT * FROM codpost_loc WHERE Judet = 'Iasi' OR Judet = 'Vaslui'

Iar utiliznd IN: SELECT * FROM codpost_loc WHERE Judet IN ("Iasi", "Vaslui")

Operatorul IS NULL O valoare nul este o valoare nedefinit. Este posibil ca la adugarea unei linii ntr-o tabel,

valorile unor atribute s fie necunoscute. n aceste cazuri valoarea atributului pentru tuplul respectiv este nul. Reamintim c, prin definiie, nu se admit valori nule pentru grupul atributelor care constituie cheia primar a relaiei. Pentru aflarea clienilor pentru care nu s-a introdus adresa, se poate scrie: SELECT * FROM clienti WHERE Adresa IS NULL

Cum n baza noastr de date, numai clientului OCCO SRL nu-i cunoatem adresa, rezultatul interogrii este cel din figura 6.6 (n ACCESS valorile NULL se afieaz ca i cum ar conine spaii).

Figura 6.6. Extragerea valorilor NULLe

Observaii Valoarea NULL nu se confund cu valoarea zero (pentru atributele numerice) sau cu valoarea

"spaiu" (pentru atributele de tip ir de caractere) Operatorul NULL se utilizeaz cu IS i nu cu semnul "=". Dac s-ar utiliza expresia = NULL i nu

expresia IS NULL, rezultatul evalurii va fi ntotdeauna fals, chiar dac expresia nu este nul !


Opiunile DISTINCT i ORDER BY Dorim s aflm judeele n care firma are clieni. Este necesar parcurgerea relaiei

CODPOST_LOC, singurul atribut care intereseaz fiind Jude: SELECT DISTINCT Judet FROM codpost_loc

Dup cum se observ n partea stng a figurii 6.7, SQL nu elimin dublurile automat, iar dac se dorete ca n tabela-rezultat o localitate s figureze o singur dat, se utilizeaz opiunea DISTINCT (rezultatul n figura 6.8):

SELECT DISTINCT Judet FROM codpost_loc

Figura 6.7. Fr i cu DISTINCT Figura 6.8. Clauza ORDER BY

n continuare vrem s obinem denumirea fiecrei localiti i judeul n care se afl, dar liniile

rezultatului trebuie ordonate n funcie de jude i, n cadrul aceluiai jude, n ordinea invers a localitii (de la Z la A), fraza SELECT se formuleaz dup cum urmeaz, rezultatul fiind prezentat n figura 6.7. SELECT DISTINCT Localitate, Judet FROM codpost_loc ORDER BY Judet ASC, Localitate DESC

Opiunile ASCENDING (cresctor) i DESCENDING (descresctor) indic deci modul n care se face ordonarea tuplurilor tabelei-rezultat al interogrii. Prioritatea de ordonare este stabilit prin ordinea atributelor specificate n ORDER BY: ordonarea "principal" se face n funcie de valorile primului atribut specificat; n cadrul grupelor de tupluri pentru care valoarea primului atribut este identic, ordinea se stabilete dup valoarea celui de-al doilea atribut specificat .a.m.d. Dac n ORDER BY lipsesc opiunile ASCENDING/DESCENDING, ordonarea se face cresctor.

6.5. JONCIUNI

Dup cum afirmam i n paragraful 5.4 majoritatea informaiilor obinute dintr-o baz de date necesit rsfoirea simultan a dou sau mai multe tabele. Interogarea Fac_dupa20iunie2005v2 din figura 5.41 folosete trei tabele. Folosind opiunea SQLView obinem o fraz SELECT cu totul remarcabil: SELECT facturi.NrFact, facturi.Data, clienti.NumeClient, codPost_loc.Localitate,

[ValoareTotala]-[TVAColectata] AS Expr1, facturi.TVAColectata, facturi.ValoareTotala, * FROM (codPost_loc INNER JOIN clienti ON codPost_loc.CodPostal = clienti.CodPostal)

INNER JOIN facturi ON clienti.CodClient = facturi.CodClient WHERE (((facturi.NrFact)>#6/20/2005#) AND ((codPost_loc.Localitate)="Iasi"));

Clauza FROM vine acum n centrul ateniei prin apariia clauzei INNER JOIN. Iat cum stau lucrurile: deoarece n lista pe care vrem s obinem se gsesc atribute plasate n cele trei tabele, n clauza FROM trebuie enumerate cele trei numere; n fapt, dup cum am vzut n capitolul 4, cele trei tabele prin legate prin restricii refereniale, atributele de legtura fiind cheile strine cheile primare. Astfel, legtura dintre FACTURI i CLIENI se poate realiza prin intermediul atributului CodClient care este cheie primar n CLIENI (tabela printe) i cheie strin n FACTURI (tabela copil).


Legtura dintre aceste dou tabele care prezint un cmp comun se numete jonciune i se simbolizeaz n SQL prin INNER JOIN: SELECT * FROM facturi INNER JOIN clienti ON facturi.CodClient=clienti.CodClient

Fr a intra n prea multe detalii teoretice, reinem c, ori de cte ori informaiile necesare i condiiile pe care trebuie s le ndeplineasc acele informaii privesc atribute aflate n tabele diferite, trebuie fcut jonciunea acestor tabele. Cnd tabele nu pot fi joncionate direct, trebuie aduse cu fora n clauza FROM i tabelele care s completeze lanul.

Ne intereseaz, spre exemplu, numrul i data facturilor emise clienilor din judeul Iai. Numrul i data facturilor se gsesc n tabela FACTURI (atributele NrFact i Data), ns pentru denumirea judeului exist un atribut (Judet) n tabela CODPOST_LOC. Cele dou tabele nu pot fi joncionate direct, aa nct atragem n interogare i cea de-a treia tabel CLIENI: SELECT NrFact, Data FROM (facturi INNER JOIN clienti ON facturi.codclient=clienti.codclient) INNER JOIN codpost_loc ON codpost_loc.codpostal=clienti.codpostal WHERE judet=Iasi ORDER BY NrFact

Scris sub form de modul ACCESS InterogareJONCTIUNE1() i lansat prin apsarea butonului Run, fraza SELECT obine rezultatul este cel din figura 6.9.

Figura 6.9. Un modul ACCESS cu fraz SELECT ce joncioneaz cele trei tabele (plus rezultatul)

Lsam s se neleag, la un moment dat, c n SQL pot fi formulate interogri mult mai complexe dect se poate realiza cu ajutorul machetei din paragraful 5.4. Haidei s lum o asemenea problem, ce-i drept nu att de complicat precum ameninam: Care sunt facturile emise n aceeai zi ca i factura 111113 ? Dificultatea ine de faptul c cerina este formulat indirect, adic vrem s aflm facturile emise ntr-o zi (Data), dar noi nu tim data etalon, ci factura-etalon.

Problema propus poate fi rezolvat relativ uor folosind o subconsultare, dup cum va fi prezentat ntr-un paragraf viitor. Pn una-alta, soluia pe care o avem n acest moment la ndemn se bazeaz pe autojonciune. Autojonciunea nseamn jonciunea unei tabele (FACTURI) cu ea-nsi, practic, jonciunea a dou instane ale unei aceleai tabele. Pentru a jonciune cele dou instane trebuie s aib pseudonime (aliasuri) diferite, n cazul nostru F1 i F2. ntruct ne intereseaz facturi emise n aceeai zi cu 111113, autojonciunea se face dup atributul Data: SELECT * FROM facturi F1 INNER JOIN facturi F2 ON F1.data=F2.data WHERE F2.NrFact = 111113


Iat rezultatul vezi figura 6.10. Rezultatul conine 10 coloane, cinci din prima instan a tabelei FACTURI (F1) i cinci din a doua instan (F2). ACCESSul e destul de inspirat s scrie naintea fiecrui atribut din ce instan provine.

Figura 6.10. Facturile emise n aceeai zi ca i 111113

Toate liniile rezultatului respect condiia de jonciune - F1.data=F2.data. Jumtatea din dreapta a coloanelor se refer strict la factura etalon 111113, iar cea din stnga la facturile din aceeai zi cu 111113, inclusiv factura-etalon. Pentru a rspunde punctual la problem, precizm atributele (coloanele) care ne intereseaz i eliminm din rezultat factura 111113: SELECT F1.NrFact, F1.Data FROM facturi F1 INNER JOIN facturi F2 ON F1.data=F2.data WHERE F2.NrFact = 111113 AND F1.NrFact 111113

6.6. FUNCII-AGREGAT: COUNT, SUM, AVG, MAX, MIN

Cu funciile agregat facem un prim pas spre analiza datelor din baz. Li se spune funcii agregat deoarece, n absena gruprii (clauza GROUP BY vezi ultimul paragraf din acest capitol) rezultatul unei asemenea funcii are forma unei tabele cu o singur linie.

Funcia COUNT Contorizeaz valorile unei coloane, altfel spus, numr, ntr-o relaie, cte valori diferite de

NULL are coloana specificat. Dac n locul unui atribut apare semnul asterisc (*) se numr liniile rezultatului. Astfel, dac vrem s aflm ci clieni are firma vom folosi interogarea (rezultatul su se afl in stnga figurii 6.11). SELECT COUNT (CodClient) AS Nr_Clienti1, COUNT (*) AS Nr_Clienti2 FROM clienti

Figura 6.11. Nite COUNT-uri

Teoretic, n tabela CLIENI pot aprea i clieni crora nc nu li s-a trimis nc nici o factur. Dac vrem s aflm rspunsul la ntrebarea: La ci clieni s-au trimis facturi ?, am fi tentai s folosim interogarea: SELECT COUNT () AS NrClienti1, COUNT(CodClient) AS NrClienti2 FROM clienti INNER JOIN facturi ON clienti.CodClient=facturi.CodClient

care, ns, ne va furniza un rspuns eronat (vezi partea dreapt a figurii 6.10). n produsele care respect recomandrile standardelor SQL, rezultatul corect poate fi ns obinut prin utilizarea clauzei DISTINCT astfel: SELECT COUNT (DISTINCT CodClient) FROM facturi

n ACCESS aceast opiune nu este acceptat, aa nct mai ateptm pn la paragraful dedicat subconsultrilor.

Funcia SUM Funcia SUM calculeaz suma valorilor unei coloane. Pentru a afla suma valorilor totale ale

facturilor, soluia este ct se poate de simpl: SELECT SUM (ValoareTotala) AS Total_ValoriFacturi FROM facturi

iar totalul valorilor pentru facturile trimise clientului AXON SRL este obinut astfel:


SELECT SUM (ValoareTotala) AS Total_Fact_AXON FROM facturi INNER JOIN clienti ON facturi.CodClient = clienti.CodClient WHERE NumeClient = AXON SRL

Funciile MAX i MIN Determin valorile maxime, respectiv minime ale unei coloane n cadrul unei tabele. Valorile

cea mai mic i cea mai mare ale unei facturi se afl astfel: SELECT MIN(ValoareTotala), MAX(ValoareTotala) FROM facturi

Atenie ! La ntrebarea Care este factura emis cu cea mai mare valoare ? nu putem rspunde deocamdat. Varianta urmtoare nu este corect: SELECT NrFactura, MAX(ValoareTotala ) FROM facturi

La execuia acestei interogri se afieaz un mesaj de eroare, soluia problemei fiind posibil ceva mai pe finalul capitolului.

6.7. SUB-CONSULTRI. OPERATORUL IN

O alt facilitate deosebit de important a limbajului SQL o constituie posibilitatea includerii (imbricrii) a dou sau mai multe fraze SELECT, astfel nct pot fi formulate interogri cu mare grad de complexitate. Operatorul cel mai des ntrebuinat este IN. Astfel, revenind la o problem anterioar - Care sunt facturile emise n aceeai zi n care a fost ntocmit factura 111113 ? n locul epuizantei auto-jonciuni putem recurge la subconsultare: SELECT * FROM facturi WHERE NrFact 111113 AND Data IN (SELECT Data FROM facturi WHERE NrFact=111113)

Sub-consultarea SELECT Data FROM facturi WHERE NrFact = 111113 are ca rezultat o tabel alctuit dintr-o singur coloan (Data) i o singur linie ce conine valoarea atributului Data pentru factura 111113, ca n stnga figurii 6.12. Clauza WHERE Data IN determin cutarea n tabela FACTURI a tuturor tuplurilor (liniilor) care au valoarea atributului Data egal cu una din valorile tuplurilor (n cazul nostru, egal cu valoarea tuplului) din tabela obinut prin "sub-consultare" (n cazul nostru, tabela din stnga figurii). Cu alte cuvinte, n acest caz WHERE Data IN va selecta toate facturile pentru care data emiterii este 18/06/2005 partea dreapt a figurii 6.12.

Figura 6.12 Rezultatul sub-consultrii (stnga) i al interogrii

Dac s-ar schimba condiia de selecie, n sensul c ne-ar interesa facturile emise n alte zile dect cea n care a fost ntocmit factura 111113 operatorul de sub-consultare va fi NOT IN: SELECT * FROM facturi WHERE Data NOT IN (SELECT Data FROM facturi WHERE NrFact = 111113)

Ca s ncheiem paragraful cu o interogare mai prezentabil, ne intereseaz clienii crora li s-au trimis facturi ntocmite n aceeai zi cu factura 111113: SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN (SELECT CodClient FROM facturi WHERE Data IN (SELECT Data FROM facturi WHERE NrFact = 111113))

Am ilustrat modul n care pot fi imbricate (nlnuite, incluse) trei fraze SELECT.


6.8. REUNIUNE, INTERSECIE, DIFEREN

Operatorul pentru reuniune este deci UNION. De remarcat c, la reuniune, SQL elimin automat dublurile, deci nu este necesar utilizarea clauzei DISTINCT. Operatorul UNION este prezent n toate SGBD-urile importante. Dac dou relaii, R1 i R2 sunt uni-compatibile, adic au acelai numr de atribute care corespund sintactic (adic primul atribut din R1 este de acelai tip cu primul atribut din R2), se poate scrie SELECT * FROM R1 UNION SELECT * FROM R2.

n ceea ce ne privete, vrem s aflm cum se numesc clienii crora le-am emis facturi pe 23 sau pe 24 iunie 2005, avem la dispoziie dou variante, una bazat pe operatorul logic OR: SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN

(SELECT DISTINCT CodClient FROM facturi WHERE Data = #6/23/2005# OR Data = #6/24/2005#)

i o alta bazat pe reuniune: SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN

(SELECT DISTINCT CodClient FROM facturi WHERE Data = #6/23/2005# ) UNION SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN

(SELECT DISTINCT CodClient FROM facturi WHERE Data = #6/24/2005#)

Pentru realizarea interseciei a dou tabele unicompatibile, R1 i R2, n standardele SQL a fost introdus operatorul INTERSECT: SELECT * FROM R1 INTERSECT SELECT * FROM R2. Dac n produsele profesionale, precum DB2 (IBM) sau Oracle operatorul este prezent, n schimb multe din cele din categoria uoar, precum Visual Fox Pro i ACCESS INTERSECT rmne un deziderat, funcionalitatea sa realizndu-se prin subconsultri (operatorul IN) sau, uneori, prin jonciune. Astfel, dac dorim s aflm cum se numesc clienii crora le-am emis facturi i pe 23 i pe 24 iunie 2005, soluia cea mai la ndemn n ACCESS este: SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN

(SELECT DISTINCT CodClient FROM facturi WHERE Data = #6/23/2005# ) AND CodClient IN


Diferena dintre tabelele R1 i R2 (unicompatibile) se realizeaz utiliznd operatorul MINUS sau EXCEPT, ns implementrile sunt similare operatorului INTERSECT. Astfel, pentru a obine clienii crora le-am emis facturi i pe 24, dar nu i pe 24 iunie 2005, soluia ACCESS este: SELECT DISTINCT NumeClient FROM clienti WHERE CodClient IN

(SELECT DISTINCT CodClient FROM facturi WHERE Data = #6/24/2005# ) AND CodClient NOT IN


6.9. GRUPAREA TUPLURILOR. CLAUZELE GROUP BY I HAVING

n paragraful 5.4 fceam cunotin cu o prim interogare n care era necesar gruparea liniilor. Ne interesez valoarea zilnic a vnzrilor ntr-o anumit perioad, iar cadrul construirii interogrii (VinzariPeZile_interval_la_alegere). Folosind din nou opiunea SQL View s vedem fraza SELECT ce se ascunde n spatele machetei: SELECT facturi.Data, Sum(facturi.TVAColectata) AS SumOfTVAColectata,

Sum(facturi.ValoareTotala) AS SumOfValoareTotala, Sum([ValoareTotala]-[TVAColectata]) AS ValFaraTVA

FROM facturi GROUP BY facturi.Data


HAVING (((facturi.Data) Between [Data initiala:] And [Data finala:])) ORDER BY facturi.Data;

SQL permite utilizarea clauzei GROUP BY pentru a forma grupe (grupuri) de tupluri ale unei relatii, pe baza valorilor comune ale unei coloane. n frazele SELECT formulate pn n acest paragraf, prin intermediul clauzei WHERE au fost selectate tupluri din diferite tabele. Prin asocierea unei clauze HAVING la o clauz GROUP BY este posibil selectarea anumitor grupe de tupluri ce ndeplinesc un criteriu.

Clauza GROUP BY Rezultatul unei fraze SELECT ce conine aceast clauz este o tabel care va fi obinut prin

regruparea tuturor liniilor din tabelele enumerate n FROM, care prezint o aceeai valoare pentru o coloan sau un grup de coloane. Formatul general este: SELECT coloan 1, coloan 2, ...., coloan m FROM tabel GROUP BY coloan-de-regrupare

Simplificm problema, dorind o list cu are este totalul zilnic al valorii facturilor emise. Fraza este ceva mai simpl: SELECT Data, SUM (ValoareTotala) AS Total_Zilnic FROM facturi GROUP BY Data

Tabela-rezultat va avea un numr de linii egal cu numrul de date calendaristice distincte din tabela FACTURI. Pentru toate facturile aferente unei zile se va calcula suma valorilor, datorit utilizrii funciei SUM(ValoareTotala). Succesiunea pasilor este urmatoarea: 1. Se ordoneaza liniile tabelei FACTURI n functie de valoarea atributului Data - figura 6.13.

Figura 6.13. Pasul 1 al gruprii Figura 6.14. Al doilea pas al gruprii

2. Se formeaza cte un grup pentru fiecare valoare distincta a atributului Data - vezi figura 6.14. 3. Pentru fiecare din cele noua grupuri se calculeaza suma valorilor atributului ValoareTotala. Tabela rezultat va avea noua linii, ca n figura 6.15.

Figura 6.15. Rezultatul final al gruprii Figura 6.16. Zilele cu vnzri mai mari de 40000 RON

Dac intereseaz este numrul facturilor emise pentru fiecare client, rspunsul poate fi obinut prin interogarea: SELECT NumeClient, COUNT(NrFact) AS NrFacturi_pe_Client FROM facturi INNER JOIN clienti ON facturi.CodClient = clienti.CodClient GROUP BY NumeClient


Pn la standardul SQL:1999 i publicarea Amendamentului OLAP la acest standard, n SQL nu puteau fi calculate, prin GROUP BY, subtotaluri pe mai multe niveluri. Pentru aceasta este necesar scrierea de programe n SGBD-ul respectiv.

Clauza HAVING Clauza HAVING permite introducerea unor restricii care sunt aplicate grupurilor de tupluri,

deci nu tuplurilor "individuale", aa cum "face" clauza WHERE. Din tabela rezultat sunt eliminate toate grupurile care nu satisfac condiia specificat. Clauza HAVING "lucreaz" mpreun cu o clauz GROUP BY, fiind practic o clauz WHERE aplicat acesteia. Formatul general este: SELECT coloan 1, coloan 2, .... , coloan m FROM tabel GROUP BY coloan-de-regrupare HAVING caracteristic-de-grup

Pentru facturile emise intereseaza valoarea zilnica a acestora (n functie de data la care au fost ntocmite), dar numai daca aceasta (valoarea zilnica) este de mai mare de 40000 lei noi (RON). SELECT Data, SUM(ValoareTotala) AS Vinzari_Zilnice FROM facturi GROUP BY Data HAVING SUM(ValoareTotala) > 40000

La executia acestei fraze, se parcurg cei trei pasi descrii la nceputul acestui paragraf, apoi, dintre cele noua tupluri obtinute prin grupare, sunt extrase numai cele care ndeplinesc conditia SUM(ValoareTotala) > 40000. Rezultatul final este cel din figura 6.16.

i acum, o interogare cu adevrat interesant: S se afieze ziua n care s-au ntocmit cele mai multe facturi ! Iat soluia: SELECT Data, COUNT(*) AS nr_facturilor FROM facturi GROUP BY Data HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM facturi GROUP BY Data)

Figura 6.17. Clauza HAVING cu folosirea unei subconsultri

Avem de-a face cu o subconsultare al crei rezultat (stnga figurii 6.16) servete drept termen de comparaie al predicatului (condiiei) formulat n clauza HAVING.

Cam att pentru acest capitol, disciplin, semestru (informatic) i an (tot informatic) ! V mulumim pentru rbdare, i v asigurm c cine a ajuns cu cititul (i nelesul) pn n acest punct, are toate ansele unui examen ncununat de succes.

Test gril pentru verificarea cunotinelor din Unitatea de studiu 6 1. Limbajul SQL permite: a) [x] crearea tabelelor b) [x] definirea restriciilor c) [x] actualizarea datelor din tabele d) [x] extragerea i prelucrarea datelor din BD e) [ ] crearea rapoartelor f) [ ] folosirea formularelor 2. Care din urmtoarele enunuri sunt adevrate?


a) [x] SQL nu permite codificarea structurilor de control alternative i repetitive b) [x] SQL este un sub-limbaj orientat pe lucrul cu baze de date c) [ ] comenzile SQL nu pot fi inserate n programe scrise cu limbaje de programare clasice d) [ ] SQL ofer faciliti de lucru cu obiecte vizuale 3. Se d urmtoarea fraz SQL: SELECT NumeClient, SUM(ValoareTotala) As TotalValTotala FROM FACTURI, CLIENTI WHERE CLIENTI.CodClient=FACTURI.CodClient AND NumeClient = "Alfa SRL" GROUP BY NumeClient Rezultatul acestei fraze va fi: a) [x] Valoarea totala a facturilor emise clientului Alfa SRL b) [ ] Valoarea fr TVA a facturilor emise clientului Alfa SRL c) [ ] Valoarea total a facturilor emise, pentru fiecare client d) [ ] Valoarea total a facturilor emise ctre clienii cu acelai grup de facturi ca i Alfa SRL 4. Se d urmtoarea fraz SQL: SELECT NumeClient, SUM(ValoareTotala) As TotalValTotala FROM FACTURI, CLIENTI WHERE CLIENTI.CodClient=FACTURI.CodClient GROUP BY NumeClient HAVING COUNT(NrFactura)>5 Rezultatul acestei fraze va conine: a) [ ] Valoarea facturilor emise pentru clienii cu mai mult de cinci facturi pe zi b) [ ] Valoarea facturilor emise, pentru fiecare client mai mare ca 5 c) [ ] Valoarea facturilor emise d) [x] Valoarea facturilor emise, pentru clienii crora li s-au trimis mult de cinci facturi pe zi 5. Se d urmtoarea fraz SQL: SELECT Data, SUM(ValoareTotala) As TotalValTotala FROM FACTURI, ABONATI WHERE CLIENTI.CodClient=FACTURI.CodClient GROUP BY Data HAVING SUM(ValoareTotala)>5000 Rezultatul acestei fraze va conine: a) [ ] Valoarea zilnic a facturilor emise b) [ ] Totalul facturilor emise, pentru clienii cu vnzri mai mari de 5000 c) [ ] Totalul facturilor emise, pe fiecare zi n care acesta depete 5000 d) [x] Fraza SELECT este greit 6. Ce se obine prin urmtoarea interogare SQL? SELECT NrFactura FROM CLIENTI, FACTURI WHERE CLIENTI.CodClient=FACTURI.CodClient AND ValTot n (5000,10000) a) [ ] numai facturile emise cu valoarea ntre 5000 i 10000 (lei) b) [x] numai facturile emise cu valoarea de 5000 sau 10000 (lei) c) [ ] numai facturile emise cu valoarea fie ntre 5000 i 10000 (lei), fie ntre 10000 i 5000 (lei) Fiecare ntrebare, cu rspunsurile corecte, are un punct. Punctajul minim pentru promovarea testului este de 3 puncte.

Bibliografie selectiv

1. Airinei, D. a., Instrumente software pentru afaceri. Aplicaii practice, Editura Sedcom

Libris, Iai, 2010 2. Fotache, M.,SQL Dialecte DB2, Oracle, Visual FoxPro, Ed. Polirom, Iai, 2001 3. Fotache, M., Proiectarea bazelor de date, Ed. Polirom, Iai, 2005


4. Grama, A., Fotache, M., ugui, A., Instrumente software pentru afaceri. Lucrri practice i ntrebri gril, Editura Sedcom Libris, Iai, 2009

5. ugui, Al., Modele aplicative pentru gestiunea datelor subMICROSOFT ACCESS 2007. Proiectul Biblio, Ed. Sedcom Libris, Iai, 2010

isa us6

Documents