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
1. Installer DBeaver Community
DBeaver Community est un client de base de données. Il sert à écrire et exécuter des requêtes, mais ne contient pas le serveur MySQL.
- Téléchargez l’édition Community depuis le site officiel de DBeaver Community. Choisissez l’installateur qui correspond à votre système d’exploitation.
- Lancez l’installateur téléchargé et suivez les étapes proposées par votre système, puis ouvrez DBeaver.
- DBeaver utilise un pilote MySQL. Si DBeaver propose de télécharger ou d’installer ce pilote lors de la création de la connexion, acceptez.
2. Installer et démarrer MySQL Server
MySQL Server est un logiciel distinct. Installez MySQL Server 8.0 ou une version ultérieure, puis vérifiez que le service serveur est démarré. Consultez les instructions officielles d’installation MySQL correspondant à votre système. Sous Windows, MySQL Installer propose une installation guidée; l’installation du serveur comprend une étape de configuration.
Conservez le nom d’utilisateur et le mot de passe configurés pour le serveur. Le compte utilisé pour l’exercice doit être autorisé à créer une base de données et des tables.
3. Connecter DBeaver à MySQL et charger le jeu de données
- Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez
MySQL. - Pour une installation locale, indiquez
localhostcomme hôte et3306comme port, sauf si votre serveur a été configuré autrement. Saisissez le nom d’utilisateur et le mot de passe MySQL. Vous pouvez laisser la base non renseignée pour cette première connexion. - Testez la connexion, puis validez. Si nécessaire, laissez DBeaver télécharger le pilote JDBC MySQL.
- Ouvrez le fichier
mediatheque_pedagogique.sqldans l’éditeur SQL associé à cette connexion, puis exécutez le script complet. Le script crée et sélectionne la basemediatheque_pedagogique, puis crée les tables et insère les données d’exercice. - Pour les exercices, ouvrez un nouvel éditeur SQL sur cette connexion; le script a déjà sélectionné la base à utiliser.
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.
Pour les paramètres de connexion détaillés, consultez la documentation DBeaver pour MySQL et la documentation de création d’une connexion.
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
Exercice 1 — Lister le catalogue
Afficher le titre, le genre et l’année de publication de tous les livres.
Exercice 2 — Filtrer par genre
Afficher les titres de science-fiction publiés en 1950 ou après.
Exercice 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.
Exercice 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
Exercice 5 — Livre et éditeur (deux tables)
Afficher chaque titre avec le nom de son éditeur.
Exercice 6 — Emprunts, adhérents et exemplaires (trois tables)
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.
Exercice 7 — Livres et auteurs
Afficher chaque titre et chacun de ses auteurs. Le livre coécrit apparaît sur deux lignes, une par auteur.
Exercice 8 — LEFT JOIN : livres sans exemplaire
Afficher tous les livres et le nombre d’exemplaires de chacun, y compris ceux qui en ont zéro.
C. Comptages et regroupements
Exercice 9 — Nombre de livres par genre
Afficher le nombre de livres dans chaque genre, du genre le plus représenté au moins représenté.
Exercice 10 — HAVING : genres représentés plusieurs fois
Afficher uniquement les genres comptant au moins deux livres.
Exercice 11 — Nombre de prêts par adhérent, y compris zéro
Afficher chaque adhérent et le nombre total de ses prêts enregistrés, même s’il n’en a aucun.
Exercice 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
Exercice 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é.
Exercice 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.
Exercice 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.
Exercice 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.