Temps estimé de réalisation : 1h30
Objectifs
Question à laquelle cette activité va permettre de répondre :
Objectifs pédagogiques visés :
SELECT
JOIN
GROUP BY
Les requêtes SQL à exprimer sont relatives à la base de données BIBLIOTHEQUE constituée des relations dont le schéma logique est décrit ci-dessous :
abonne (id, nom, prenom, telephone, code_postal)
livre (id, isbn, titre, auteur, annee_edition, prix)
emprunt (id_livre, id_abonne, date_emprunt) : où id_livre référence livre(id) et id_abonne référence abonne(id).
Aucun historique n’est géré dans cette base : La table ABONNE contient les informations sur les abonnés actuels de la bibliothèque, et la table LIVRE contient celles de tous les livres actuellement gérés par la bibliothèque. La table EMPRUNT contient les emprunts en cours. Lorsqu’un livre est retourné, le tuple correspondant dans la table EMPRUNT est effacé.
Schéma logique de la base BIBLIOTHEQUE (sous forme graphique)
Les requêtes SQL respectent la structure générale présentée ci-dessous. Les paragraphes suivants vous guident pas à pas pour découvrir les concepts les plus importants et donnent des précisions sur la syntaxe.
SELECT colonnes
FROM table
[WHERE condition]
[GROUP BY colonnes [HAVING condition]]
[ORDER BY colonnes [ASC|DESC]];
Remarque : les expressions entre crochets [ … ] signifient que le mot-clef est optionnel !
SELECT [DISTINCT] colonne_1, ..., colonne_n
FROM nom_table
WHERE condition
[ORDER BY colonne_x [ASC|DESC], colonne_y [ASC|DESC]];
Dans la clause WHERE
, la condition peut s’exprimer au moyen de différents opérateurs : =
, !=
, <=
, <
, >
, >=
. Si l’on veut comparer deux chaînes de caractères au moyen d’une expression régulière il ne faut pas utiliser l’opérateur =
mais l’opérateur LIKE
suivi d’une chaîne de caractères pouvant contenir des jokers :
%
pour remplacer n’importe quelle suite de caractères_
pour remplacer un seul caractèreDans la clause ORDER
, le mot-clef ASC
permet de faire un tri ascendant (ordre croissant, alphabétique, alpha-numérique) et le mot-clef DESC
un tri descendant. Par défaut, si l’indication de l’ordre de tri n’est pas indiquée il s’agira d’un tri ascendant.
Exemple
Affichez le nom et le prénom de tous les abonnés :
SELECT nom, prenom FROM abonne;
Affichez tous les livres écrits par Voltaire :
SELECT DISTINCT titre, auteur FROM livre WHERE auteur='Voltaire'
Le mot-clef DISTINCT
permet de supprimer les doublons (les lignes identiques et multiples) dans la réponse. La table livre
contient en fait chacun des exemplaires physiques des livres de la bibliothèques. En utilisant DISTINCT
on ne garde qu’un seul des exemplaire qui ont le même titre et même auteur, ce que l’on va appeler ici un livre.
Affichez tous les exemplaires des livres écrits par Voltaire :
SELECT * FROM livre WHERE auteur='Voltaire'
Champ libre pour tester les requêtes précédentes :
Question 1 : Affichez le titre et l’auteur de tous les exemplaires de livre
Question 2 : Affichez le nom, le code postal et le numéro de téléphone des abonnés sur Paris (code postaux débutant par 75)
Question 3 : Affichez, sans doublon, l’isbn et l’auteur des livres selon l’ordre alphabétique des auteurs et des titres
La jointure est l’opération qui permet fusionner les lignes d’une table à celles d’une autre table en fonction d’une condition. Par exemple, dans notre schéma, la table emprunt
permet de faire le lien entre les abonnés et les livres empruntés. Si l’on désire avoir pour chaque emprunt les informations détaillées sur les abonnés (plutôt qu’uniquement l’identifiant) il faut donc effectuer une jointure entre la table emprunt
et la table abonne
. Le critère de jointure à spécifier ici est que le numéro de l’abonné dans la table emprunt
doit être le même que le numéro de l’abonné dans la table abonne
. Souvent ces attributs sont des clés primaires et étrangères (ou référentielles), mais ce n’est pas obligatoire (à vous de faire attention au résultat obtenu !).
L’opérateur pour effectuer la jointure est le JOIN
qui, implicitement, correspond à INNER JOIN
pour une jointure interne. Sachez à ce niveau que pour ce type de jointure l’ordre des tables n’est pas important. Mais ce ne sera pas toujours le cas !
Voici la syntaxe détaillée à respecter pour effectuer une jointure :
SELECT [DISTINCT] colonne_1, ..., colonne_n
FROM nom_table_1 [[AS] alias_1] [INNER] JOIN nom_table_2 [[AS] alias_2]
ON nom_table_1.nom_colonne_x = nom_table_2.nom_colonne_y
L’expression [[AS] alias]
permet de renommer une table au moyen d’un alias. Cette notation est utilisée pour faciliter la lecture d’une
requête et pour éviter les confusions lorsque plusieurs tables ont des attributs se nommant de la même façon.
Exemple
Donnez le nom des abonnés ayant fait un emprunt :
SELECT DISTINCT A.nom FROM abonne A INNER JOIN emprunt E ON A.id=E.id_abonne
Affichez les dates d’emprunt des exemplaires du livre “Le Petit Prince” (attention aux majuscules dans le nom du titre, SQL est sensible à la casse dans les chaînes de caractères) :
SELECT E.date_emprunt FROM emprunt E INNER JOIN livre L ON E.id_livre=L.id
WHERE L.titre='Le Petit Prince'
Champ libre pour tester les requêtes précédentes :
Question 4 : Affichez le titre des livres empruntés par l’abonné n°2
Question 5 : Affichez la liste des emprunts sur la ville de Brest (code postal 29200)
Question 6 : Affichez le nom et le prénom des abonnés qui empruntent actuellement un exemplaire du livre “Le Petit Prince”
GROUP BY colonne_1, ..., colonne_n
HAVING condition
Le mot-clef GROUP BY
permet de réaliser des regroupements selon un ou plusieurs attributs. Il est donc ainsi possible, au niveau du SELECT
, de spécifier une fonction d’agrégation (count
, min
, max
, sum
, avg
etc.) qui s’appliquera à l’ensemble des lignes regroupées.
Le mot-clef HAVING
permet lui de filtrer les résultats du regroupement (c’est-à-dire le résultat de la fonction d’agrégation).
Ainsi, le mot-clef WHERE
est une condition sur les valeurs présentes dans la table que vous interrogez, par opposition au mot-clef HAVING
qui est une condition sur la valeur donnée par la fonction d’agrégation.
Exemple
Affichez le nombre de livres actuellement empruntés :
SELECT count(id_livre) FROM emprunt
Affichez, pour chaque abonné ayant un emprunt en cours, le nombre de
livres actuellement empruntés :
SELECT id_abonne, count(id_livre) FROM emprunt GROUP BY id_abonne
Affichez, pour chaque abonné ayant plus de 2 emprunts en cours, le
nombre de livres actuellement empruntés :
SELECT id_abonne, count(id_livre) FROM emprunt
GROUP BY id_abonne HAVING count(id_livre) > 2
Champ libre pour tester les requêtes précédentes :
Question 7 : Affichez le prix total de tous les livres possédés par la bibliothèque
Question 8 : Affichez le nombre d’abonnés par code postal
Question 9 : Affichez le titre des livres de “Voltaire” présents en plus de 3 exemplaires
Après avoir réalisé cette activité, ou en cas de blocage, il est conseillé de vérifier ses réponses.