Utilizarea functiilor in sistemul ORACLE

Utilizarea functiilor in sistemul ORACLE

Functii grup

Functiile grup opereaza pe "multimi" de linii si returneaza un rezultat pe grup. Aceste "multimi" de linii pot fi reprezentate de intregul tabel sau de tabelul impartit in grupuri.

Sistemul Oracle accepta urmatoarele functii grup: COUNT, MIN, MAX, AVG, VARIANCE, STDDEV.

Observatii:

DISTINCT- determina luarea in considerare doar a valorilor "neduplicate".

ALL- determina luarea in considerare a fiecarei valori, inclusiv a duplicatelor. ALL este implicit si nu e necesar sa fie specificat.

Toate functiile grup, cu exceptia functiei COUNT(), ignora valorile null.

Functia COUNT() are doua formate:

COUNT(*)- returneaza numarul de linii din tabel, incluzand duplicatele si randurile cu valori null;

COUNT(expr)- returneaza numarul de linii non-null  in coloana identificata prin expr.

Functiile COUNT, MIN, MAX pot fi aplicate unor campuri numerice sau nenumerice; restul functiilor se aplica doar campurilor numerice.

Functiile grup pot sa apara doar in lista de la SELECT sau in clauza HAVING.

Exemple:

Afisarea salariului minim, maxim, mediu si a sumei salariilor pentru angajatii din al caror "job" incepe cu "SALES" (SALESMAN):

SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)

FROM emp

WHERE job LIKE 'SALES%';

Afisati angajatul care are cea mai mare vechime si pe cel care are cea mai mica vechime:

SELECT MIN(hiredate), MAX(hiredate)

FROM emp;

Afisati angajatul care are este primul si pe cel care este ultimul in ordinea alfabetica a numelui:

SELECT MIN(ename), MAX(ename)

FROM emp;

Afisati numarul de angajati din departamentul 30:

SELECT COUNT(*)

FROM emp

WHERE deptno=30;

Afisati numarul persoanelor din departamentul 30 care pot castiga comision:

SELECT COUNT(comm)

FROM emp

WHERE deptno=30;

Afisati numarul departamentelor din tabelul EMP:

SELECT COUNT(deptno)

FROM emp;

Afisati numarul departamentelor distincte din tabelul EMP:

SELECT COUNT(DISTINCT deptno)

FROM emp;

Afisati comisionul mediu al angajatilor:

SELECT AVG(comm)

FROM emp;

SELECT AVG(NVL(comm,0))

FROM emp;

Observatie:

In primul caz, media este calculata doar pe baza acelor randuri care contin o valoare "valida" in coloana comm. Media este calculata ca totalul comisionului platit tuturor angajatilor impartit la numarul celor care pot castiga comision.

In cel de-al doilea caz, media este calculata pe baza tuturor randurilor indiferent ce contin in coloana comm. Media este calculata ca totalul comisionului platit tuturor angajatilor impartit la numarul total al angajatilor din companie.

Functia NVL forteaza functiile de grup sa includa si valorile null.

In exemplele de mai sus, functiile grup au tratat tabelul ca un grup larg de informatii.

In anumite cazuri, este nevoie ca tabelul sa fie impartit in grupuri mai mici. Aceasta poate fi facuta folosind clauza GROUP BY in sintaxa comenzii SELECT:

SELECT coloana, functie_grup

FROM tabel

[WHERE conditii]

[GROUP BY expresie_grupare]

[HAVING conditie_grup]

[ORDER BY coloana];

expresie_grupare reprezinta coloana (coloanele) a carei valori constituie baza pentru liniile grupate;

conditie_grup reprezinta conditia care restrictioneaza grupurile de linii returnate (la cele care indeplinesc conditia).

De retinut:

v    daca includeti o functie grup intr-o clauza SELECT, nu puteti selecta rezultate individuale decat in cazul in care coloana apare in clauza GROUP BY;

v    folositi clauza WHERE pentru a exclude linii inaintea divizarii lor in grupuri;

v    trebuie sa includeti coloanele in clauza GROUP BY;

v    nu puteti folosi aliasul coloanei in clauza GROUP BY;

v    implicit, randurile sunt sortate-in ordinea crescatoare a coloanei incluse in clauza GROUP BY; se poate rescrie aceasta ordonare prin folosirea clauzei ORDER BY.

Exemple:

Afisati salariul mediu pe fiecare departament:

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno;

Afisati salariul mediu pe fiecare departament in ordinea crescatoare a salariului mediu:

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

ORDER BY AVG(sal);

Afisati salariul total platit pentru fiecare job in cadrul fiecarui departament:

SELECT deptno, job, SUM(sal)

FROM emp

GROUP BY deptno,job;

Afisati codul departamentului si salariul mediu pe departament, pentru departamentele in care salariul mediu depaseste $2000:

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal)>2000;

DA

SELECT deptno, AVG(sal)

FROM emp

WHERE AVG(sal)>2000

GROUP BY deptno;

NU

Conditia care restrictioneaza afisarea salariilor medii ale acelor departamente care au o medie de peste $2000 trebuie sa apara in clauza HAVING si nu in clauza WHERE.

Afisati salariul maxim pe departamentele in care salariul maxim depaseste

SELECT deptno, MAX(sal) Sal_max

FROM emp

GROUP BY deptno

HAVING MAX(sal)>2900;

Afisati meseria si salariul lunar total pe fiecare meserie, pentru meseriile pentru care acesta depaseste $5000; excludeti pe cei care sunt "salesman" si ordonati lista dupa salariul lunar total:

SELECT job, SUM(sal) sal_tot

FROM emp

WHERE job NOT LIKE 'SALES%'

GROUP BY job

HAVING SUM(sal)>5000

ORDER BY SUM(sal);

Afisati salariul mediu maxim al departamentelor:

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno;

Afisati salariul minim, maxim, suma salariilor si salariul mediu folosind aliasurile Min, Max, Suma, Medie; rotunjiti rezultatele la valori intregi:

SELECT ROUND(MIN(sal),0) "Min", ROUND(MAX(sal),0) "Max", ROUND(SUM(sal),0) "Suma", ROUND(AVG(sal),0) "Medie"

FROM emp;

Afisati aceleasi cerinte de mai sus grupate pe fiecare meserie:

SELECT job, ROUND(MIN(sal),0) "Min", ROUND(MAX(sal),0) "Max", ROUND(SUM(sal),0) "Suma", ROUND(AVG(sal),0) "Medie"

FROM emp

GROUP BY job;

Afisati numarul de manageri (fara a afisa numele lor):

SELECT COUNT(DISTINCT mgr) nr_mgr

FROM emp;

Afisati codul managerilor si salariul minim pe care l-a obtinut fiecare manager; excludeti angajatii care nu au manager, precum si grupurile pentru care salariul minim este mai mic de $1000. Sortati rezultatele in ordine descrescatoare a salariului:

SELECT mgr, MIN(sal)

FROM emp

WHERE mgr IS NOT NULL

GROUP BY mgr

HAVING MIN(sal)>1000

ORDER BY MIN(sal) DESC;

Afisati numele departamentului, numele locatiei, numarul de angajati si salariul mediu al tuturor angajatilor din departament.

SELECT d.dname, d.loc, COUNT(*) "Nr angajati", ROUND(AVG(sal),2) "Salariu"

FROM emp e, dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname,d.loc;