C4-BD

BAZE DE DATE

Universitatea Constantin Brncui din Trgu-Jiu

Facultatea de Inginerie

Departamentul de Automatic, Energie i Mediu

LECTOR DR. ADRIAN RUNCEANU

Limbajul SQL

Cereri SELECT pe o tabela

4.1. Funcii

4.2. Funcii referitoare la o singur nregistrare

4.3. Funcii referitoare la mai multe nregistrri

4.3.1. Clauza GROUP BY

4.3.2. Excluderea grupurilor (clauza HAVING)

4.3.3. Imbricarea funciilor de grup

28.04.2014 Curs - BAZE DE DATE 2

Tabele Angajati si Departamente

Pentru exemplele din cursuri vom folosi tabelele

Angajati si Departamente.

DEPARTAMENTE

Id_dept NUMBER(3) CHEIE PRIMARA (PK)

Den_dept VARCHAR2(20)

Id_manager VARCHAR2(3)

Locatie VARCHAR2(100)

28.04.2014 Curs 3 - BAZE DE DATE 3

Tabele Angajati si DepartamenteANGAJATI

Id_angajat NUMBER(3) CHEIE PRIMARA (PK)

Id_dept NUMBER(3)

REFERINTA (FK) LA TABELA DEPARTAMENTE

Nume VARCHAR2(40)

Prenume VARCHAR2(40)

Functie VARCHAR2(25)

Salariu NUMBER(7)

Id_manager VARCHAR2(3)

Data_ang DATE

Comision NUMBER(5)

28.04.2014 Curs 3 - BAZE DE DATE 4

Funcii

Funciile sunt o caracteristic

important a SQL si sunt utilizate pentru:

a realiza calcule asupra datelor

a modifica date

a manipula grupuri de nregistrri

a schimba formatul datelor

sau pentru a converti diferite tipuri de date


Funcii

Funciile se clasific n dou tipuri:

1. Funcii referitoare la o singur nregistrare

2. Funcii referitoare la mai multe nregistrri


Funcii

1. Funcii referitoare la o singur nregistrare:

1.funcii caracter

2.funcii numerice

3.funcii pentru data calendaristic si or

4.funcii de conversie

5.funcii diverse


Funcii

2. Funcii referitoare la mai multe nregistrri:

funcii totalizatoare sau funcii de grup


Funcii

Diferena dintre cele dou tipuri de funcii este numrul de nregistrri pe care acioneaz:

Funciile referitoare la o singur nregistrare returneaz un singur rezultat pentru fiecare rnd al tabelului,

pe cnd funciile referitoare la mai multe nregistrri returneaz un singur rezultat pentru fiecare grup de nregistrri din tabel.


FunciiO observaie importanta este faptul c dac se

apeleaz o funcie SQL ce are un argument egal cu

valoarea Null, atunci n mod automat rezultatul va

avea valoarea Null.

Singurele funcii care nu respect aceast

regul sunt:

CONCAT

DECODE

DUMP

NVL

REPLACE


Limbajul SQL


4.1. Funcii







Funcii referitoare la o singur nregistrare

Sunt funcii utilizate pentru manipularea

datelor individuale.

Ele pot avea unul sau mai multe

argumente i returneaz o valoare pentru

fiecare rnd rezultat n urma interogrii.



Sunt mai multe tipuri de funcii pe un singur rnd.

n sintaxa general function_name este numele funciei i arg1,arg2 sunt argumentele funciei care pot fi date de numele unei coloane sau de o expresie.


Sintaxa: function_name [(arg1,arg2,...)]


Funciile pe un singur rnd cuprind urmtoarele

tipuri de funcii:

1. funcii de tip caracter

2. funcii de tip numeric

3. funcii de tip data

4. funcii de conversie

5. funcii generale: NVL, NVL2, NULLIF,

COALESCE, CASE, DECODE



1. Funcii de tip caracter

Aceste funcii au ca argumente date de

tip caracter i returneaz date de tip

VARCHAR2, CHAR sau NUMBER.


Funcii referitoare la o singur

nregistrare

Cele mai importante funcii caracter sunt:

Functie Descriere

