FUNCŢII DE GRUP ORACLE Pentru exemplificarea fundamentelor SQL care stau la baza interpretării rezultatelor economice obţinute în urma prelucării datelor din sistemul economic sunt foarte des utilizate funcţiile de grup COUNT, MAX, MIN, SUM, AVG, STDDEV, VARIANCE precum şi clauzele ROLLUP, CUBE, GROUPING, GROUP BY, HAVING - care permit o analiză economică detaliată a rezultatelor obţinute. 12/27/2012

CUPRINS

Noţiuni teoretice ...................................................................................................................................... 3

Funcţia COUNT(exp) ............................................................................................................................... 3

Funcţia MAX(exp) .................................................................................................................................... 3

Funcţia MIN(exp) ..................................................................................................................................... 3

Funcţia AVG(exp) ..................................................................................................................................... 3

Funcţia SUM(exp) .................................................................................................................................... 3

Aplicaţii ....................................................................................................................................................... 4

Fişa nr.1 ....................................................................................................................................................... 4

Clauza GROUP BY ..................................................................................................................................... 5

Clauza HAVING .......................................................................................................................................... 6

Subtotaluri ................................................................................................................................................. 6

Fişa nr. 2 ...................................................................................................................................................... 7

Fişa nr. 3 ...................................................................................................................................................... 8

Rezolvarea fişei nr. 1 .............................................................................................................................. 9

Rezolvarea fişei nr. 2 .............................................................................................................................. 9

Noţiuni teoretice

Funcţiile de grup returnează o singură valoare pentru un grup sau set de linii dintr-un tabel. Cu ajutorul acestor funcţii putem calcula valoarea minimă sau valoarea maxima dintr-un set de valori, putem determina numarul de ınregistrari ce respecta o anumita condiţie etc. Funcţiile de grup ignora valorile nule.

Funcţia COUNT(exp)

- determină numărul de valori ale lui exp. Această funcţie poate fi folosită în combinaţie cu clauza DISTINCT, pentru a număra doar valorile distincte dintr-un domeniu.Exemple:

SELECT COUNT(job) FROM EMP SELECT COUNT(DISTINCT(job)) FROM EMP

Funcţia MAX(exp)

-determină valoarea maximă a valorilor expresiei exp. Exemplu: SELECT MAX(sal) FROM emp

SELECT MAX(hiredate) FROM emp

Funcţia MIN(exp)

-determină valoarea minimă a valorilor expresiei exp. Exemplu: SELECT MIN(sal) FROM emp

SELECT MIN(hiredate) FROM emp

Funcţia AVG(exp)

-determină media valorilor expresiei exp. Exemplu: SELECT AVG(sal) FROM emp

SELECT AVG(comm), AVG(NVL(comm,0)) FROM emp

Funcţia SUM(exp)

-determină suma valorilor expresiei exp.Exemplu: SELECT SUM(sal) FROM emp

SELECT SUM(sal) FROM emp WHERE job='MANAGER'

CUPRINS

Aplicaţii

Deschide baza de date Oracle şi urmăreşte rezultatul afişat de interogările de mai jos: SELECT AVG(sal), MAX(sal),MIN(sal), SUM(sal) FROM emp WHERE job='SALESMAN'

SELECT MIN(ename), MAX(ename) FROM emp

SELECT MIN(hiredate), MAX(hiredate) FROM emp WHERE sal BETWEEN 1500 AND

2500

SELECT COUNT(*) FROM emp WHERE COMM IS NOT NULL

SELECT MAX(hiredate)-MIN(hiredate) FROM emp

SELECT MAX(sal),MIN(sal),MAX(sal)-MIN(sal) FROM emp WHERE hiredate BETWEEN '01.01.1982' AND '01.01.1985'

Fişa nr.1

1. Afişaţi salariul mediu, maxim, minim şi suma tuturor salariilor angajaţilor cu job MANAGER.

