Bases de données :
(encore) un peu plus loin dans SQL
Karën Fort (repris d’Alice Millour)
[Link]@[Link]
1 / 33
Sources d’inspiration
I il s’agit du cours d’Alice Millour (2020)
I [Link]
administrez-vos-bases-de-donnees-avec-mysql/
1966846-fonctions-dagregation
I [Link]
initiez-vous-a-lalgebre-relationnelle-avec-le-
langage-sql/4558491-ameliorez-vos-agregations-
grace-a-having
I [Link]
2 / 33
Retours sur le TD
Agréger les résultats
Filtrer les résultats après une agrégation
Les alias
Pour finir
3 / 33
Exercice 1
1. SELECT filiere FROM Programme (ou, sans doublon, SELECT
DISTINCT filiere FROM Programme)
2. SELECT auteur FROM Livres (ou trié, sans doublon : SELECT
DISTINCT auteur FROM Livres ORDER BY auteur)
3. SELECT titre FROM Livres WHERE nb_pages=150
4. SELECT nom, prenom FROM Etudiants WHERE prenom="Marie"
5. SELECT titre FROM Emprunts WHERE date_ret IS NULL
6. SELECT num_et FROM Emprunts WHERE date_ret IS NULL
4 / 33
NATURAL JOIN
une écriture simplifiée d’un INNER JOIN, dans le cas où les
colonnes ont :
I le même nom
I le même type
5 / 33
Exemple
7 SELECT prenom FROM Emprunts NATURAL JOIN Etudiants
WHERE date_ret IS NULL
vs
SELECT prenom FROM Emprunts INNER JOIN Etudiants ON
Emprunts.num_et=Etudiants.num_et WHERE date_ret IS
NULL
8 SELECT titre, date_emp FROM Livres NATURAL JOIN Emprunts
WHERE nb_pages=150
6 / 33
Dans ce cours
I aller plus loin dans le SELECT : compter les éléments, trouver
le minimum ou calculer la moyenne d’une colonne, etc.
I grouper les lignes qui partagent une même valeur pour faire
des statistiques plus fines sur les données
7 / 33
Retours sur le TD
Agréger les résultats
Compter en SQL
GROUP BY
Filtrer les résultats après une agrégation
Les alias
Pour finir
8 / 33
Retours sur le TD
Agréger les résultats
Compter en SQL
GROUP BY
Filtrer les résultats après une agrégation
Les alias
Pour finir
9 / 33
L’agrégation
I Requête sans agrégation : renvoie une liste = liste des lignes
sélectionnées par la requête
I Requête avec agrégation : renvoie une valeur = résultat d’une
fonction d’agrégation appliquée à une colonne
10 / 33
Fonction d’agrégation
permet (principalement) de calculer des statistiques sur les
données :
I COUNT(colonne) = compter les éléments not NULL
I MIN(colonne), MAX(colonne) = trouver les éléments minimum
et maximum d’une colonne
I SUM(colonne) = sommer les éléments d’une colonne
I AVG(colonne) = calculer la valeur moyenne des éléments d’une
colonne
Attention : il ne doit pas y avoir d’espace entre la commande SQL
et la colonne entre parenthèse
11 / 33
(rappel) requête sans agrégation
Dans la BD Enquete
SELECT filiere FROM Diplomes
12 / 33
Requête avec agrégation : COUNT(colonne)
Dans la BD Enquête
SELECT COUNT(filiere) FROM Diplomes
(avec DISTINCT) 4 filières
43 filières
différentes
13 / 33
Requête avec agrégation : autres exemples
Dans la BD Enquête
(à tester vous-même)
I SELECT MIN(nb_pages) FROM Livres
I SELECT MAX(nb_pages) FROM Livres
I SELECT SUM(nb_pages) FROM Livres
I SELECT AVG(nb_pages) FROM Livres
14 / 33
Retours sur le TD
Agréger les résultats
Compter en SQL
GROUP BY
Filtrer les résultats après une agrégation
Les alias
Pour finir
15 / 33
La clause GROUP BY
GROUP BY colonne
groupe les résultats d’une requête qui partagent la même valeur
dans la colonne spécifiée
SELECT filiere FROM Diplomes GROUP BY filiere
16 / 33
Faire des statistiques sur une table
combiner fonctions d’agrégation et GROUP BY
Compter les étudiants
I nombre de diplômés (43)
SELECT COUNT(num_et) FROM Diplomes
I nombre de diplômés par filière :
SELECT COUNT(num_et) FROM Diplomes GROUP BY filière
la fonction d’agrégation s’applique à chaque groupe de résultats
17 / 33
Faire des statistiques sur une table (2)
combiner fonctions d’agrégation et GROUP BY
Compter les coureurs (base : BDD_L3_ARMENTA)
I nombre de participations (16)
SELECT COUNT(NumeroCoureur) FROM Participer
I nombre de participations par coureur :
SELECT COUNT(NumeroCoureur) FROM Participer GROUP BY
NumeroCoureur
18 / 33
Rendre les statistiques lisibles
Champs à sélectionner
SELECT filiere, COUNT(num_et)
FROM Diplomes
GROUP BY filiere
dans une clause SELECT comprenant un GROUP BY, on ne peut
avoir que deux types d’éléments :
I un élément présent dans la clause GROUP BY (ou qui en
dépend fonctionnellement) : ici filiere
I une fonction d’agrégation : ici COUNT(num_et)
19 / 33
Rendre les statistiques lisibles
Champs à sélectionner
SELECT [Link], COUNT([Link])
FROM Participer
INNER JOIN Coureur ON [Link] =
[Link]
GROUP BY [Link]
20 / 33
Erreur classique
Champ mal sélectionné
le champ [Link]éalise :
I ne dépend pas fonctionnellement de [Link]
I n’est pas une fonction d’aggrégation
21 / 33
GROUP BY sur plusieurs champs
I nombre de diplômés par filière et par niveau :
SELECT filiere, niveau, COUNT(num_et)
FROM Diplomes
GROUP BY filiere, niveau
22 / 33
GROUP BY, exemples
(à tester vous-même)
I durée moyenne des cours par filière :
SELECT filiere, AVG(nb_heures)
FROM Programme
GROUP BY filiere
I première date d’obtention de diplôme par filière et par niveau :
SELECT filiere, niveau, MIN(date_d)
FROM Diplomes
GROUP BY filiere, niveau
23 / 33
Retours sur le TD
Agréger les résultats
Filtrer les résultats après une agrégation
Les alias
Pour finir
24 / 33
Filtrer les résultats : HAVING
HAVING condition
restreint les résultats à ceux qui respectent la condition
I la clause ‘WHERE condition’ restreint avant l’agrégation
I la clause ‘HAVING condition’ restreint après l’agrégation
25 / 33
Filtrer les résultats : HAVING
Si on cherche les livres qui ont été empruntés au moins 3 fois
depuis le 1er janvier 2012
I restriction sur la date d’emprunt avec WHERE
I restriction sur le nombre d’emprunts avec HAVING
SELECT titre, COUNT(date_emp)
FROM Emprunts
WHERE date_emp > "2012-01-01" # restriction sur la date d’emprunt
GROUP BY titre
HAVING COUNT(date_emp) >= 3 # restriction sur le nombre d’emprunts
26 / 33
Retours sur le TD
Agréger les résultats
Filtrer les résultats après une agrégation
Les alias
Pour finir
27 / 33
Clarifier les requêtes grâce aux alias
La clause AS
(optionnel) pour changer le nom de colonnes et de tables
I formater un résultat (alias de colonne) :
SELECT num_et AS "Numéro d’étudiant" FROM Etudiants
28 / 33
Clarifier les requêtes grâce aux alias
La clause AS
(optionnel) pour changer le nom de colonnes et de tables
I raccourcir les requêtes (alias de table) :
SELECT i.num_et, [Link], [Link] FROM Inscriptions AS i
I nommer des résultats de fonctions d’agrégation :
SELECT num_et, COUNT(annee) AS nb_insc
FROM Inscriptions
GROUP BY num_et
HAVING nb_insc = 1
29 / 33
Retours sur le TD
Agréger les résultats
Filtrer les résultats après une agrégation
Les alias
Pour finir
CQFR : Ce Qu’il Faut Retenir
TD
30 / 33
I maîtrise des fonctions d’agrégation,
de GROUP BY, de AS
I champs à sélectionner quand on
combine fonctions d’agrégation et
GROUP BY
31 / 33
Exercice (à faire)
1. Afficher les noms et prénoms des étudiants qui n’ont emprunté
aucun livre
2. Afficher les filières dont la somme des heures de cours par
semaine est inférieure à 10
32 / 33
Aide pour l’exercice 1
1. Afficher nom, prénom et dates d’emprunts pour chaque
étudiant
2. Grouper les noms et prénoms pour afficher le nombre
d’emprunts par étudiant
3. Filtrer les résultats pour ne garder que les étudiants n’ayant
emprunté aucun livre
33 / 33
