/* EXEMPLE */
-- extensia ROLLUP

-- Afisati numele departamentului, numele meseriei, salarul minim, salarul maxim si media
-- salarului pentru meserie, respectiv departament. Sa se afiseze si valorile agregate la nivel de
-- meserie, la nivel de department si la nivel general. Ordonati liniile dupa numele departamentului
-- si apoi dupa numele meseriei.
SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP(dname, job)
ORDER BY dname, job;

-- doar cu clauza GROUP BY
SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname, job
ORDER BY dname, job;

-- ROLLUP partial
SELECT loc, dname, job, AVG(sal), MIN(sal), MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc, ROLLUP(dname, job);

-- extensia CUBE

-- Afisati numele departamentului, numele meseriei, precum si toate valorile pentru salarul
-- minim, salarul maxim si media salarului pentru meserie, respectiv departament. Sa se afiseze si
-- valorile agregate la nivel de meserie, la nivel de department, la nivel de meserie si departament,
-- precum si la nivel general.
SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE(dname, job);

-- CUBE partial = ROLLUP() pentru toate combinatiile de dimensiuni
SELECT loc, dname, job, AVG(sal), MIN(sal), MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY loc, CUBE(dname, job);

-- GROUPING -> pentru a afla daca valorile de NULL provin din ROLLUP / CUBE sau din tabela initiala
SELECT dname, job, ROUND(AVG(sal), 2), MIN(sal), MAX(sal),
    GROUPING(dname) AS gdept, GROUPING(job) AS gjob
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP(dname, job);

SELECT dname, job, AVG(sal), MIN(sal), MAX(sal),
    GROUPING(dname) AS gdept, GROUPING(job) AS gjob
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE(dname, job)
HAVING GROUPING(dname) = 1 OR GROUPING(job) = 1;

-- GROUPING_ID -> pentru a afla nivelul de agregare
SELECT dname, job, AVG(sal), MIN(sal), MAX(sal),
    GROUPING(dname) AS gdept, GROUPING(job) AS gjob,
    GROUPING_ID(dname, job) AS nivel
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE(dname, job)
ORDER BY nivel;

-- GROUP_ID -> pentru a afla duplicatele
SELECT dname, job, AVG(sal), 2,
    GROUPING(dname) AS gdept, GROUPING(job) AS gjob,
    GROUPING_ID(dname, job) AS nivel, GROUP_ID()
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname, ROLLUP(dname, job)
ORDER BY nivel;

-- GROUPING SETS
-- Calculati salarul mediu pentru doua grupuri de date: department / job si job/manager
SELECT dname, job, mgr, avg(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS((dname, job), (job, mgr));

/* EXERCITII */
-- 1
SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
FROM emp JOIN salgrade
ON emp.sal BETWEEN losal AND hisal -- gradul de salarizare = non-equijoin intre tabelele "emp" si "salgrade"
GROUP BY ROLLUP(job, grade); -- calculul de subtotaluri -> ROLLUP()

-- 2
SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
FROM emp JOIN salgrade
ON emp.sal BETWEEN losal AND hisal
GROUP BY CUBE(job, grade); -- subtotaluri intre toate combinatiile de cele 2 dimensiuni = in loc de ROLLUP() folosim CUBE()

-- 3
SELECT e2.ename AS manager, e1.job, SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- pentru a afla managerul, facem self-join cu tabela "emp"
GROUP BY ROLLUP(e2.ename, e1.job); -- e2.ename = manager

-- 4
-- pentru a inlocui valorile de NULL folosim functia NVL()
SELECT NVL(e2.ename, 'Total ename') AS manager, NVL(e1.job, 'Total job') AS job,
    SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno
GROUP BY ROLLUP(e2.ename, e1.job);

-- 5
SELECT NVL(d.dname, 'Total') AS dname, NVL(e1.job, 'Total') AS job,
    NVL(e2.ename, 'Total') AS manager, NVL(TO_CHAR(s.grade), 'Total') AS grade,
    SUM(e1.sal),
    GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) AS nivel_agregare -- functia GROUPING_ID() ne da nivelul de agregare
FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- SELF JOIN intre emp si emp (pentru a afla manager-ul)
    JOIN dept d ON e1.deptno = d.deptno -- EQUIJOIN intre emp si dept
    JOIN salgrade s ON e1.sal BETWEEN losal AND hisal -- aflarea gradului de salarizare: NON-EQUIJOIN intre emp si salgrade
GROUP BY GROUPING SETS(ROLLUP(d.dname, e1.job), ROLLUP(e2.ename, s.grade))
HAVING GROUP_ID() = 0 -- evitarea duplicatelor -> eliminam inregistrarile cu GROUP_ID() nenul
ORDER BY GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) ASC;

-- 6
SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id) -- JOIN cu tabela "customers" ca sa putem afla "country_id"
    JOIN countries c ON (cust.country_id = c.country_id)
    -- sales = tabela de fapte, restul sunt tabele de dimensiune
WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- aplicam filtrarile cerute in enunt
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, cust.country_id); -- subtotalurile pe coloanele cerute

-- 7
SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN countries c ON (cust.country_id = c.country_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
GROUP BY ch.channel_desc, ROLLUP(t.calendar_month_desc, cust.country_id);
-- singura modificare este in GROUP BY, unde scoatem campul "channel_desc" din ROLLUP(), pentru a obtine gruparea partiala

-- 8
SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN countries c ON (cust.country_id = c.country_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
GROUP BY CUBE(ch.channel_desc, t.calendar_month_desc, cust.country_id);
-- modificare fata de problema 6: folosim CUBE() in loc de ROLLUP(), pentru ca vrem subtotaluri pentru toate combinatiile de dimensiuni

SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
-- valorile NULL se trateaza cu NVL()
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN countries c ON (cust.country_id = c.country_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
GROUP BY CUBE(ch.channel_desc, cust.country_id);
-- nu mai luam in considerare luna in calculul subtotalurilor (stergem si din CUBE() si din SELECT)

-- 9
SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN countries c ON (cust.country_id = c.country_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
GROUP BY ROLLUP(cust.country_id, ch.channel_desc); -- doar totalurile pe tara, respectiv pe canal (ROLLUP() + campuri inversate fata de problema 8)

-- 10
SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
    JOIN times t ON (s.time_id = t.time_id)
    JOIN customers cust ON (s.cust_id = cust.cust_id)
    JOIN countries c ON (cust.country_id = c.country_id)
WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
    AND t.calendar_month_desc IN ('2000-09', '2000-10')
    AND cust.country_id IN ('UK', 'US')
    -- in loc de ROLLUP() folosim GROUPING SETS pentru subtotaluri pe liste de grupuri specificate
GROUP BY GROUPING SETS((ch.channel_desc, t.calendar_month_desc, cust.country_id),
            (ch.channel_desc, cust.country_id),
            (t.calendar_month_desc, cust.country_id));