2. Afişaţi datele(ziua şi luna, în format monday 06, september) la care s-au făcut prima şi ultima angajare în anul 1982.

3. Afişaţi primul şi ultimul nume de angajat în ordine alfabetică.

4. Afişaţi numărul angajaţilor care iau comision din departamentul 30.

5. Afişaţi numărul de departamente din firmă.

6. Afişaţi numărul de funcţii din firmă.

7. Afişaţi numărul de persoane angajate în 1981.

Rezolvarea fişei nr. 1

CUPRINS

Clauza GROUP BY

Pentru a putea împărţi tabela în grupuri mai mici se foloseşte clauza GROUP BY. Folosirea acesteia returnează informaţii despre fiecare grup. Exemplu:

SELECT deptno, MAX(sal), MIN(sal) FROM emp GROUP BY deptno

Comanda de mai sus afişează salariul maxim şi salariul minim pentru fiecare departament. Reguli de folosire a clauzei GROUP BY: - în clauza GROUP BY nu se acceptă un alias de coloană; - toate câmpurile care apar în clauza SELECT trebuie să apară şi în clauza GROUP BY; - NU SE FOLOSESC funcţii de grup în clauza WHERE; - în clauza GROUP BY pot să apară şi coloane care nu sunt în clauza SELECT. Exemple: 1)SELECT deptno AS DEPARTAMENT, MAX(sal) FROM emp GROUP BY DEPARTAMENT Comanda de mai sus va genera o eroare deoarece nu respectă prima regulă. Comanda corectă este:

SELECT deptno AS DEPARTAMENT, MAX(sal) FROM emp GROUP BY deptno 2)SELECT deptno ,job, MAX(sal) FROM emp GROUP BY deptno Comanda de mai sus va genera o eroare deoarece nu respectă a doua regulă. Comanda corectă este:

SELECT deptno ,job, MAX(sal) FROM emp GROUP BY deptno,job 3) SELECT job, MAX(sal) FROM emp WHERE MAX(sal)>1500 GROUP BY job Comanda de mai sus va genera o eroare deoarece nu respectă a treia regulă. Comanda corectă se scrie cu ajutorul clauzei HAVING:

SELECT job, MAX(sal) FROM emp GROUP BY job HAVING MAX(sal)>1500

4)SELECT AVG(MAX(sal)) FROM emp GROUP BY deptno

CUPRINS

Clauza HAVING

Pentru a filtra grupurile obţinute cu ajutorul clauzei GROUP BY, se foloseşte clauza HAVING. Clauza HAVING funcţionează în mare ca şi clauza WHERE, diferenţa fiind că, HAVING este folosit pentru a exclude anumite grupuri din rezultat, nu rânduri cum face WHERE. Clauza HAVING poate fi folosită înainte de GROUP BY, însă este mai logic să fie folosită după. Ordinea de execuţie va rămâne aceeaşi. Exemplu: SELECT job, SUM(sal) FROM emp WHERE job!='SALESMAN' GROUP BY job HAVING SUM(sal) > 2500 ORDER BY SUM(sal) Comanda de mai sus afişează funcţiile şi salariul total pe fiecare funcţie, fără a lua in calcul angajaţii cu funcţia SALESMAN şi excluzând funcţiile cu suma salariilor sub 2500 cu ordonare după salariul total.

Subtotaluri

Pentru a efectua sinteze a datelor pe baza totalurilor parţiale folosim clauzele ROLLUP şi GROUPING. Clauza ROLLUP este inclusă în clauza GROUP BY, argumentele acesteia fiind coloanele de grupare. Clauza GROUPING este inclusă în clauza SELECT, argumentul acesteia fiind coloana de grupare. Rezultatul întors de această clauză este 1 atunci când coloana respectivă este inclusă într-un grup de agregare superior sau 0 pentru liniile din afara subtotalurilor.Exemplu: SELECT CASE WHEN GROUPING(job)=1 THEN 'Nr. total angajati' WHEN GROUPING(ename)=1 THEN 'Nr. angajati pe functia de ' ELSE ename END NUME, job FUNCTIA, COUNT(empno) NUMAR FROM EMP GROUP BY ROLLUP(job,ename) Deschide baza de date Oracle şi urmăreşte rezultatul afişat de interogarea de mai sus.