LOWER(column|expression) converteste alfa caracterele din caractere mari in

caractere mici

UPPER(column|expression) converteste alfa caracterele din caractere mici in

caractere mari

INITCAP(column|expression) converteste prima litera a fiecarui cuvant in

caractere mari si restul cuvantului in caractere

mici

CONCAT(column1|expression1,

column2|expression2)

functia este echivalentul operatorului de

concantenare (||)

SUBSTR(column|expression, m [, n]) returneaza un sir de n caractere incepand cu

caracterul aflat pe pozitia m

LENGHT(column|expression) returneaza numarul de caractere dintr-o expresie

INSTR(column|expression, 'string',

[m], [n])

returneaza pozitia unui anumit sir, optional se

poate incepe cautarea cu pozitia m sau cu a n-a

aparitie a sirului. m si n sunt prin definitie 1

REPLACE(text, search_string,

replacement_ string)

cauta un anumit text intr-un sir de caractere si

daca il gaseste il inlocuieste



Exemplu de utilizare a

funciei LENGTH:

SELECT LENGTH(nume)

FROM angajati;



Exemplu:

SELECT 'Numele functiei pentru

'||UPPER(nume)||'este '||LOWER(functie) AS

"DETALII ANGAJAT"

FROM angajati;



Exemplu:

SELECT numar_ang, UPPER(nume), functie,

id_dept

FROM angajati

WHERE nume = 'popa'



Clauza WHERE a acestei cereri SQLcompar numele din tabela Angajai cu 'Popa'.

Pentru comparaie numele sunt convertite n litere mici i din aceast cauz se obine un rezultat.

Exemplu:

SELECT id_ang, UPPER(nume), functie, id_dept

FROM angajati

WHERE INITCAP(nume) = 'Popa'



Exemplu:

Pentru afiarea numelui cu majuscule de

folosete funcia UPPER.

SELECT id_ang, CONCAT(nume, functie) NUME,

UPPER(nume)

FROM angajati;



Spre deosebire de alte funcii, funciile

caracter pot fi imbricate pn la orice

adncime.

Dac funciile sunt imbricate, atunci

ele sunt evaluate din interior spre exterior.

Pentru a determina, de exemplu, de

cte ori apare caracterul 'A' n cmpul

nume vom folosi interogarea:



SELECT nume, LENGTH (nume) - LENGTH (TRANSLATE(nume,'DA','D'))

FROM angajati;


NUME 'A'---- ---GHEORGHIU 0MARIN 1GEORGESCU 0IONESCU 0ALBU 1VOINEA 1STANESCU 1


Not:

n exemplul anterior, funcia TRANSLATE (nume, 'DA', 'D') va cuta n coloana nume primul caracter (caracterul 'D') din cel de-al doilea argument al funciei (irul de caractere 'DA') i l va nlocui cu primul caracter (adic tot cu caracterul 'D') din cel de-al treilea argument al funciei (irul de caractere 'D'), apoi va cuta cel de-al doilea caracter, adic caracterul 'A', i l va terge din cmpul nume deoarece acesta nu are caracter corespondent n cel de-al treilea argument al funciei.

Am folosit acest artificiu deoarece irul de caractere vid este echivalent cu valoarea Null, deci funcia TRANSLATE(nume, 'A', ' ') ar fi nlocuit toate valorile cmpului nume cu valoarea Null.


Limbajul SQL


4.1. Funcii







4.3. Funcii de grup

Funciile de grup sunt funcii care opereaz pe un set de rnduri pentru a da un rezultat pe ntreg setul.

Parametrii i descrierea funciilor de grup.

Funciile de grup sunt:

1. AVG

2. COUNT

3. MAX

4. MIN

5. STDDEV

6. SUM

7. VARIANCE


4.3. Funcii de grup

Functia Descriere

AVG([DISTINCT|ALL]n) Valoarea medie pentru grup, ignorand valorile nule

COUNT({*|[DISTINCT|ALL]expr})

Numarul de randuri unde expr evalueaza altceva in afara de null (folosind * sunt numarate toate randurile, incluzand duplicatele si pe cele cu valoare nula)

MAX([DISTINCT|ALL]expr) Valoarea maxima a expr, ignorand valorile nule

