Baze de date-Anul 2

Laborator 5 SQL

Operatorii ROLLUP şi CUBE. Clauza GROUPING SETS. Funcţia GROUPING.

Subcereri corelate.
Cereri ierarhice.
Analiza top-n.
Clauza WITH.

I. [Operatorii ROLLUP şi CUBE. Clauza GROUPING SETS. Funcţia GROUPING.]

Am introdus, în laboratorul 4, operatorii ROLLUP şi CUBE. Aceştia se utilizează în cadrul
clauzei GROUP BY pentru generarea de linii superagregat.
 Reamintim că:
 GROUP BY ROLLUP (expr_1, expr_2, …, expr_n) generează n+1 tipuri de linii,
corespunzătoare următoarelor grupări:
o GROUP BY (expr_1, expr_2, …, expr_n-1, expr_n)
o GROUP BY (expr_1, expr_2, …, expr_n-1)
o …
o GROUP BY (expr_1, expr_2)
o GROUP BY (expr_1)
o GROUP BY () – corespunzător absenţei clauzei GROUP BY şi deci, calculului
funcţiilor grup din cerere pentru întreg tabelul.
Obs:
 Lista de expresii care urmează operatorului ROLLUP este parcursă de la dreapta la stânga,
suprimându-se câte o expresie .
 O cerere în care apare un astfel de operator este echivalentă cu reuniunea (UNION ALL) a
n+1 cereri.
 GROUP BY CUBE (expr_1, expr_2, …, expr_n) generează 2n tipuri de linii,
corespunzătoare tuturor combinaţiilor posibile de expresii din lista.
 Pentru determinarea modului ìn care a fost obţinută o valoare totalizatoare cu ROLLUP sau
CUBE, se utilizează funcţia:
 GROUPING(expresie)
Aceasta întoarce:
- valoarea 0, dacă expresia a fost utilizată pentru calculul valorii agregat
- valoarea 1, dacă expresia nu a fost utilizată.
 Dacă se doreşte obţinerea numai a anumitor grupări superagregat, acestea pot fi precizate prin
intermediul clauzei :
 GROUPING SETS ((expr_11, expr_12, …, expr_1n), (expr_21, expr_22, …expr_2m), …)

Exerciţii:
1. a) Să se afişeze numele departamentelor, titlurile job-urilor şi valoarea medie a salariilor, pentru:
- fiecare departament şi, în cadrul său pentru fiecare job;
- fiecare departament (indiferent de job);
- întreg tabelul.
SELECT department_name, job_title, AVG(salary) medie
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY ROLLUP(department_name, job_title);
 2

b) Analog cu a), afişând şi o coloană care arată intervenţia coloanelor department_name,

job_title, în obţinerea rezultatului.
COLUMN jt FORMAT ‘99’
COLUMN dn FORMAT ‘99’
SELECT department_name, job_title, AVG(salary) medie,
GROUPING(department_name) dn, GROUPING(job_title) jt
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY ROLLUP(department_name, job_title);
2. a) Să se afişeze numele departamentelor, titlurile job-urilor şi valoarea medie a salariilor, pentru:
- fiecare departament şi, în cadrul său pentru fiecare job;
- fiecare departament (indiferent de job);
- fiecare job(indiferent de departament)
- întreg tabelul.
SELECT department_name, job_title, AVG(salary) medie
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY CUBE(department_name, job_title);
b) Cum intervin coloanele în obţinerea rezultatului? Să se afişeze ’Dep’, dacă departamentul a
intervenit în agregare, şi ‘Job’, dacă job-ul a intervenit în agregare.
SELECT department_name, job_title, AVG(salary) medie,
DECODE(GROUPING(department_name), 0, ‘Dep’) dep,
DECODE(GROUPING(job_title), 0, ‘Job’) job
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY CUBE(department_name, job_title);
3. Să se afişeze numele departamentelor, numele job-urilor, codurile managerilor, maximul şi
suma salariilor pentru:
- fiecare departament şi, în cadrul său, fiecare job;
- fiecare job şi, în cadrul său, pentru fiecare manager;
- întreg tabelul.
SELECT department_name, job_title, e.manager_id, MAX(salary) “Maxim”,
SUM(salary) “Suma”
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY GROUPING SETS ((department_name, job_title),
(job_title, e.manager_id), ());
4. [HAVING fara GROUP BY] Să se afişeze salariul maxim al angajatilor doar daca acesta este
mai mare decât 15000.
SELECT MAX(salary)
FROM employees
HAVING MAX(salary)>15000;

