Funcii SQL standard

21Funcii SQL standard

21.1Funciile SQL de agregare

31.2Utilizarea funciilor de agregare n interogri

91.3Utilizarea funciilor de agregare n rapoarte

171.4Probleme propuse

191.5Intrebri de control

1 Funcii SQL standard1.1 Funciile SQL de agregareIn tabelele rezultate din interogri i n rapoarte putem defini cmpuri ce conin rezultatele unor calcule cu valorile datelor din tabelele bazei de date. Cel mai des aceste rezultate provin din evaluarea unor expresii aritmetice ce conin: numere, nume ale coloanelor tabelelor (atribute) i funcii matematice standard ale limbajului SQL. Operatorii aritmetici sunt cei cunoscui, +, -, * i / pentru numere reale i +, -, \ i Mod pentru numere ntregi.

Funciile standard utile ale limbajului SQL sunt urmtoarele:

1. funcii care calculeaz o valoare pe baza valorilor unei coloane ale tabelei (funcii de agregare):

AVG() d valoarea medie,

VAR() d dispersia valorilor din coloana respectiv,

MIN() d valoarea minim,

MAX() d valoarea maxim,

FIRST() d prima valoare,

LAST() d ultima valoare,

COUNT() d numrul de linii,

SUM() d suma valorilor.

Toate aceste funcii au ca argument un nume de coloan (atribut) al tabelei.

2. funcii ce prelucreaz iruri de caractere:

ucase() convertete un ir n litere mari,

lcase() convertete un ir n litere mici,

mid() extrage un subir dintr-un ir,

len() d lungimea unui ir de caractere,

format() formateaz un cmp,

round() rotunjete un camp numeric la un numr de zecimale specificat,

now() furnizeaz un ir de caractere cu data i timpul curent.

Funcia mid() extrage un subir din ir. Ea are prototipul

mid(nume_coloana, indice [, lungime])

nume_coloana este cmpul din care se extrage subirul, indice d poziia n ir a primului character din subir (primul character din ir are indicele 1), iar parametrul opional lungime d lungimea subirului. Dac acest parametru nu se specific, funcia d restul irului.

Funcia round rotunjete numrul la cel mai apropiat ntreg. Ea are forma

round(nume_coloana, numar_de_zecimale)

Funcia format formateaz un cmp. Ea are forma

format(nume_coloana, format)

unde format este un ir de caractere ce d formatul valorii din coloan. De exemplu, funcia

format(now(), YYYY-MM-DD)

d data curent sub forma an-luna-zi.

Restul funciilor au ca argument un nume de coloan (atribut) al tabelei.

1.2 Utilizarea funciilor de agregare n interogri

Instruciunea SELECT poate fi folosit la calculul totalurilor valorilor din unele cmpuri. In instruciunile SELECT de pn acum, fiecare linie a tabelei rezultat corespundea unei singure linii din tabela surs. In interogrile n care nsumm valorile unor cmpuri, o linie a tabelei rezultat corespunde uneia sau mai multor linii din tabela surs. Funciile de agregare ce se aplic asupra valorilor unei coloane din tabela rezultat sunt cele de mai sus: Sum, (nsumare), Avg, (valoare medie), Var, (dispersia), Min, Max, (valoare minim / maxim). Fie de exemplu tabela de mai jos cu vnzrile unei intreprinderi n diverse zone.

FilialaZonaVanzri

Bucureti Muntenia 192000

PloietiMuntenia 88300

ClujTransilvania 172500

Iai Moldova128000

AradTransilvania98000

Timioara Transilvania102300

BacuMoldova34000

Suceava Moldova 28000

O interogare ce calculeaz suma vnzrilor pe zone geografice are ca rezultat urmtoarea tabel

ZonaVnzri

Muntenia 280300

Transilvania 372800

Moldova186000

Datele din coloana Vnzri se calculeaz astfel:

1. se selecteaz toate liniile tabelei,

2. se grupeaz liniile dup valorile din coloana Zona,

3. se insumeaz valorile din coloana Vnzri pentru fiecare grup de linii.

Instruciunea SELECT permite n plus ca liniile din tabela rezultat s fie grupate dup anumite criterii i funciile de agregare s fie aplicate asupra coloanelor din fiecare grup. Instruciunea SELECT corespunztoare are forma urmtoare

