-- Exercice 2

-- GROUP BY Normal
SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
FROM exercice ex, prof p, etablissement et, notion n
WHERE ex.proprietaire = p.idp
AND p.rne = et.rne
AND ex.idex = n.idexo
GROUP BY et.localite, ex.niveau, p.annee_naissance, n.notion

-- GROUP BY ROLLUP
-- reduit les dimensions
SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
FROM exercice ex, prof p, etablissement et, notion n
WHERE ex.proprietaire = p.idp
AND p.rne = et.rne
AND ex.idex = n.idexo
GROUP BY ROLLUP (et.localite, ex.niveau, p.annee_naissance, n.notion)

-- GROUP BY CUBE
-- Donne toute les combinaisons possibles
SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
FROM exercice ex, prof p, etablissement et, notion n
WHERE ex.proprietaire = p.idp
AND p.rne = et.rne
AND ex.idex = n.idexo
GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)

-- GROUP BY GROUPING SETS
-- Donne la mesure pour une unique dimension
SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
FROM exercice ex, prof p, etablissement et, notion n
WHERE ex.proprietaire = p.idp
AND p.rne = et.rne
AND ex.idex = n.idexo
GROUP BY GROUPING SETS (et.localite, ex.niveau, p.annee_naissance, n.notion)

-- DECODE
SELECT et.localite, ex.niveau, p.annee_naissance, DECODE(n.notion, NULL , 'TOUTE NOTION', n.notion) , COUNT(ex.niveau)
FROM exercice ex, prof p, etablissement et, notion n
WHERE ex.proprietaire = p.idp
AND p.rne = et.rne
AND ex.idex = n.idexo
GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)