MIN([DISTINCT|ALL]expr) Valoarea minima a expr, ignorand valorile nule

STDDEV([DISTINCT|ALL]x) Deviatia standard pentru grup, ignorand valorile nule

SUM([DISTINCT|ALL]x) Suma valorilor pentru grup, ignorand valorile nule

VARIANCE([DISTINCT|ALL]x) Variatia pentru grup, ignorand valorile nule


Fiecare dintre aceste funcii accept anumii parametri:

4.3. Funcii de grupDISTINCT face ca funcia s ignore valorile

duplicat.

ALL face ca funcia s afieze i valorile

duplicat.

Valoarea implicit este ALL, deci nu este

necesar s fie specificat.

Tipul de dat returnat de funcia expr poate fi

CHAR, VARCHAR2, NUMBER sau DATE.

Toate funciile de grup ignor valorile nule.

Pentru a lua n considerare i valorile nule se

folosesc funciile NVL, NVL2 sau COALESCE.


4.3. Funcii de grup

Sintaxa funciilor de grup:

Rezultatele sunt sortate implicit cresctor. Pentru o ordonare descresctoare se va folosi clauza DESC dup ORDER BY.


SELECT [coloana,]functie_de_grup(coloana), ...FROM tabel[WHERE conditie][GROUP BY coloana][HAVING conditie_de_grupare][ORDER BY coloana];

4.3. Funcii de grup

Exemplul 1:

Afiarea salariului mediu, maxim, minim i suma

tuturor salariilor angajailor cu funcie VANZATOR.

SELECT AVG(salariu), MAX(salariu), MIN(salariu),

SUM(salariu)

FROM angajati

WHERE functie='VANZATOR';


4.3. Funcii de grup

Exemplul 2

Datele la care s-au fcut prima i ultima

angajare.

SELECT MIN(data_ang), MAX(data_ang)

FROM angajati;


4.3. Funcii de grup

Exemplul 3

Primul i ultimul nume de angajat n ordine

alfabetic.

SELECT MIN(nume), MAX(nume)

FROM angajati;


4.3. Funcii de grup

Funcia COUNT

Funcia COUNT are 3 formate:


COUNT(*)

COUNT(expr)

COUNT(DISTINCT expr)

4.3. Funcii de grup

COUNT(*) ntoarce numrul de rnduri dintr-un

tabel care satisfac criteriul de selecie, incluznd

rndurile duplicat i rndurile coninnd valori

nule.

Dac clauza WHERE este introdus, atunci

COUNT(*) returneaz numrul de rnduri care

satisfac condiia din clauza WHERE.


4.3. Funcii de grup

n contrast, funcia COUNT(expr) ntoarce

numrul de valori nenule din coloana specificat

de expr.

COUNT(DISTINCT expr) returneaz numrul de

valori distincte, nenule din coloana specificat de

expr.


4.3. Funcii de grup

Exemplul 4

Numrul angajailor din departamentul cu id-ul 30.

SELECT COUNT(*)

FROM angajati

WHERE id_dept = 30;


4.3. Funcii de grup

Exemplul 5

Numrul angajailor care iau comision din

departamentul 30.

SELECT COUNT(comision)

FROM angajati

WHERE id_dept = 30;


4.3. Funcii de grup

Exemplul 6

Numrul de departamente din firma (varianta

incorect i varianta corect).

SELECT COUNT(id_dept), COUNT(DISTINCT

id_dept)

FROM angajati;


4.3. Funcii de grup

Exemplul 7

Comisionul mediu n departamentul 30 (ignornd

sau nu valorile nule).

SELECT AVG(comision), AVG(NVL(comision, 0))

FROM angajati

WHERE id_dept = 30;


Limbajul SQL


4.1. Funcii








Pn acum toate funciile de grup au fost

aplicate ntregii tabele.

Pentru a putea mpri tabela n grupuri mai

mici se folosete clauza GROUP BY.

Folosirea acesteia returneaz informaii

sumare despre fiecare grup.



Folosind GROUP BY nu se pot extrage i coloane individuale, ci doar coloane ce rmn identice n tot grupul.