SELECT nume_coloana_1, nume_coloana_2, , nume_coloana_k, funcie(nume_coloana_1), funcie(nume_coloana_2), funcie(nume_coloana_k)

FROM nume_tabel

GROUP BY nume_coloana_1, nume_coloana_2,

WHERE condiie

ORDER BY nume_coloana [ASC | DESC], nume_coloana [ASC|DESC], ;

Clauza GROUP BY conine criteriul de grupare, coloanele n funcie de care se grupeaz liniile din tabela rezultat. Toate coloanele nume_coloana_i din clauza GROUP BY trebuie s apar ntre coloanele nume_coloana_j din clauza SELECT i invers. In tabela rezultat vor aprea doar coloanele de forma funcie(nume_coloana_j). Vom expune modul de creare al unei interogri de acest fel n care nsumm valorile unei coloane grupate dup valorile altei coloane. Fie tabela VANZARI_ZONE a crei proiectare este cea din Figura 1, iar datele din tabel sunt cele din Figura 2.

Figura 1. Proiectarea tabelei VANZARI_ZONE.

Figura 2. Tabela VANZARI_ZONE.

Deoarece cmpul ZONA conine doar valorile Transilvania, Moldova, Muntenia, Oltenia, se va crea o caset Combo Box pentru introducerea acestor valori.Vrem s crem o interogare ce totalizeaz vnzrile grupate pe zone geografice: Transilvania, Moldova, Muntenia i Oltenia. Procedura este urmtoarea:

1. se creaz o interogare. Clic pe butonul New al casetei Database i se alege opiunea Design View. Se afiaz fereastra de proiectare a interogrii din Figura 3,

Figura 3. Ferestra de proiectare a interogrii.

2. clic pe meniul View ( Totals din Tabelul 1 sau pe butonul Totals, , al barei de instrumente sau al meniului contextual din Tabelul 2b; n fereastra de proiectare apare linia Total ca n Figura 4,

a) meniul Queryb) meniul View

Tabelul 1. Meniurile apicaiei Query Design.

a) meniu contextualb) bara de instrumente Query Design

Tabelul 2. Meniu contextual i bara de instrumente Query Design.3. se completeaz cmpurile din linia Field, cmpurile ZONA i VANZARI, iar n linia Total a coloanei VANZARI se introduce funcia Sum din caseta afiat n Figura 4,

Figura 4. Completarea interogrii.

4. tabela rezultat este cea din Figura 5. Coloana cu rezultatul are titlul SumOfVANZARI atribuit de aplicaie,

Figura 5. Tabela rezultat din interogare.

5. se modific numele coloanei SumOfVANZRI din tabela rezultat pentru a fi mai semnificativ; n celula VANZARI din linia Field se introduce numele dorit, Vanzari pe zone : nainte de numele coloanei, ca n Figura 6,

6. Figura 6. Modificarea numelui unei coloane n tabela rezultat.

7. noua tabel rezultat este cea din Figura 7.

Figura 7. Tabela rezultat din interogri.

Vom modifica irurile din coloana ZONA astfel nct ele s apar cu litere mari. In prima celul a liniei Field vom introduce expresia

ZONE:ucase([ZONA])

vezi Figura 8. Deoarece n expresie avem rezultatul unei funcii, ucase([ZONA]), trebuie s atribuim i un nume coloanei, care a fost ales ZONE.

Figura 8. Modificare unei coloane n tabela rezultat.

Noua tabel rezultat este cea din Figura 9.

Figura 9. Tabela rezultat din interogare.

Instruciunea SELECT generat de Access din formular este cea din Figura 10.

Figura 10. Instruciunea SELECT a interogrii.1.3 Utilizarea funciilor de agregare n rapoarte

Funciile de agegare se pot utiliza i n rapoarte. Cmpurile cu rezultate ale unor expresii se introduc n rapoarte n casete text. Vom arta cum se face acest lucru pe un exemplu simplu.

Fie o tabel PRODUS-PRET ce are coloanele codul unui produs, ID_PRODUS i preul lui, PRET.

PRET_PRODUS

ID_PRODUSPRET

Proiectarea acestei tabele este cea din Figura 11.

Figura 11. Proiectare tabelei PRODUS-PRET.