II. [Subcereri corelate (sincronizate)]

O subcerere (cerere imbricată sau încuibărită) corelată poate avea forma următoare:
SELECT nume_coloană_1[, nume_coloană_2 …]
FROM nume_tabel_1 extern
 3

WHERE expresie operator

(SELECT nume_coloană_1 [, nume_coloană_2 …]
FROM nume_tabel_2
WHERE expresie_1 = extern.expresie_2);
Modul de execuţie este următorul :
 cererea externă determină o linie candidat;
 cererea internă este executată utilizând valoarea liniei candidat;
 valorile rezultate din cererea internă sunt utilizate pentru calificarea sau descalificarea
liniei candidat;
 paşii precedenţi se repetă până când nu mai există linii candidat.
Obs: operator poate fi:
 single-row operator (>, =, >=, <, <>, <=), care poate fi utilizat dacă subcererea
returnează o singură linie;
 multiple-row operator (IN, ANY, ALL), care poate fi folosit dacă subcererea returnează
mai mult de o linie.
Obs: O subcerere (corelată sau necorelată) poate apărea în clauzele:
 SELECT
 FROM (vezi laboratorul 4)
 WHERE
 HAVING (vezi laboratorul 4)
 START WITH (vezi mai jos – la cereri ierarhice)

Operatorul EXISTS
 În instrucţiunile SELECT imbricate, este permisă utilizarea oricărui operator logic.
 Pentru a testa dacă valoarea recuperată de cererea externă există în mulţimea valorilor
regăsite de cererea internă corelată, se poate utiliza operatorul EXISTS. Dacă subcererea
returnează cel puţin o linie, operatorul returnează valoarea TRUE. În caz contrar, va fi returnată
valoarea FALSE.
 Operatorul EXISTS asigură că nu mai este continuată căutarea în cererea internă după ce
aceasta regăseşte o linie.

Exerciţii:
5. a) Să se afişeze informaţii despre angajaţii al căror salariu depăşeşte valoarea medie a
salariilor colegilor săi de departament.
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);
b) Analog cu cererea precedentă, afişându-se şi numele departamentului şi media salariilor
acestuia şi numărul de angajaţi.
Soluţia 1 (subcerere necorelată în clauza FROM):

SELECT last_name, salary, e.department_id, department_name, sal_med, nr_sal

FROM employees e, departments d, (SELECT department_id, AVG(salary) sal_med,
COUNT(*) nr_sal
FROM employees
GROUP BY department_id) sm
WHERE e.department_id = d.department_id
AND d.department_id = sm.department_id
 4

AND salary > (SELECT AVG(salary)

FROM employees
WHERE department_id = e.department_id);
Soluţia 2 (subcerere corelată în clauza SELECT):

SELECT last_name, salary, e. department_id, department_name,

(SELECT AVG(salary)
FROM employees
WHERE department_id = e. department_id) “salariu mediu”,
(SELECT COUNT(*)
FROM employees
WHERE department_id = e. department_id) “Nr angajati”
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);

6. Să se afişeze numele şi salariul angajaţilor al căror salariu este mai mare decât salariile medii
din toate departamentele.
SELECT last_name, salary
FROM employees
WHERE salary > ALL (SELECT AVG(salary)
FROM employees
GROUP BY department_id);
Sau:
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT MAX( AVG(salary) )
FROM employees
GROUP BY department_id);

7. Sa se afiseze numele si salariul celor mai prost platiti angajati din fiecare departament.
Soluţia 1 (cu sincronizare):
SELECT last_name, salary, department_id
FROM employees e
WHERE salary = (SELECT MIN(salary)
FROM employees
WHERE department_id = e.department_id);
Soluţia 2 (fără sincronizare):
SELECT last_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id);
Soluţia 3: Subcerere în clauza FROM (temă)
8. Pentru fiecare departament, să se obtina numele salariatului avand cea mai mare vechime din
departament. Să se ordoneze rezultatul după numele departamentului.
SELECT last_name, hire_date, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
 5

