Écrire les requêtes de suivi des emprunts et retards

Vous devez suivre les emprunts en retard et repérer les œuvres les plus empruntées, sans produire un résultat trompeur. Le schéma ne contient pas de date de retour effective : vous pouvez donc lister les emprunts présents dans la table retards, mais pas déterminer à vous seuls quels emprunts sont encore en retard aujourd’hui.

Vous avez déjà préparé les demandes et les critères de résultat dans la leçon précédente. Ici, vous transformez ces critères en requêtes lisibles, en réutilisant les tables et les colonnes du schéma de la médiathèque. Ajoutez les requêtes suivantes à la fin du fichier requetes.sql ; ne remplacez pas les requêtes des leçons précédentes.

Lister les emprunts enregistrés comme retards

La table retards identifie les emprunts concernés par un retard et fournit le nombre de jours de retard ainsi que les frais. Pour présenter ces informations avec le nom de l’adhérent et le titre de l’œuvre, reliez les retards aux emprunts, puis aux adhérents, aux exemplaires et aux œuvres. Chaque jointure suit une clé étrangère déclarée dans le schéma.

-- Lister les emprunts enregistrés dans la table des retards
SELECT
    em.id_emprunt,
    ad.nom,
    ad.prenom,
    oe.titre,
    em.date_emprunt,
    em.date_retour_prevue,
    re.jours_retard,
    re.frais
FROM retards AS re
INNER JOIN emprunts AS em
    ON re.id_emprunt = em.id_emprunt
INNER JOIN adherents AS ad
    ON em.id_adherent = ad.id_adherent
INNER JOIN exemplaires AS ex
    ON em.id_exemplaire = ex.id_exemplaire
INNER JOIN oeuvres AS oe
    ON ex.id_oeuvre = oe.id_oeuvre
ORDER BY re.jours_retard DESC, em.date_retour_prevue ASC;

Le résultat contient une ligne par emprunt associé à un enregistrement de retard. Le tri place d’abord les retards ayant le plus grand nombre de jours en tête ; en cas d’égalité, la date de retour prévue sert de critère secondaire. Les colonnes sélectionnées permettent d’identifier l’emprunt, l’adhérent, l’œuvre et les informations de retard sans afficher toutes les colonnes des tables.

Classer les œuvres selon leur nombre d’emprunts

Pour comparer la fréquence d’emprunt des œuvres, il faut partir des emprunts et remonter de chaque exemplaire vers l’œuvre correspondante. Le comptage porte sur les lignes d’emprunt : si plusieurs exemplaires d’une même œuvre ont été empruntés, chaque emprunt contribue au total de cette œuvre. Le regroupement par identifiant et titre permet d’obtenir une ligne par œuvre.

-- Classer les œuvres selon le nombre d'emprunts enregistrés
SELECT
    oe.id_oeuvre,
    oe.titre,
    COUNT(em.id_emprunt) AS nombre_emprunts
FROM oeuvres AS oe
INNER JOIN exemplaires AS ex
    ON oe.id_oeuvre = ex.id_oeuvre
INNER JOIN emprunts AS em
    ON ex.id_exemplaire = em.id_exemplaire
GROUP BY oe.id_oeuvre, oe.titre
ORDER BY nombre_emprunts DESC, oe.titre ASC;

La colonne calculée nombre_emprunts facilite la lecture du résultat et le tri décroissant affiche en premier les œuvres les plus empruntées. Le tri alphabétique du titre départage les œuvres qui ont le même total. Comme la requête utilise des jointures internes, elle ne renvoie que les œuvres ayant au moins un exemplaire lié à un emprunt ; les œuvres jamais empruntées ne figurent pas dans ce classement.

Ces requêtes utilisent les colonnes disponibles dans le contrat du projet et n’ajoutent aucune date ou information supposée. Si les critères préparés demandent un nombre limité d’œuvres, vous pouvez appliquer la limite convenue après le tri ; sans seuil défini, le classement complet reste plus transparent et permet de vérifier les égalités en tête. Les résultats dépendront naturellement des données présentes dans donnees.sql ou dans votre base.

À retenir

• Reliez chaque table en suivant les clés étrangères du schéma pour réunir les informations utiles au suivi.
• La table retards permet de lister les emprunts enregistrés comme retards, mais ne prouve pas qu’ils sont toujours actifs.
• Comptez les emprunts par œuvre, puis triez le total par ordre décroissant pour faire ressortir les plus empruntées.
• Ajoutez les requêtes commentées à la suite du contenu existant de requetes.sql, sans modifier les autres fichiers.

Dans la prochaine leçon, vous contrôlerez les résultats obtenus par rapport aux critères préparés et finaliserez la livraison du fichier SQL.