Tabela cu date este cea din Figura 12.

Figura 12. Tabela PRODUS-PRET.

Vrem s crem un raport care s conin cele dou coloane ale tabelei plus nc o coloan cu valoarea TVA care este dat de relaia PRET * 0.19. Vom crea un raport cu aplicaia vrjitor, utilizat n capitolele anterioare cu cele dou coloane ale tabelei, la care vom aduga coloana cerut cu aplicaia Design View. Raportul iniial, creat cu aplicaia vrjitor, are numele PRODUS-PRET i n vederea Print este cel din Figura 13 iar n vederea Design este cel din Figura 14.

Figura 13. Raport cu coloanele tabelei PRODUS-PRET.

Figura 14. Fereastra Design View pentru raportul PRODUS-PRET.

Vom aduga o etichet cu numele TVA i o caset text unde s apar valoarea cerut. Procedura de adugare a etichetei n zona Page Header este cea dinainte:

1. clic pe obiectul Aa din caseta de instrumente; cursorul se modific n form de cruce,

2. se deseneaz un dreptunghi n zona Page Header unde se scrie textul TVA.

Trebuie s adugm o caset text n zona Detail. Acest obiect este ab| i el genereaz dou obiecte, unul o etichet i alturi o caset text, dup cum s-a vzut ntr-un capitol anterior. Se adaug acest obiect i se terge eticheta, ca mai nainte. In caseta text se introduce textul

=[PRET]*0,19

Se poate proceda i altfel: se adug caseta text n zona Detail i se mut eticheta n zona Page Header cu opiunile Cut i Paste ale meniului Edit.

Acesta este modul de a insera expresii aritmetice ale cror rezultate vor aprea n caseta text. Se adaug o linie de subliniere a etichetei coloanei TVA. Toate obiectele adugate au nite caracteristici implicite, dimensiunea i tipul fontului, alinierea textului, etc., care se pot modifica cu caseta Properties a obiectelor grafice. Proiectarea raprotului final este cea din Figura 15.

Figura 15. Modificarea proiectrii raportului PRODUS-PRET.

Raportul final este artat n Figura 16.

Figura 16. Raportul PRODUS-PRET modificat.

Vom exemplifica utilizarea funciilor standard la crearea unui raport al vnzrilor anuale ale unei intreprinderi din tabela din Figura 17. Vrem ca n raport s apar la sfrit valoarea minim, maxim i medie anual a vnzrilor.

Figura 17. Vnzrile anuale ale unei intreprinderi.

Pentru nceput crem un raport cu aplicaia vrjitor pe baza tabelei de date. In raport apar coloanele AN i SUMA ca n Figura 18.

Figura 18. Raportul vnzrilor anuale.

Fereasta Design View a raportului, generat de aplicaia vrjitor, este cea din Figura 19.

Figura 19. Fereastra Design View a raportului din Figura 18.

Vom aduga n zona Report Footer trei casete de text n care vor apare valorile minim, maxim i medie a cifrelor vnzrilor anuale. Pentru aceasta selectm n caseta Toolbox obiectul Text Box simbolizat prin ab| i deplasm cursorul n locul unde dorim s apar caseta text. Desenm dreptunghiul corespunztor casetei; n acest dreptunghi apare caseta text iar n stnga apare eticheta casetei. Facem acest lucru pentru cele trei casete text i obinem proiectarea din Figura 20. Reamintim c, la selectarea obiectului grafic n caseta Toolbox, cursorul are forma +ab|.

Figura 20. Fereastra Design View cu casetel text adugate.

Vom introduce textul etichetelor n obiectele Text9, , Text13 dup cum urmeaz:

1. se selecteaz obiectul grafic cu un clic,

2. se execut nc un clic pe obiect i apare cursorul de inserare,

3. tastm textul dorit pentru etichet.

Casete text au eticheta Unbound, deoarece el nu sunt conectate la nicio surs de date. Vom introduce funciile n felul urmtor:

1. se selecteaz obiectul grafic cu un clic,

2. se execut nc un clic pe obiect i apare cursorul de inserare,

3. se introduce expresia funciei.

Expresia funciei are forma:

=nume_funcie(argument)

Pentru funcia Min expresia pe care o introducem este

=Min(SUMA)