AND hire_date = (SELECT MIN(hire_date)

FROM employees
WHERE department_id = e.department_id)
ORDER BY 3;
9. Sa se obtina numele salariatilor care lucreaza intr-un departament in care exista cel putin 1
angajat cu salariul egal cu salariul maxim din departamentul 30.
SELECT last_name, salary
FROM employees e
WHERE EXISTS (SELECT *
FROM employees
WHERE e.department_id = department_id
AND salary = (SELECT MAX(salary)
FROM employees
WHERE department_id =30));
10. Sa se obtina numele primilor 3 angajati avand salariul maxim. Rezultatul se va afişa în ordine
crescătoare a salariilor.
Solutia 1:
SELECT last_name, salary
FROM employees e
WHERE 2>= (SELECT COUNT(*)
FROM employees
WHERE salary >[Link])
ORDER BY salary;
Solutia 2: vezi analiza top-n (mai jos)

11. Să se afişeze codul, numele şi prenumele angajaţilor care au cel puţin doi subalterni.
SELECT employee_id, last_name, first_name
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM employees
WHERE manager_id = e.employee_id);

12. Să se determine locaţiile în care se află cel puţin un departament.

SELECT location_id, city
FROM locations l
WHERE EXISTS (SELECT 'x'
FROM departments
WHERE location_id = l.location_id);
Obs: Întrucât nu este necesar ca instrucţiunea SELECT interioară să returneze o anumită valoare,
se poate selecta o constantă (‘x’, ‘’, 1 etc.). De altfel, din punct de vedere al performanţei,
selectarea unei constante asigură mai multă rapiditate decât selectarea unei coloane.
Obs: Ca alternativă a lui EXISTS, poate fi utilizat operatorul IN. Exemplul precedent poate fi
rezolvat prin instrucţiunea următoare:
SELECT location_id, city
FROM locations
WHERE location_id IN (SELECT location_id
FROM departments);
13. Să se determine departamentele în care nu există nici un angajat.
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'x'
FROM employees
 6

WHERE department_id = d.department_id);

Obs: Acest exemplu poate fi rezolvat şi printr-o subcerere necorelată, utilizând operatorul NOT IN
(vezi şi laboratorul 3). Atenţie la valorile NULL! (fie puneţi condiţia IS NOT NULL în subcerere, fie
utilizaţi funcţia NVL) :
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);

II. [Subcereri ierarhice]

 Clauzele START WITH şi CONNECT BY se utilizează în formularea cererilor ierarhice.
 START WITH specifică o condiţie care identifică liniile ce urmează să fie considerate ca
rădăcini ale cererii ierarhice respective. Dacă se omite această clauză, sistemul Oracle
utilizează toate liniile din tabel drept linii rădăcină.
 CONNECT BY specifică o condiţie care identifică relaţia dintre liniile „părinte“ şi „copil“ ale
ierarhiei. Condiţia trebuie să conţină operatorul PRIOR pentru a face referinţă la linia „părinte“.
 Operatorul PRIOR face referinţă la linia „părinte“. Plasarea acestui operator determină direcţia
interogării, dinspre „părinte“ spre „copil“ (top-down) sau invers (bottom-up). Traversarea top-
down, respectiv bottom-up a arborelui se realizează prin specificări de forma următoare:
Top-down: CONNECT BY PRIOR cheie_parinte = cheie_copil;
Bottom-up: CONNECT BY PRIOR cheie_copil = cheie_parinte;
Obs: Operatorul PRIOR poate fi plasat în faţa oricărui membru al condiţiei specificate în clauza
CONNECT BY.
Obs: Liniile „părinte“ ale interogării sunt identificate prin clauza START WITH. Pentru a găsi liniile
„copil“, server-ul evaluează expresia din dreptul operatorului PRIOR pentru linia „părinte“, şi
cealaltă expresie pentru fiecare linie a tabelului. Înregistrările pentru care condiţia este adevărată
vor fi liniile „copil“. Spre deosebire de START WITH, în clauza CONNECT BY nu pot fi utilizate
subcereri.
 Pseudocoloana LEVEL poate fi utilă într-o cerere ierarhică. Aceasta
determină lungimea drumului de la rădăcină la un nod.

Exerciţii:
14. Să se afişeze codul, numele, data angajării, salariul şi managerul pentru:
a) subalternii directi ai lui De Haan;
SELECT employee_id, last_name, hire_date, salary, manager_id
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE LOWER(last_name)='de haan');
b) ierarhia arborescenta de sub De Haan.
SELECT employee_id, last_name, hire_date, salary, manager_id
FROM employees
START WITH employee_id=( SELECT employee_id
FROM employees
WHERE LOWER(last_name)='de haan')
CONNECT BY manager_id = PRIOR employee_id;
Obs: Traversarea precedentă este top-down. Faceţi modificarea necesară obtinerii unei traversari
bottom-up. Interpretaţi rezultatul.
 7

