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.

1. Téléchargez l’édition Community depuis le site officiel de DBeaver Community. Choisissez l’installateur qui correspond à votre système d’exploitation.
2. Lancez l’installateur téléchargé et suivez les étapes proposées par votre système, puis ouvrez DBeaver.
3. 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.

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.

1. Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez MySQL.
2. 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 MySQL. Vous pouvez laisser la base non renseignée pour cette première connexion.
3. Testez la connexion, puis validez. Si nécessaire, laissez DBeaver télécharger le pilote JDBC MySQL.
4. 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.
5. 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.

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.

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

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

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

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

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.

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

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

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.

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

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.

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.

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

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

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

Afficher les genres qui comptent au moins cinq livres.

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

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

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

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.

À 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.

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

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.

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

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