Argumentul funciei este coloana SUMA. Aplicaia Access scrie argumentul n paranteze drepte, [ i ], astfel nct expresia funciei apare

=Min([SUMA])

Expresia funciei se poate introduce i cu aplicaia Expression Builder.

Se mai introduce o linie deasupra celor trei casete text. Grosimea liniei se va lua 2 pt. Linia se va selecta n caseta Properties. Proiectarea final este cea din Figura 21.

Figura 21. Proiectarea final a raportului.

Raportul este cel din Figura 22.

Figura 22. Raportul vnzrilor unei intreprinderi.

1.4 Probleme propuse

1. Fie tabela de mai jos cu vnzrile trimestriale ale unei interprinderi n milioane lei n diverse ri. Cheia primar a tabelei este TARA.VANZARI

TARATRIM_1TRIM_2TRIM_3TRIM_4

Germania 62809875

Franta 51789563

Austria 31354634

Romania 26324029

SUA 9210516097

Spania 48555951

Se cere:

s se proiecteze tabela,

s se completeze tabela cu date cu ajutorul unui formular,

s se fac un raport n care s se afieze totalurile pe trimestre. Se va crea un raport cu tabela utiliznd aplicaia Report Wizard. In seciunea Report Footer se vor aduga patru casete text n care se va introduce funcia SUM(),

s se proiecteze o diagram cu vnzrile pe ri.

2. Fie tabela DATE_CONTABILE de mai jos cu datele contabile ale unei intreprinderi. Cheia primar a tabelei este AN. DATE_CONTABILE

ANVENITURICHELTUIELIPROFIT_BRUTIMPOZITPROFIT_NET

20014704125817.440.6

20024954306519.545.5

200360249510732.174.9

200471453018455.2128.8

200589062027081189

200686061025075175

In tabel se vor introduce doar coloanele AN, VENITURI, CHELTUIELI. Celelalte coloane se vor calcula cu formulele:

PROFIT_BRUT = VENITURI CHELTUIELI

IMPOZIT = PROFIT_BRUT * 30%

PROFIT_NET = PROFIT_BRUT - IMPOZITSe cere:

s se proiecteze tabela,

se vor introduce datele cu un formular,

se va crea un raport care s fac suma datelor pe coloane; sumele respective apar n zona Report Footer,

se va crea o diagram cu profitul net pe ani.

3. Fie tabela VANZARI_ZONE_ANI de mai jos ce conine date despre vnzrile unei intreprinderi pe zone geografice. Cheia primar a tabelei este ID. VANZARI_ZONE_ANI

IDZONAANTRIMESTRUVANZARI

1Moldova 20081173000

2Transilvania 20072240000

3Muntenia20084128500

4Moldova200821687000

5Transilvania20071135200

6Muntenia2008398200

7Muntenia20082102500

8Muntenia2008178500

9Moldova20083162400

10Moldova20084143500

11Transilvania20081224800

12Transilvania20082254500

13Transilvania20084197600

14Transilvania20083204300

15Moldova20074138600

16Moldova20073193500

17Transilvania20073238300

18Transilvania20074231600

19Muntenia20074189400

20Moldova20072142300

Se cere:

s se proiecteze tabela,

se va crea un formular de introducere a datelor cu care se umple tabela, se va crea o interogare cu vnzrile pe ani pe zone geografice,

se vor crea diagrame cu suma vnzrilor, grupate pe ani i pe trimestre.

4. Fie tabela de mai jos cu angajaii unei intreprinderi. Cheia primar a tabelei este CNP. ANGAJATI

CNPNumePrenumeData naterii

1237Ionescu Vasile 22/05/1985

3520Popa Gheorghe15/07/1990

2715Mihai Nicolae 03/09/1987

1744Georgescu Nicolae 14/10/1988

2560Vasile Mihai 12/06/1986

4187Nicolaescu Ioan 15/02/1992

3208George Ilie 18/12/1988

Se cere:

s se proiecteze tabela,

se va crea un formular de introducere a datelor cu care se umple tabela,

s se creeze un raport n care s apar i vrsta fiecrei persoane.

1.5 Intrebri de control1. Care sunt funciile SQL standard de agregare a datelor?2. Care este forma instruciunii SELECT n care se utilizeaz funcii de agregare a datelor?PAGE 11