15. Să se obţină ierarhia şef-subaltern, considerând ca rădăcină angajatul având codul 114.
SELECT employee_id, last_name, hire_date, salary, manager_id, LEVEL
FROM employees
START WITH employee_id=114
CONNECT BY manager_id = PRIOR employee_id;
16. Scrieti o cerere ierarhica pentru a afisa codul salariatului, codul managerului si numele
salariatului, pentru angajatii care sunt cu 2 niveluri sub De Haan. Afisati, de asemenea, nivelul
angajatului în ierarhie.
SELECT employee_id, last_name, hire_date, salary, manager_id, LEVEL
FROM employees
WHERE LEVEL=3
START WITH employee_id=( SELECT employee_id
FROM employees
WHERE LOWER(last_name)='de haan')
CONNECT BY manager_id = PRIOR employee_id;
17. Pentru fiecare linie din tabelul EMPLOYEES, se va afisa o structura arborescenta in care va
apărea angajatul, managerul său, managerul managerului etc. Coloanele afişate vor fi: codul
angajatului, codul managerului, nivelul în ierarhie (LEVEL) si numele angajatului. Se vor folosi
indentari.
Obs: Pentru formatarea afişării coloanei nume din cerere, daţi comenzile SQL*Plus următoare:
SET LINESIZE 100
COLUMN name FORMAT a25;

SELECT employee_id, manager_id, LEVEL, last_name,

LPAD(last_name, length(last_name)+level*2-2, '_') name
FROM employees
CONNECT BY employee_id=prior manager_id;
18. Să se afişeze ierarhia de sub angajatul având salariul maxim, reţinând numai angajaţii al
căror salariu este mai mare de 5000. Se vor afişa codul, numele, salariul, nivelul din ierarhie şi
codul managerului.
SELECT employee_id, last_name, salary, LEVEL, manager_id
FROM employees
START WITH salary=( SELECT MAX(salary)
FROM employees)
CONNECT BY PRIOR employee_id = manager_id
AND salary > 5000 ;
Obs: În clauza CONNECT BY, coloana employee_id este evaluată pentru linia „părinte“, iar
coloanele manager_id şi salary sunt evaluate pentru linia „copil“. Pentru a introduce, de exemplu,
conditia ca salariul managerilor sa fie mai mare decât 15000, se scrie:
PRIOR salary > 15000

III. [Clauza WITH]

 Cu ajutorul clauzei WITH se poate defini un bloc de cerere înainte ca acesta să fie utilizat
într-o interogare.
 Clauza permite reutilizarea aceluiaşi bloc de cerere într-o instrucţiune SELECT complexă.
Acest lucru este util atunci când o cerere face referinţă de mai multe ori la acelaşi bloc de cerere,
care conţine operaţii join şi funcţii agregat.

Exerciţii:
 8

19. Utilizând clauza WITH, să se scrie o cerere care afişează numele departamentelor şi
valoarea totală a salariilor din cadrul acestora. Se vor considera departamentele a căror valoare
totală a salariilor este mai mare decât media valorilor totale ale salariilor tuturor angajatilor.
WITH val_dep AS (SELECT department_name, SUM(salary) AS total
FROM departments d, employees e
WHERE d.department_id = e.department_id
GROUP BY department_name),
val_medie AS (SELECT SUM(total)/COUNT(*) AS medie
FROM val_dep)
SELECT *
FROM val_dep
WHERE total > (SELECT medie
FROM val_medie)
ORDER BY department_name;
20. Să se afişeze ierarhic codul, prenumele şi numele (pe aceeaşi coloană), codul job-ului şi data
angajării, pornind de la subordonaţii direcţi ai lui Steven King care au cea mai mare vechime.
Rezultatul nu va conţine angajaţii în anul 1970.
WITH emp_sk AS
(SELECT employee_id, hire_date
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE INITCAP(last_name) = 'King'
AND INITCAP(first_name) = 'Steven'))
SELECT employee_id, INITCAP(first_name) ||’ ‘||UPPER(last_name),
job_id, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'yyyy') != 1970
START WITH (employee_id, hire_date) IN
(SELECT employee_id, hire_date
FROM emp_sk
WHERE hire_date = (SELECT MIN(hire_date)
FROM emp_sk))
CONNECT BY PRIOR employee_id = manager_id;