CUPRINS

Fişa nr. 2

Deschide baza de date Oracle şi urmăreşte rezultatul afişat de interogările de mai jos: 1) SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno 2) SELECT deptno, ROUND(AVG(sal),4) FROM emp GROUP BY deptno 3) SELECT AVG(MIN(sal)) FROM emp GROUP BY empno 4) SELECT deptno, SUM(sal) FROM emp GROUP BY deptno ORDER BY SUM(sal) DESC 5) SELECT job, ROUND(AVG(sal),4) FROM emp GROUP BY job HAVING AVG(sal)<2200 Exerciţii

1. Afişaţi salariul maxim, minim, suma salariilor şi salariul mediu pentru fiecare funcţie. Care este diferenţa între salariul maxim şi salariul minim pentru fiecare funcţie?

2. Determinaţi numărul de angajaţi cu aceeaşi funcţie.

3. Determinaţi numărul de manageri(Folosiţi câmpul MGR).

4. Afisati id-ul managerului si salariul celui mai slab platit angajat al acelui manager. Excludeti pe oricine al carui manager nu este cunoscut si orice grup unde salariul minim este 1000 sau mai putin. Sortati rezultatele in ordinea descrescatoare a salariilor minime.

5. Creaţi o interogare care să afişeze numele angajatului, funcţia, salariul total(SUBTOTAL) pentru acea funcţie şi totalul general obţinut.

6. Modificaţi interogarea de mai sus astfel încât, să afişeze numele angajatului, numărul departamentului, salariul total pe fiecare departament şi totalul general.

7. Modificaţi interogarea de mai sus astfel încât, să afişeze numele angajatului, numărul departamentului, funcţia, salariul total pe fiecare departament şi pe fiecare funcţie precum şi totalul general.

8. Afişaţi numărul total de angajaţi si apoi numărul de persoane angajate în anii 1980, 1981, 1982, 1983 si 1984. Pentru rezolvarea acestui exerciţiu se folosesc SUBINTEROGĂRI scalare (subinterogare ce returnează o singură linie/coloană(vezi mai jos rezolvarea).

SELECT (SELECT COUNT(empno) FROM emp) NR_TOTAL, (SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1980') AS "1980", (SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1981') AS "1981", (SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1982') AS "1982", (SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1983') AS "1983", (SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1984') AS "1984" FROM DUAL

Rezolvarea fişei nr. 2

CUPRINS

Fişa nr. 3

Deschide baza de date Oracle şi rezolvă interogările de mai jos:

1) Afişaţi numele departamentului şi numărul de angajaţi pentru fiecare departament. 2) Afişaţi numele departamentului şi valoarea totală a salariilor pentru fiecare

departament. 3) Modificaţi interogările de mai sus astfel încât să se afişeze şi totalul general1. 4) Creaţi o interogare care să afişeze numele departamentului, funcţia, salariul total

(SUBTOTAL) pentru acea funcţie şi totalul general obţinut.2 5) Modificaţi interogarea de mai sus astfel încât să elimine orice grup unde salariul total

este 2000 sau mai puţin.3 6) Afişaţi numărul de angajaţi pe oraşe. 7) Afişaţi următoarea propoziţie:IN DEPARTAMENTUL dname DIN ORASUL loc SUNT

numar ANGAJATI. VALOAREA TOTALA A SALARIILOR ACESTORA ESTE DE suma. Să se afişeze şi totalul general.

8) Să se afişeze numele departamentului şi primele două salarii maxime din fiecare departament.

