PBD_Lab6_CorrectV

2. Scrieti o interogare care sa afiseze, simultan, urmatoarele informatii pentru acei angajati al caror
identificator pentru manager este mai mic de 120:
a. identificatorul managerului
b. - identificatorul jobului
c. - salariul total pentru toti angajatii subordonati unui manager
d. - un total al tuturor salariilor

SELECT manager_id, job_id,first_name, SUM (salary)
 FROM employees
 WHERE manager_id< 120
 GROUP BY ROLLUP (manager_id,job_id,first_name) ;

3.Modificati interogarea de la punctul 4 astfel incat sa se determine daca o valoare NULL din
coloanele corespunzatoare expresiei din clauza GROUP BY este produsa de o operatie ROLLUP.

SELECT
DECODE (GROUPING (department_id),1, 'NULL Caused by rollup function', department_id),
 DECODE (GROUPING (job_id),1, 'NULL Caused by rollup function', job_id)
, SUM (salary),
GROUPING (department_id) GRP_DEPT,
 GROUPING (job_id) GRP_JOB
 FROM employees
 WHERE department_id< 60
 GROUP BY ROLLUP (department_id, job_id) ;

4. Utilizand clauza GROUPING SETS, scrieti o interogare care sa realizeze urmatoarele grupari:
a. department_id, manager_id, job_id
b. department_id, job_id
c. manager_id, job_id
Interogarea va calcula suma salariilor pentru fiecare din aceste grupari.

SELECT department_id, manager_id, job_id, SUM (salary)
 FROM employees
 GROUP BY GROUPING SETS (department_id, manager_id,job_id) ;


SELECT department_id, job_id, SUM (salary)
 FROM employees
 GROUP BY GROUPING SETS (department_id, job_id) ;

SELECT manager_id, job_id, SUM (salary)
 FROM employees
 GROUP BY GROUPING SETS (manager_id,job_id) ;