IV . [Analiza top-n]
Pentru aflarea primelor n rezultate ale unei cereri, este utilă pseudocoloana ROWNUM. Aceasta
returnează numărul de ordine al unei linii în rezultat.

Exerciţii:
21. Să se detemine primii 10 cei mai bine plătiţi angajaţi.
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC)
WHERE ROWNUM < 11;
22. Să se determine cele mai prost plătite 3 job-uri, din punct de vedere al mediei salariilor.
SELECT job_title, sal_med
FROM (SELECT job_title, AVG(salary) sal_med
FROM jobs j, employees e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY sal_med)
WHERE ROWNUM <= 3;
 9

IV.[Exerciţii – utilizarea alternativă a funcţiei DECODE sau a structurii CASE; din nou NVL şi
NVL2; COALESCE; NULLIF]
Obs:
 NVL(a, b) – întoarce a, dacă a este NOT NULL, altfel întoarce b;
 NVL2(a, b, c) - întoarce b, dacă a este NOT NULL, altfel întoarce c;
 COALESCE (expr_1, expr_2, …expr_n) – întoarce prima expresie NOT NULL din listă;
 NULLIF(a, b) – întoarce a, dacă a!=b; altfel întoarce NULL ;
 DECODE (expresie, val_1, val_2, val_3, val_4, …., val_2n-1, val_2n, default) – dacă expresie
= val_1, întoarce val_2; dacă expresie = val_3, întoarce val_4; …; altfel întoarce default.
 DECODE este echivalent cu CASE, a cărui structură este:
CASE expresie
WHEN val_1 THEN val_2
WHEN val_3 THEN val_4
…
ELSE default
END
CASE poate avea si forma:
CASE
WHEN expr_logica_1 THEN val_2
WHEN expr_logica_3 THEN val_4
…
ELSE default
END

23. Să se afişeze informaţii despre departamente, în formatul următor: „Departamentul

<department_name> este condus de {<manager_id> | nimeni} şi {are numărul de salariaţi <n> | nu
are salariati}“.
SELECT 'Departamentul ' || department_name || ' este condus de '
|| NVL(TO_CHAR(manager_id), ‘nimeni’)
|| NVL2(TO_CHAR(nr_sal),
' şi are numărul de salariati '|| nr_sal ,
' şi nu are salariati.') "Detalii dep"
FROM departments d, (SELECT department_id, COUNT(*) nr_sal
FROM employees
GROUP BY department_id) ns
WHERE d.department_id = ns.department_id(+);
24. Să se afişeze numele, prenumele angajaţilor şi lungimea numelui pentru înregistrările în care
aceasta este diferită de lungimea prenumelui.
SELECT last_name, first_name,
NULLIF (LENGTH(last_name), LENGTH(first_name)) Rezultat
FROM employees;
25. Să se afişeze numele, data angajării, salariul şi o coloană reprezentând salariul după ce se
aplică o mărire, astfel: pentru salariaţii angajaţi în 1989 creşterea este de 20%, pentru cei angajaţi
în 1990 creşterea este de 15%, iar salariul celor angajaţi în anul 1991 creşte cu 10%. Pentru
salariaţii angajaţi în alţi ani valoarea nu se modifică.
SELECT last_name, hire_date, salary,
CASE TO_CHAR(hire_date, 'yyyy')
WHEN '1989' THEN salary * 1.20
WHEN '1990' THEN salary * 1.15
WHEN '1991' THEN salary * 1.10
ELSE salary
END "Salariu marit"
 10

FROM employees;
Instrucţiunea din acest exemplu poate fi rescrisă utilizând funcţia DECODE în modul
următor:
SELECT last_name, hire_date, salary,
DECODE (TO_CHAR(hire_date, 'yyyy'),
'1989', salary * 1.20,
'1990', salary * 1.15,
'1991', salary * 1.10,
salary) "Salariu marit"
FROM employees;

26. Să se afişeze:
- suma salariilor, pentru job-urile care incep cu litera S;
- media generala a salariilor, pentru job-ul avand salariul maxim;
- salariul minim, pentru fiecare din celelalte job-uri.
SELECT job_id,
( CASE
WHEN UPPER(job_id) LIKE ‘S%’ THEN SUM(salary)
WHEN job_id= (SELECT job_id
FROM employees
WHERE salary =(SELECT MAX(salary)
FROM employees))
THEN (SELECT AVG(salary) FROM employees)
ELSE MIN(salary)
END) calcul
FROM employees
GROUP BY job_id2;
! Scrieţi şi varianta cu DECODE.