Folosind WHERE se pot exclude rnduri, naintea mpririi lor n grupuri.

Nu pot fi folosite aliasuri de coloane n clauza GROUP BY.

Implicit, rndurile sunt sortate cresctor dupcoloana (coloanele) specificate n GROUP BY.

Acest lucru poate fi schimbat folosind ORDER BY.


4.3.1. Clauza GROUP BYExemplul 8

Salariul mediu pe fiecare departament.

SELECT id_dept, AVG(salariu)

FROM angajati

GROUP BY id_dept;



Exemplul 9

Salariul mediu pe fiecare departament, iar rezultatele ordonate dup salariul mediu pe departament.


FROM angajati

GROUP BY id_dept

ORDER BY AVG(salariu);



Gruparea dup mai multe coloane.

Cteodat este necesar obinerea de

rezultate pentru grupuri n alte grupuri.

Atunci n dreptul clauzei GROUP BY vom

ntlni mai multe coloane.



Exemplul 10

Salariul total pe fiecare departament si pe

fiecare functie, iar rezultatele ordonate dup

salariul mediu pe departament.

SELECT id_dept, functie, SUM(salariu)

FROM angajati

GROUP BY id_dept, functie

ORDER BY AVG(salariu);


Limbajul SQL


4.1. Funcii







4.3.2. Excluderea grupurilor

(clauza HAVING)

Clauza HAVING funcioneaz n mare ca i

clauza WHERE, diferena fiind c HAVING este

folosit pentru a exclude anumite grupuri din rezultat,

nu rnduri cum fcea WHERE.

Clauza HAVING poate fi folosit nainte de

GROUP BY, ns este mai logic s fie folosit dup.

Ordinea execuiei va rmne aceeai.



(clauza HAVING)

Exemplul 11

Salariul mediu pe fiecare departament unde

acesta depete 3000$.


FROM angajati

HAVING AVG(salariu) > 3000

GROUP BY id_dept;



(clauza HAVING)

Exemplul 12

Salariul maxim pe fiecare departament unde

acesta depete 3000$.

SELECT id_dept, MAX(salariu)

FROM angajati

HAVING AVG(salariu) > 3000

GROUP BY id_dept;



(clauza HAVING)Exemplul 13

Salariul total pe fiecare funcie, fr a lua n calcul

MANAGERII, excluznd funciile cu suma salariilor

sub 6000$ cu ordonare dup total.

SELECT functie, SUM(salariu)

FROM angajati

WHERE functie!='MANAGER'

GROUP BY functie

HAVING SUM(salariu) < 6000

ORDER BY SUM(salariu);


Salariul total pe fiecare funcie

fr a lua n calcul MANAGERII

excluznd funciile cu suma salariilor sub 6000$

cu ordonare dup total

Limbajul SQL


4.1. Funcii







Ordinea de executie a functiilor de

grup

Serverul Oracle execut funciile de grup ntr-o

anumit ordine:

1. Selecia rndurilor ce respect clauza WHERE

2. Gruparea rndurilor obinute, respectnd clauza

GROUP BY

3. Calcularea rezultatelor funciilor de grup pentru

fiecare grup n parte

4. Eliminarea grupurilor ce nu respect clauza

HAVING

5. Ordonarea rezultatelor respectnd clauza

GROUP BY.


Ordinea de executie a functiilor de

grup Ordinea de execuie are o importan foarte mare,

deoarece are un impact direct asupra vitezei.

Cu ct mai multe nregistrri pot fi eliminate

utiliznd clauza WHERE, cu att mai puin va

dura gruparea i operaiile ce urmeaz.

Dac o cerere SQL este conceput s elimine

nregistrri/grupuri doar folosind clauza HAVING,

atunci ar fi bine de ncercat dac este posibil i

prin clauza WHERE. De obicei, totui, aceast

rescriere nu va fi posibil.


4.3.3. Imbricarea functiilor de grup

Funciile de grup pot fi imbricate cu o

adncime de 2.

Exemplul 14

Salariul mediu maxim.

SELECT MAX(AVG(salariu))

FROM angajati

GROUP BY id_dept;


ntrebri?


C4-BD

Documents

Transcript of C4-BD