TD3 - SQL MySQL — Médiathèque : exercices progressifs

Ce support accompagne le script mediatheque_pedagogique.zip, 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.

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.

MariaDB Server est un logiciel distinct de DBeaver Community. Installez MariaDB Server, puis vérifiez que le service serveur est démarré. Consultez les instructions officielles d’installation de MariaDB correspondant à votre système.

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.

Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez MariaDb.
Pour une installation locale, indiquez localhost comme hôte et 3306 comme port, sauf si votre serveur a été configuré autrement. Saisissez le nom d’utilisateur et le mot de passe MariaDb. 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 MariaDb.
Ouvrez le fichier mediatheque_pedagogique.sql dans l’éditeur SQL associé à cette connexion, puis exécutez le script complet. Le script crée et sélectionne la base mediatheque_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.

Table	Champs utiles	Liens
`editeur`	`id_editeur`, nom, ville	Référencé par `livre.id_editeur`.
`adherent`	`id_adherent`, nom, prenom, ville, date_inscription	Référencé par `emprunt.id_adherent`.
`auteur`	`id_auteur`, nom, prenom	Associé aux livres par `livre_auteur`.
`livre`	`id_livre`, titre, genre, annee_publication, `id_editeur`	Référence `editeur`.
`livre_auteur`	`id_livre`, `id_auteur`, ordre_auteur	Table d’association livres-auteurs.
`exemplaire`	`id_exemplaire`, `id_livre`, code_barres, date_acquisition	Référence `livre`.
`emprunt`	`id_emprunt`, `id_exemplaire`, `id_adherent`, date_emprunt, date_echeance, date_retour	Référence `exemplaire` et `adherent`.

Un date_retour à NULL signale un emprunt non rendu. Pour les exercices de retard, utilisez la date de référence 15 mars 2025. Les livres 47 à 50 n’ont pas d’exemplaire.

Exercice 1 — Parcourir le catalogue

Afficher le titre, le genre et l’année de publication de chaque livre. Trier par genre, puis par titre.

Exercice 2 — Filtrer un genre

Afficher le titre et l’année des livres de Science-fiction publiés depuis 2015 inclus.

Exercice 3 — Recherche dans les titres

Trouver les titres contenant le mot des. Trier par année de publication décroissante.

Exercice 4 — Livres d’un intervalle d’années

Afficher le titre et l’année des livres publiés entre 2000 et 2010 inclus.

Exercice 5 — Inscriptions récentes

Afficher le nom, le prénom et la date d’inscription des adhérents inscrits depuis le 1er janvier 2024. Trier du plus récent au plus ancien.

Exercice 6 — Limiter un résultat

Afficher les dix livres les plus récents, du plus récent au plus ancien.

Exercice 7 — Livres et éditeurs

Pour chaque livre, afficher le titre et le nom de son éditeur. Trier par nom d’éditeur puis par titre.

Exercice 8 — Livres et auteurs

Afficher le titre de chaque livre, ainsi que le nom et le prénom de ses auteurs. Utiliser la table livre_auteur. Un livre ayant plusieurs auteurs doit apparaître sur plusieurs lignes.

Exercice 9 — Livres et exemplaires

Afficher le titre des livres et le code-barres de leurs exemplaires.

Exercice 10 — Emprunts et adhérents

Afficher chaque emprunt avec le nom, le prénom et la ville de l’adhérent, ainsi que les dates d’emprunt, d’échéance et de retour. Inclure les emprunts non rendus.

Exercice 11 — Parcours complet d’un emprunt

Pour chaque emprunt, afficher le titre du livre, son code-barres, le nom et le prénom de l’adhérent, ainsi que les dates d’emprunt, d’échéance et de retour.

Exercice 12 — Livres sans exemplaire

Afficher les livres qui n’ont aucun exemplaire dans la base.

Exercice 13 — Effectif par ville

Compter les adhérents de chaque ville et trier de l’effectif le plus important au plus faible.

Exercice 14 — Catalogue par genre

Compter le nombre de livres par genre et trier du plus grand nombre au plus petit.

Exercice 15 — Genres avec au moins cinq titres

Afficher les genres qui comptent au moins cinq livres.

Exercice 16 — Nombre d’exemplaires par livre

Pour chaque livre, afficher le titre et le nombre d’exemplaires. Inclure les livres qui n’en ont aucun.

Exercice 17 — Emprunts par adhérent

Afficher chaque adhérent avec le nombre d’emprunts enregistrés, y compris ceux qui n’ont aucun emprunt.

Exercice 18 — Adhérents ayant plusieurs emprunts

Afficher l’identifiant, le nom et le nombre d’emprunts des adhérents ayant au moins trois emprunts enregistrés.

Exercice 19 — Emprunts actifs

Afficher les emprunts non rendus, avec le titre du livre, le nom de l’adhérent, la date d’emprunt et la date d’échéance.

Exercice 20 — Retards au 15 mars 2025

À la date du 15 mars 2025, afficher les emprunts non rendus dont la date d’échéance est antérieure à cette date. Afficher également le nombre de jours de retard.

Exercice 21 — Retours en retard

Afficher les emprunts rendus après leur date d’échéance et calculer le nombre de jours séparant l’échéance du retour.

Exercice 22 — Nombre d’emprunts par livre

Pour chaque livre, afficher son titre et le nombre total d’emprunts enregistrés. Inclure les livres sans exemplaire et ceux dont les exemplaires n’ont jamais été empruntés.

Exercice 23 — Emprunts par genre

Pour chaque genre, afficher le nombre d’emprunts enregistrés pour ses livres. Inclure les genres pour lesquels aucun emprunt n’a été enregistré.

Exercice 24 — Auteurs de plusieurs livres

Afficher le nom et le prénom des auteurs associés à au moins deux livres, ainsi que le nombre de livres concernés.