Notes de cours & TP 5
[Link]égation de données à l’aide de
fonctions de groupe
Objectifs
Au terme de ce chapitre, l’étudiant saura :
➢ Générer de données agrégées à l’aide des fonctions de groupe : Min, Max,
Avg, Count et Sum.
➢ Écrire des requêtes avec les clauses : Group By & Having.
Durée estimative : 1,5h
5.1 NOTES DE COURS
5.1.1 DEFINITION DES FONCTIONS DE GROUPE
Les fonctions de groupe ou fonctions agrégatives opèrent sur des groupes pour donner un
résultat pour chaque groupe. On ne peut pas utiliser une fonction de groupe dans la clause
WHERE.
Les fonctions de groupe sont : AVG, SUM, MIN, MAX, COUNT, STDDEV, et VARIANCE. La
syntaxe générale est :
SELECT group_function (column),...
FROM table
[WHERE condition]
[ORDER BY column]
SELECT AVG (salary), MAX (salary), MIN (salary), SUM (salary),
COUNT (salary)
FROM Employees
SELECT COUNT (*) FROM Employees
Cette requête retourne 107. C’est le nombre de lignes dans la table EMPLOYEES.
SELECT COUNT (department_id) FROM Employees
Cette requête 106. C’est le nombre de lignes dans la table EMPLOYEES contenant une valeur
non nulle de department_id, car les fonctions de groupes ignorent les valeurs nulles.
2022/2023 - Ons BEN ROMDHANE & Adel DAHMANE 30
Notes de cours & TP 5 : Agrégation de données à l’aide de fonctions de groupe
SELECT COUNT (NVL (department_id, 0)) FROM employees
Cette requête retourne 107. Si department_id est nul NVL retourne 0, donc même les valeurs
nulles seront comptabilisées. La fonction NVL force les fonctions de groupes à inclure les
valeurs nulles.
SELECT COUNT (DISTINCT department_id) FROM employees
Elle retourne 11. C’est le nombre de valeurs distinctes et non nulles de department_id.
SELECT COUNT (DISTINCT NVL (department_id, 0)) FROM employees
Retourne 12. C’est le nombre de valeurs distinctes de department_id y compris les valeurs
nulles.
5.1.2 LA CLAUSE GROUP BY
SELECT column | expression | group_function(column|expression
[alias]),…
FROM table
[WHERE condition(s)]
[GROUP BY {col(s) |expr}]
[ORDER BY column]
La clause GROUP BY décompose un ensemble de lignes en des sous groupes. L'attribut de
regroupement qui figure devant GROUP BY est la clé commune partagée par les lignes de
chaque groupe. On peut par exemple décomposer les lignes de la table EMPLOYEES en des
groupes contenant chacun le même department_id, puis exécuter les fonctions de groupe sur
chaque groupe (employés du même département) pour calculer le salaire maximum dans
chaque département, le salaire minimum, le salaire moyen…
Tout élément de la liste SELECT doit être une fonction de groupe ou un attribut de
regroupement de la clause GROUP BY.
SELECT department_id, max (salary), count (*)
FROM Employees
GROUP BY department_id
ORDER BY department_id
La clause GROUP BY permet de partitionner un ensemble de données en groupes et permet
aussi à ces groupes d'être divisés en sous-groupes en utilisant plusieurs attributs de
regroupement.
SELECT department_id, job_id, max (salary), count (*)
FROM Employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id
2022/2023 - Ons BEN ROMDHANE & Adel DAHMANE 31
Notes de cours & TP 5 : Agrégation de données à l’aide de fonctions de groupe
5.1.3 LA CLAUSE HAVING
SELECT column | expression | group_function(column|expression
[alias]),…
FROM table
[WHERE condition(s)]
[GROUP BY {col(s) |expr}]
[HAVING group_condition(s)]
[ORDER BY column]
La clause HAVING ne peut être spécifiée que si la clause GROUP BY est présente. Elle est
évaluée après avoir effectué les regroupements et exécuté les fonctions de groupe.
Exemple
SELECT department_id, count (*)
FROM job_history
WHERE department_id in (50, 60, 80, 110)
GROUP BY department_id
HAVING count(*)>1
5.2 REQUETES
Dans chaque cas, on demande d’écrire une requête SQL pour l’opération considérée :
a) On veut afficher des informations sur les employés dont le job_id est SA_REP : le plus petit
salaire, le plus grand salaire, les dates de recrutement du premier employé et du dernier
employé.
b) Afficher la moyenne des salaires par département.
c) Afficher pour chaque département, son numéro et la somme des commissions de ses
employés. On ne considère pas les commissions nulles.
d) Afficher pour chaque département, son numéro, ses job_id et la somme des commissions
des employés dans chaque job_id. On ne considère pas les commissions nulles.
2022/2023 - Ons BEN ROMDHANE & Adel DAHMANE 32
Notes de cours & TP 5 : Agrégation de données à l’aide de fonctions de groupe
e) Obtenir le nombre des employés qui ont quitté leur emploi, regroupés par l'année de
sortie. Leurs emplois (job_id) sont également nécessaires. Le résultat doit être classé par
ordre décroissant du nombre d'employés dans chaque groupe puis par ordre croissant de
l’année de sortie.
f) A partir de la table EMPLOYEES, afficher le salaire moyen et le nombre d’employés de
chaque job_id. On exclut les job_id ayant un salaire moyen inférieur ou égal à 10000 et un
nombre d’employés égal à 1. Trier le résultat selon l’ordre croissant du salaire moyen.
g) La même requête précédente mais on exclut les job_id qui contiennent ‘MAN’.
2022/2023 - Ons BEN ROMDHANE & Adel DAHMANE 33