9) Afişaţi numele managerilor şi numărul de angajaţi pe care îi are în subordine fiecare manager.

10) Modificaţi interogarea de mai sus astfel încât să afişeze pentru fiecare manager, numele acestuia, numele angajaţilor şi numărul lor.

11) Dacă salariul angajaţilor creşte la implinirea a 32 ani vechime cu 10%, afişati numărul de angajaţi care au astăzi această vechime şi valoarea noului salariu.

12) Presupunând că salariul se măreşte cu fiecare lună ce trece de la data angajării cu 0.15%, iar salariul stocat în baza de date e cel primit la data angajării, care a fost valoarea totală a salariilor la data de 1 ianuarie 2013 (afişaţi valoarea totală a salariilor iniţiale şi valoarea totală a salariilor la data de 01.01.2013, cu două zecimale) ?

13) Considerăm că salariul se măreşte cu fiecare an de vechime cu 1.8%, pentru cei ce nu câştigă comision şi cu 0.9% pe an pentru cei ce beneficiază de comision. Care este valoarea totală a salariilor în această lună pentru angajaţii care nu beneficiază de comision şi pentru angajaţii care au comision? 4

1 Se foloseşte clauza ROLLUP

2 Se folosesc clauzele ROLLUP şi GROUPING

3 Se adaugă clauza HAVING

4 Se recomandă folosirea subinterogărilor scalare

CUPRINS

Rezolvarea fişei nr. 1

1) SELECT ROUND(AVG(sal)), MAX(sal), MIN(sal), SUM(sal) FROM emp WHERE job='MANAGER'

2) SELECT MAX(TO_CHAR(hiredate,'day, dd month')),MIN(TO_CHAR(hiredate,'day, dd month')) FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1982'

3) SELECT MIN(ename), MAX(ename) FROM emp 4) SELECT COUNT(comm) FROM emp WHERE deptno=30 5) SELECT COUNT(DISTINCT deptno) FROM emp 6) SELECT COUNT(DISTINCT job) FROM emp 7) SELECT COUNT(empno) FROM emp WHERE TO_CHAR(hiredate,’yyyy’)=’1981’

Rezolvarea fişei nr. 2

1) SELECT job, MAX(sal), MIN(sal), SUM(sal), ROUND(AVG(sal)),MAX(sal)-MIN(sal) FROM emp GROUP BY job

2) SELECT job, COUNT(job) FROM emp GROUP BY job 3) SELECT COUNT(DISTINCT mgr) FROM emp 4) SELECT a.empno, MIN(b.sal) FROM emp a, emp b WHERE b.mgr=a.empno AND

a.sal>b.sal GROUP BY a.empno HAVING MIN(b.sal)>1000 ORDER BY MIN(b.sal) DESC 5) SELECT CASE WHEN GROUPING(job)=1 THEN 'TOTAL GENERAL' WHEN GROUPING(ename)=1 THEN 'TOTAL PENTRU FUNCTIA' ELSE ename END, job , SUM(sal) FROM emp GROUP BY ROLLUP(job,ename) 6) SELECT CASE WHEN GROUPING(deptno)=1 THEN 'TOTAL GENERAL' WHEN GROUPING(ename)=1 THEN 'TOTAL PENTRU DEPARTAMENTUL' ELSE ename END NUME, deptno , SUM(sal) FROM emp GROUP BY ROLLUP(deptno,ename) 7) SELECT CASE WHEN GROUPING(deptno)=1 THEN 'TOTAL GENERAL' WHEN GROUPING(job)=1 THEN 'TOTAL DEPARTAMENT' WHEN GROUPING(ename)=1 THEN 'TOTAL FUNCTIE ' ELSE ename END , deptno, job, SUM(SAL) FROM emp GROUP BY ROLLUP(deptno, job, ename)

Fişa nr. 2

Fişa nr.1

CUPRINS