Ceci est une ancienne révision du document !
TD3 - SQL MySQL — Médiathèque : exercices progressifs
Présentation
Ce support accompagne le script mediatheque_pedagogique.sql, destiné à MySQL 8.0+. Le jeu de données pédagogique décrit une médiathèque : éditeurs, adhérents, auteurs, livres, exemplaires physiques et emprunts. Les noms et données sont fictifs.
Installation et préparation
- Importez
mediatheque_pedagogique.sqldans une instance MySQL 8.0+ avec un compte autorisé à créer une base et des tables. - Le script sélectionne la base
mediatheque_pedagogiqueet crée les tables ainsi que les données d’exercice. - Attention : à chaque réexécution, le script supprime puis recrée les tables de même nom dans cette base. Utilisez un environnement de pratique, et non une base contenant des données à conserver.
- Ouvrez un client SQL et assurez-vous que la base
mediatheque_pedagogiqueest sélectionnée avant de travailler. - Rédigez et exécutez vos requêtes dans votre client SQL.
Contexte et schéma
Le modèle relationnel comprend les tables suivantes. Les identifiants préfixés par id_ sont des clés; les liens indiqués correspondent aux clés étrangères du modèle.
| Table | Champs principaux | Rôle / relations |
|---|---|---|
editeur | id_editeur, nom, ville | Informations sur les éditeurs. |
livre | id_livre, titre, genre, annee_publication, id_editeur | Catalogue; id_editeur référence editeur.id_editeur. |
auteur | id_auteur, nom, prenom | Informations sur les auteurs. |
livre_auteur | id_livre, id_auteur, ordre_auteur | Association entre livres et auteurs; les deux identifiants référencent leurs tables respectives. |
exemplaire | id_exemplaire, id_livre, code_barres, date_acquisition | Exemplaires physiques; id_livre référence livre.id_livre. |
adherent | id_adherent, nom, prenom, ville, date_inscription | Informations sur les adhérents. |
emprunt | id_emprunt, id_exemplaire, id_adherent, date_emprunt, date_echeance, date_retour | Historique des prêts; les identifiants d’exemplaire et d’adhérent référencent leurs tables respectives. |
date_retour peut être NULL lorsqu’un emprunt n’est pas encore rendu dans les données d’exercice. Les exercices qui portent sur les retards utilisent la date de référence fixe du 15 mars 2025 afin de garder des résultats reproductibles.
A. Catalogue, filtres et tri
1. Lister le catalogue
Afficher le titre, le genre et l’année de publication de tous les livres.
2. Filtrer par genre
Afficher les titres de science-fiction publiés en 1950 ou après.
3. Rechercher et trier
Afficher les livres dont le titre contient « du », triés par année de publication décroissante puis par titre croissant.
4. Filtrer par intervalle de dates
Afficher les emprunts commencés entre le 1er et le 28 février 2025 inclus, du plus récent au plus ancien.
B. Relations entre les tables
5. Livre et éditeur
Afficher chaque titre avec le nom de son éditeur.
6. Emprunts, adhérents et exemplaires
Lister les prêts avec le nom et le prénom de l’adhérent, le code-barres, la date d’emprunt et la date de retour. Inclure les prêts actifs.
7. Livres et auteurs
Afficher chaque titre et chacun de ses auteurs. Le livre coécrit apparaît sur deux lignes, une par auteur.
8. Livres et nombre d’exemplaires
Afficher tous les livres et le nombre d’exemplaires de chacun, y compris ceux qui en ont zéro.
C. Comptages et regroupements
9. Nombre de livres par genre
Afficher le nombre de livres dans chaque genre, du genre le plus représenté au moins représenté.
10. Genres représentés plusieurs fois
Afficher uniquement les genres comptant au moins deux livres.
11. Nombre de prêts par adhérent
Afficher chaque adhérent et le nombre total de ses prêts enregistrés, même s’il n’en a aucun.
12. Adhérents ayant plusieurs prêts
Afficher les adhérents qui ont au moins deux emprunts enregistrés (historiques et actifs confondus).
D. Prêts et analyses de dates
13. Prêts actifs
Lister les prêts encore actifs au sens pédagogique (date_retour non renseignée), avec l’adhérent et le livre concerné.
14. Retards à la date de référence
Parmi les prêts actifs au 15 mars 2025, afficher ceux dont l’échéance est dépassée et calculer le nombre de jours de retard.
15. Retours après échéance
Lister les prêts rendus après leur date d’échéance, avec le nombre de jours de retard au retour.
16. Nombre de prêts par titre
Pour chaque livre, afficher le nombre de prêts associés à ses exemplaires; inclure les livres sans exemplaire ou sans prêt.