Bases de données
Cours 4
Requêtes SQL (suite)
Marie Pelleau
[Link]@[Link]
5 décembre 2023
1 / 39
Notations
1 Notations et exemple fil-rouge
Exemple : bibliothèque
Notations
2 Langage de manipulation des données
3 Agrégation et regroupement
4 SQL : Jointures avancées
2 / 39
Notations
Exemple fil-rouge : bibliothèque
Attributs des entités
Chaque abonné a un numéro d’abonné unique, un nom, un prénom,
une adresse et une date d’abonnement.
Les livres ont tous un numéro ISBN, un titre, un éditeur et une année
de publication.
Les auteurs qui écrivent les livres sont identifiés par un numéro
d’auteur, et on stocke leur nom et prénom.
Lorsqu’un abonné réalise un emprunt d’un livre, on enregistre le
numéro et la date de l’emprunt.
Lorsqu’il le restitue, on mémorise la date de retour.
3 / 39
Notations
Dictionnaire de données
On le résume dans un tableau.
Libellé Type Description
NumAbo entier Numéro de l’abonné
NomAbo car(20) Nom de l’abonné
PrénomAbo car(20) Prénom de l’abonné
AdrAbo car(80) Adresse de l’abonné
DateAbo date Date de l’abonnement (AAAA-MM-JJ)
NumAut entier Numéro de l’auteur
NomAut car(20) Nom de l’auteur
PrénomAut car(20) Prénom de l’auteur
ISBN car(13) Code ISBN identifiant un livre
Titre car(80) Titre du livre
Editeur car(20) Nom de l’editeur
Année entier Année de publication
NumEmp entier Numéro d’emprunt
DateEmp date Date de l’emprunt d’un livre par un abonné
DateRet date Date de retour d’un livre emprunté par un abonné
4 / 39
Notations
Exemple BD Bibliothèque : schéma E-A
Abonné 1,* Réalise 1,1 Emprunt 1,1 Concerne 1,* Livre
NumAbo NumEmp ISBN
NomAbo DateEmp Titre
PrénomAbo DateRet Éditeur
AdrAbo Année
DateAbo
1,*
Écrit
1,*
Auteur
3 Support des exemples du chapitre NumAut
NomAut
PrénomAut
5 / 39
Notations
Exemple BD Bibliothèque : schéma relationnel
Schéma relationnel de la BD
ABONNÉ(NumAbo, NomAbo, PrénomAbo, AdrAbo, DateAbo)
LIVRE(ISBN, Titre, Éditeur, Année)
AUTEUR(NumAut, NomAut, PrénomAut)
ÉCRIT(ISBN, NumAut)
EMPRUNT(NumEmp, NumAbo, ISBN, DateEmp, DateRet)
6 / 39
Notations
Exemple BD Bibliothèque : tables
Tables (relations).
AUTEUR
LIVRE
NumAut NomAut PrénomAut
ISBN Titre Éditeur Année
1 Gardarin Georges
9782212112818 Bases de Données Eyrolles 1989
2 Kernighan Brian
9782225805158 Le Langage C Masson 1985
3 Ritchie Dennis
9782207257357 Fondation Denoël 2006
4 Asimov Isaac
ABONNE
ÉCRIT
NumAbo NomAbo PrénomAbo DateAbo
ISBN NumAut
1 Dupont Philippe 2008-06-18
9782212112818 1
2 Durand Arthur 2009-01-02
9782225805158 2
3 Dupont Charlie 2015-05-03
9782225805158 3
4 Ducros Marie 2020-07-04
9782207257357 4
5 Vernier Alain 2021-09-15
EMPRUNT
NumEmp ISBN NumAbo DateEmp DateRet
1 9782225805158 2 2021-09-06 2021-09-20
2 9782225805158 3 2021-09-25 2021-10-11
3 9782212112818 1 2021-10-28 2021-11-10
4 9782212112818 1 2021-11-08 NULL
7 / 39
Notations Notations
Conventions de notations
Mots-clés de SQL : caractères COURIER majuscules
Paramètres des requêtes : caractères courier minuscules
Paramètres optionnels : [option]
Valeurs multiples possibles : valeur1 |valeur2
Options multiples : [option1 |option2 ]
8 / 39
Langage de manipulation des données
1 Notations et exemple fil-rouge
2 Langage de manipulation des données
Insertions de n-uplets
Suppression de n-uplets
Modification de n-uplets
Exercice
3 Agrégation et regroupement
4 SQL : Jointures avancées
9 / 39
Langage de manipulation des données Insertions de n-uplets
Insertion de n-uplets
Insertion de n-uplets
INSERT INTO table [(attribut1 , ..., attributi )]
VALUES (val11 , ..., val1i ), (val21 , ..., val2i ), ... ;
Insertion des n-uplets de valeurs (val11 , ..., val1i ), ... dans la
table.
Exemple
INSERT INTO Auteur VALUES
(5, 'Berge', 'Claude');
INSERT INTO Abonné VALUES
(7, 'Dunod', 'Charles', '2021-11-28'),
(8, 'Dupond', 'Hector', '2021-11-30');
10 / 39
Langage de manipulation des données Insertions de n-uplets
Insertion de n-uplets(2)
Il est possible de ne pas spécifier certaines valeurs :
INSERT INTO Emprunt(NumEmp, ISBN, NumAbo, DateEmp) VALUES
(5, '9782207257357', 5, '2021-11-29');
Les valeurs non renseignées prennent
la valeur par défaut pour l’attribut si elle a été définie (contrainte
DEFAULT valeur de la création de la table) ;
la valeur NULL sinon.
Les n-uplets insérés doivent vérifier les contraintes de la table
(domaine, NOT NULL, UNIQUE et autres).
11 / 39
Langage de manipulation des données Suppression de n-uplets
Suppression de n-uplets
Suppression de n-uplets vérifiant les conditions :
DELETE FROM table
WHERE condition1
[AND|OR] ...
[AND|OR] conditioni ;
Exemple
Suppression de tous les emprunts
DELETE FROM Emprunt;
Suppression de tous les emprunts précédant le 1er janvier 2007
DELETE FROM Emprunt
WHERE DateEmp < '2007-01-01';
12 / 39
Langage de manipulation des données Suppression de n-uplets
Suppression de n-uplets référencés
Attribut référencé par un autre attribut
Suppression impossible si un autre n-uplet référence la valeur.
CREATE TABLE Livre (
ISBN VARCHAR(10) NOT NULL, ...);
CREATE TABLE Emprunt (
...,
FOREIGN KEY (ISBN) REFERENCES Livre(ISBN));
Exemple
DELETE FROM Livre WHERE ISBN = '9782207257357';
On ne peut pas supprimer de n-uplet de Livre s’il est référencé par un
n-uplet d’Emprunt.
13 / 39
Langage de manipulation des données Suppression de n-uplets
Suppression de n-uplets référencés (2)
Suppression en cascade : ON DELETE CASCADE
Suppression du n-uplet entraîne la suppression des n-uplets qui le
référencent.
CREATE TABLE Livre (
ISBN VARCHAR(10) NOT NULL, ...);
CREATE TABLE Emprunt (
...,
FOREIGN KEY (ISBN) REFERENCES Livre(ISBN)
ON DELETE CASCADE);
Exemple
DELETE FROM Livre WHERE ISBN = '9782207257357';
La suppression de cet n-uplet de Livre entraînera la suppression des
n-uplets d’Emprunt correspondants.
14 / 39
Langage de manipulation des données Suppression de n-uplets
Suppression de n-uplets référencés (3)
Remplacement : ON DELETE SET NULL
NULL est substitué aux valeurs dont la référence est supprimée.
CREATE TABLE Emprunt (
...,
FOREIGN KEY (NumAbo) REFERENCES Abonné(NumAbo)
ON DELETE SET NULL);
Exemple
DELETE FROM Abonné WHERE NomAbo = 'Bertrand'
AND PrénomAbo = 'Christine';
La suppression de cet n-uplet de Abonné entraînera la mise à la valeur
NULL pour le numéro d’abonné correspondant dans la table Emprunt.
Variante : ON DELETE SET DEFAULT : remplacement par la valeur par
défaut (précisée à la création).
15 / 39
Langage de manipulation des données Modification de n-uplets
Modificiation de n-uplets
Applique la modification modif aux n-uplets vérifiant les conditions :
UPDATE table SET modif
WHERE condition1
[AND|OR] ...
[AND|OR] conditioni ;
Exemple
Changer le nom de Ferrand Christine en Bertrand :
UPDATE Abonné SET NomAbo = 'Bertrand'
WHERE NomAbo = 'Ferrand'
AND PrenomAbo = 'Christine';
Ajoute 1000 au numéro des abonnés dont le prénom commence par C :
UPDATE Abonné SET NumAbo = NumAbo + 1000
WHERE PrenomAbo LIKE 'C%';
16 / 39
Langage de manipulation des données Modification de n-uplets
Modification de n-uplets référencés
Attribut référencé par un autre attribut
Modification impossible si un autre n-uplet référence la valeur.
CREATE TABLE Écrit (
...,
FOREIGN KEY (NumAut) REFERENCES Auteur(NumAut));
Exemple
UPDATE Auteur SET NumAut = NumAut + 10000
WHERE NomAut LIKE 'L%';
On ne peut pas modifier un NumAut d’Auteur s’il est référencé par un
n-uplet d’Écrit.
17 / 39
Langage de manipulation des données Modification de n-uplets
Modification de n-uplets référencés
On peut contourner cette restriction en définissant le comportement
en cas de modification :
CREATE TABLE Écrit (
...,
FOREIGN KEY (NumAut) REFERENCES Auteur(NumAut))
ON UPDATE CASCADE;
En cas de modification de NumAut dans Auteur, les modifications
sont répercutées dans Écrit
En général, c’est une (très) mauvaise idée de changer la valeur d’une
clé primaire, en particulier d’un numéro (à auto-incrémentation).
L’option ON UPDATE CASCADE est plus utile quand une clé étrangère
fait référence à une clé non primaire.
18 / 39
Langage de manipulation des données Exercice
Exercice
Ajout de l’auteur Claude Berge
Ajout du livre Graphes et Hypergraphes écrit par Claude Berge en
1970 aux éditions Dunod (ISBN : 9782040097554)
Modification de l’année pour 1973 du livre Graphes et Hypergraphes
écrit par Claude Berge
Ajout de l’abonné Maire Pekkeau qui s’est abonné aujourd’hui
Modification du prénom et du nom des abonnés dont le prénom est
Maire et le nom Pekkeau pour Marie Pelleau
Suppression des abonnés dont le prénom est Marie et le nom Pelleau
19 / 39
Agrégation et regroupement
1 Notations et exemple fil-rouge
2 Langage de manipulation des données
3 Agrégation et regroupement
Agrégation
Regroupement
Exercice
4 SQL : Jointures avancées
20 / 39
Agrégation et regroupement Agrégation
Opérateurs d’agrégation
Opérateurs d’agrégation
SELECT FONCTION(att) FROM ...;
Fonction de calcul appliquée aux valeurs des n-uplets sélectionnés.
SUM(att) somme des valeurs de l’attribut att
AVG(att) moyenne
MIN(att) valeur minimale
MAX(att) valeur maximale
COUNT(att) compte le nombre de valeurs (sauf NULL)
COUNT(DISTINCT att) compte le nombre de valeurs distinctes
COUNT(*) compte le nombre de n-uplets renvoyés
D’autres fonctions peuvent exister (en fonction du SGBD).
21 / 39
Agrégation et regroupement Agrégation
Opérateurs d’agrégation : exemples (1)
Compter le nombre d’emprunts (lignes) effectués par l’abonné 145
SELECT COUNT(*)
FROM Emprunt
WHERE NumAbo = 145;
Compter le nombre de valeurs non NULL dans la colonne DateEmp (y
compris identiques) pour les emprunts effectués par l’abonné 145
SELECT COUNT(DateEmp)
FROM Emprunt
WHERE NumAbo = 145;
Compter le nombre de valeurs distinctes dans la colonne DateEmp
pour les emprunts effectués par l’abonné 145
SELECT COUNT(DISTINCT DateEmp)
FROM Emprunt
WHERE NumAbo = 145;
22 / 39
Agrégation et regroupement Agrégation
Opérateurs d’agrégation : exemples (2)
Date du dernier emprunt du livre numéro 9782207257357
SELECT MAX(DateEmp)
FROM Emprunt
WHERE ISBN = '9782207257357';
Durée moyenne des emprunts (en nombre de jours)
SELECT AVG(DateRet - DateEmp)
FROM Emprunt;
Durée maximale des emprunts (en nombre de semaines)
SELECT MAX(DateRet - DateEmp)/7
FROM Emprunt;
23 / 39
Agrégation et regroupement Agrégation
Opérateurs d’agrégation : exemples (3)
On peut renommer les colonnes
SELECT MAX(DateRet - DateEmp) AS "Durée Maximale"
FROM Emprunt;
Durée maximale des emprunts.
Renommage : s’il comporte des espaces, le nouveau nom doit être
entouré de guillemets
Sous-requête
SELECT *
FROM Emprunt
WHERE DateRet - DateEmp =
(SELECT MAX(DateRet - DateEmp)
FROM Emprunt);
Liste des emprunts de durée maximale (il peut y en avoir plusieurs).
24 / 39
Agrégation et regroupement Agrégation
Spécificité SQLite
Les dates en SQLite sont (souvent) stockées en format texte (type
TEXT)
Il est donc impossible d’effectuer des calculs (arithmétiques) sur des
dates
Il faut donc les convertir en une valeur numérique en utilisant l’une des
fonctions prédéfinies
La fonction JULIANDAY('2022-11-03') retourne le nombre de jours
écoulés jusqu’au 3 novembre 2022 1 .
Exemple :
SELECT AVG(JULIANDAY(DateRet) - JULIANDAY(DateEmp))
FROM Emprunt;
1 depuis le 24 novembre -4713 (ou le 24 novembre 4714 avant Jésus-Christ)
25 / 39
Agrégation et regroupement Regroupement
Regroupement des n-uplets en sortie
Clause GROUP BY
Regrouper les n-uplets qui ont la ou les mêmes valeurs pour un ou
plusieurs attributs
Une seule ligne en sortie pour chaque groupe formé
Fonction d’agrégation calculée pour chaque groupe
Exemple
Numéro ISBN et nombre d’emprunts de chaque livre
SELECT ISBN, COUNT(*)
FROM Emprunt
GROUP BY ISBN;
26 / 39
Agrégation et regroupement Regroupement
Regroupement des n-uplets en sortie
Les emprunts sont groupés par ISBN puis agrégés
Emprunt
NumEmp ISBN NumAbo ...
1 1234123410 1 ...
3 1234123410 2 ... Résultat
123 1234123410 33 ...
ISBN COUNT(*)
2 1234123425 3 ...
1234123410 3
4 1234123425 2 ...
1234123425 5
12 1234123425 9 ...
1234123475 2
15 1234123425 15 ...
... ...
101 1234123425 2 ...
4 1234123475 3 ...
21 1234123475 12 ...
... ... ... ...
27 / 39
Agrégation et regroupement Regroupement
Regroupement sur plusieurs attributs
Une ligne pour chaque combinaison de valeurs des attributs du
GROUP BY
Nombre d’emprunts de chaque livre pour chaque abonné.
SELECT ISBN, NumAbo, COUNT(*)
FROM Emprunt
GROUP BY ISBN, NumAbo;
ISBN NumAbo COUNT(*)
1234123410 1 1
1234123410 2 1
1234123425 2 2
1234123425 3 1
1234123475 3 1
... ... ...
28 / 39
Agrégation et regroupement Regroupement
Attributs de regroupement
Le regroupement doit porter sur un attribut de la clause SELECT
Exemple erroné
SELECT Abonné.NomAbo, MAX(DateRet - DateEmp)
FROM Abonné, Emprunt
WHERE Abonné.NumAbo = [Link]
GROUP BY [Link];
Erreur : l’attribut [Link] n’apparaît pas dans la clause
SELECT
Exemple corrigé
SELECT Abonné.NumAbo, NomAbo, MAX(DateRet - DateEmp)
FROM Abonné, Emprunt
WHERE Abonné.NumAbo = [Link]
GROUP BY Abonné.NumAbo;
Correct : l’attribut Abonné.NumAbo apparaît dans la clause SELECT
29 / 39
Agrégation et regroupement Regroupement
Condition de sélection des groupes (1)
Clause HAVING : condition de sélection sur l’agrégat
Exemple
Numéro d’abonné et nombre d’emprunts pour les abonnés ayant fait
strictement plus de deux emprunts
SELECT NumAbo, COUNT(*)
FROM Emprunt
GROUP BY NumAbo
HAVING COUNT(*) > 2;
Renommage : s’il comporte des espaces, le nouveau nom doit être
entouré de guillemets
SELECT NumAbo, COUNT(*) AS "Nombre d'Emprunts"
FROM Emprunt
GROUP BY NumAbo
HAVING "Nombre d'Emprunts" > 2;
30 / 39
Agrégation et regroupement Regroupement
Condition de sélection des groupes (2)
Exemple
SELECT NumAbo, MAX(DateRet - DateEmp) AS "Durée maximale"
FROM Emprunt
GROUP BY NumAbo
HAVING "Durée maximale" =
(SELECT MAX(DateRet - DateEmp)
FROM Emprunt);
Sous-requête : durée maximale parmi tous les emprunts.
Requête principale : calcule la durée maximale des emprunts pour
chaque abonné et la compare à la valeur renvoyée par la sous-requête.
Résultat : numéro d’abonné et durée d’emprunt des emprunts de durée
maximale.
31 / 39
Agrégation et regroupement Exercice
Exercice
Afficher les informations du dernier abonné
Afficher le nombre de livres distincts qui ont été emprunté au moins
une fois
Afficher pour chaque abonné la durée totale de ses emprunts
Afficher pour chaque livre emprunté le nombre de fois qu’il a été
emprunté
Afficher pour chaque livre emprunté la durée moyenne de l’emprunt
Afficher pour chaque livre emprunté la durée totale des emprunts
32 / 39
SQL : Jointures avancées
1 Notations et exemple fil-rouge
2 Langage de manipulation des données
3 Agrégation et regroupement
4 SQL : Jointures avancées
33 / 39
SQL : Jointures avancées
Opérateur JOIN
Jointure (interne) entre deux tables
SELECT ...
FROM table1 JOIN table2
ON table1 .att11 op1 table2 .att21 [AND|OR
ON table1 .att12 op2 table2 .att22 [AND|OR
ON ... ]];
Exemple
SELECT Abonné.*
FROM Abonné JOIN Emprunt
ON Abonné.NumAbo = [Link];
Opérateur arithmétique opi de comparaison : =, <>, <, >, <=, >=
On peut aussi écrire INNER JOIN
34 / 39
SQL : Jointures avancées
Opérateurs LEFT JOIN, RIGHT JOIN, FULL JOIN
Jointure externe gauche, droite ou totale entre deux tables
SELECT ...
FROM table1 [LEFT|RIGHT|FULL] JOIN table2
ON table1 .att11 op1 table2 .att21 [AND|OR
ON table1 .att12 op2 table2 .att22 [AND|OR
ON ... ]];
LEFT JOIN : inclure tous les n-uplets de table1 même s’ils n’ont pas
de n-uplet lié dans table2
RIGHT JOIN : inclure tous les n-uplets de table2 même s’ils n’ont pas
de n-uplet lié dans table1
FULL JOIN : inclure tous les n-uplets de table1 et table2 même s’ils
n’ont pas de n-uplet lié dans l’autre table.
35 / 39
SQL : Jointures avancées
Opérateurs LEFT JOIN, RIGHT JOIN, FULL JOIN (2)
Exemple
SELECT *
FROM Abonné LEFT JOIN Emprunt
ON Abonné.NumAbo = [Link];
Abonné Emprunt
NumAbo NomAbo PrénomAbo DateAbo NumEmp ISBN NumAbo DateEmp DateRet
1 Dupont Philippe 2021-09-17 3 9782212112818 1 2021-10-28 2021-11-10
1 Dupont Philippe 2021-09-17 4 9782212112818 1 2021-11-08 NULL
2 Durand Arthur 2009-01-02 1 9782225805158 2 2021-09-06 2021-09-20
3 Dupont Charlie 2015-05-03 2 9782225805158 3 2021-09-25 2021-10-11
4 Ducros Marie 2020-07-04 NULL NULL NULL NULL NULL
5 Vernier Alain 2021-09-15 NULL NULL NULL NULL NULL
Les abonnés n’ayant pas fait d’emprunt apparaissent dans le résultat.
Attributs non renseignés : valeur NULL
36 / 39
SQL : Jointures avancées
Jointures imbriquées
Il est possible de combiner les opérateurs JOIN, LEFT JOIN,
RIGHT JOIN, FULL JOIN
Exemple
SELECT * FROM Livre
JOIN (Ecrit JOIN Auteur
ON [Link] = [Link])
ON [Link] = [Link];
Une jointure externe peut être imbriquée dans une jointure interne.
La réciproque n’est pas vraie.
37 / 39
SQL : Jointures avancées
Exercice
Afficher les noms et prénoms des abonnés qui n’ont pas encore rendu
un livre emprunté
Afficher les noms et prénoms des abonnés qui ont déjà emprunté un
livre par un auteur de même prénom qu’eux
Titre des livres écrits par (au moins) deux auteurs qui portent le même
prénom
38 / 39
À suivre
Dans un prochain cours :
vues, index et déclencheurs.
39 / 39