SHARE
TWEET

Untitled

a guest Oct 18th, 2019 91 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Exercice 2
  2.  
  3. -- GROUP BY Normal
  4. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  5. FROM exercice ex, prof p, etablissement et, notion n
  6. WHERE ex.proprietaire = p.idp
  7. AND p.rne = et.rne
  8. AND ex.idex = n.idexo
  9. GROUP BY et.localite, ex.niveau, p.annee_naissance, n.notion
  10.  
  11. -- GROUP BY ROLLUP
  12. -- reduit les dimensions
  13. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  14. FROM exercice ex, prof p, etablissement et, notion n
  15. WHERE ex.proprietaire = p.idp
  16. AND p.rne = et.rne
  17. AND ex.idex = n.idexo
  18. GROUP BY ROLLUP (et.localite, ex.niveau, p.annee_naissance, n.notion)
  19.  
  20. -- GROUP BY CUBE
  21. -- Donne toute les combinaisons possibles
  22. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  23. FROM exercice ex, prof p, etablissement et, notion n
  24. WHERE ex.proprietaire = p.idp
  25. AND p.rne = et.rne
  26. AND ex.idex = n.idexo
  27. GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)
  28.  
  29. -- GROUP BY GROUPING SETS
  30. -- Donne la mesure pour une unique dimension
  31. SELECT et.localite, ex.niveau, p.annee_naissance, n.notion, COUNT(ex.niveau)
  32. FROM exercice ex, prof p, etablissement et, notion n
  33. WHERE ex.proprietaire = p.idp
  34. AND p.rne = et.rne
  35. AND ex.idex = n.idexo
  36. GROUP BY GROUPING SETS (et.localite, ex.niveau, p.annee_naissance, n.notion)
  37.  
  38. -- DECODE
  39. SELECT et.localite, ex.niveau, p.annee_naissance, DECODE(n.notion, NULL , 'TOUTE NOTION', n.notion) , COUNT(ex.niveau)
  40. FROM exercice ex, prof p, etablissement et, notion n
  41. WHERE ex.proprietaire = p.idp
  42. AND p.rne = et.rne
  43. AND ex.idex = n.idexo
  44. GROUP BY CUBE (et.localite, ex.niveau, p.annee_naissance, n.notion)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top