SQL : jointures, agrégation et requêtes imbriquées

SELECT titre, nom FROM livre                                        -- (a)
JOIN auteur ON livre.id_auteur = auteur.id_auteur ORDER BY annee;

SELECT titre FROM emprunt                                           -- (b)
JOIN livre ON emprunt.id_livre = livre.id_livre
WHERE id_usager = 1;

SELECT DISTINCT usager.nom FROM emprunt                             -- (c)
JOIN usager ON emprunt.id_usager = usager.id_usager
JOIN livre  ON emprunt.id_livre  = livre.id_livre
WHERE livre.id_auteur = 3;

(a) Huit lignes, d'Orgueil et préjugés ($1813$) au Blé en herbe ($1923$). (b) Vingt mille lieues sous les mers, Guerre et paix, Orgueil et préjugés. (c) Alice et Emma (Guerre et paix), Chloé (Anna Karénine) — le DISTINCT protège du doublon si quelqu'un empruntait deux Tolstoï. (Chaque jointure suit une flèche du schéma : avant d'écrire, tracer le chemin de tables qui mène des données dont on dispose à celles qu'on veut — ici $\text{usager}\leftarrow \text{emprunt}\to \text{livre}$ ; une requête à jointures se planifie comme un itinéraire, chapitre 14.)

SELECT COUNT(*) FROM usager;                                        -- (a) 6
SELECT COUNT(*) FROM emprunt WHERE jour >= '2025-10-01';            -- (b) 5
SELECT AVG(annee) FROM livre WHERE id_auteur = 1;                   -- (c) 1870.33...
SELECT MAX(pages) FROM emprunt                                      -- (d)
JOIN livre ON emprunt.id_livre = livre.id_livre
WHERE jour <= '2025-09-30';                                         --     1536

(b) profite du format de dates : octobre $=$ « après le 1^er octobre », aucune fonction calendaire requise. (d) compose tout le chapitre en une ligne : jointure (pour avoir les pages), filtre (septembre), agrégat (MAX). (Vérifier (d) à la main : cinq emprunts de septembre, livres $1,4,3,1,2$, pages $448,1536,480,448,320$ — maximum $1536$ ✓ ; sur une base-jouet, l'agrégat se contrôle par énumération, et c'est tout l'intérêt pédagogique des bases-jouets.)

SELECT ville, COUNT(*) FROM usager GROUP BY ville;                  -- (a)
SELECT jour, COUNT(*) FROM emprunt GROUP BY jour ORDER BY jour;     -- (b)
SELECT id_auteur, MAX(annee) FROM livre GROUP BY id_auteur;         -- (c)

(a) Lyon $2$, Nantes $2$, Paris $2$ — l'égalité parfaite, qui se voit d'un coup d'œil là où la table brute la cachait. (b) Dix jours, un emprunt chacun : un GROUP BY peut rendre des groupes de taille $1$, et ce n'est pas une erreur. (c) Verne $1876$, Austen $1813$, Tolstoï $1877$, Colette $1923$. (Relire (c) avec la règle d'or : id_auteur est groupé, MAX(annee) agrégé — licite ; ajouter titre pour savoir quel livre serait illicite, et c'est l'objet de l'exercice 7.)

SELECT titre, COUNT(*) AS nb
FROM emprunt JOIN livre ON emprunt.id_livre = livre.id_livre
GROUP BY livre.id_livre, titre
ORDER BY nb DESC, titre;

Guerre et paix                   | 2
Orgueil et préjugés              | 2
Vingt mille lieues sous les mers | 2
Anna Karénine                    | 1
De la Terre à la Lune            | 1
Le Blé en herbe                  | 1
Michel Strogoff                  | 1

Sept lignes pour huit livres : Les Vrilles de la vigne, jamais emprunté, n'a aucune ligne dans emprunt — la jointure interne l'efface, lui et son zéro (le piège du cours, en chair et en os). Pour le recenser : l'EXCEPT du chapitre 15 (SELECT id_livre FROM livre EXCEPT SELECT id_livre FROM emprunt) en requête d'appoint — le tableau de bord complet est en deux requêtes, et l'honnêteté consiste à publier les deux. (Tout classement « par nombre de » construit sur une table d'association a ce angle mort ; les sondages, les statistiques de ventes, les comptages de citations aussi — savoir demander « et qui est à zéro ? » est une compétence d'analyste, pas de syntaxe.)

SELECT nom, COUNT(*) AS nb FROM emprunt                             -- (a)
JOIN usager ON emprunt.id_usager = usager.id_usager
GROUP BY usager.id_usager, nom;

-- (b) : ajouter  WHERE jour >= '2025-10-01'           (filtre de lignes)
-- (c) : ajouter  HAVING COUNT(*) >= 2                 (filtre de groupes)
-- (d) : ajouter  les deux clauses à la fois

Résultats : (a) Alice $3$, Bruno $2$, Chloé $3$, David $1$, Emma $1$ ; (b) Alice $1$, Bruno $1$, Chloé $2$, Emma $1$ — David disparaît (aucune ligne d'octobre) ; (c) Alice, Bruno, Chloé — David et Emma disparaissent (leur groupe échoue) ; (d) Chloé seule. La grammaire est limpide : « emprunts d'octobre » qualifie chaque ligne $\to$ WHERE ; « au moins 2 » qualifie le total d'un groupe $\to$ HAVING. (Le contresens classique — WHERE après coup sur l'agrégat ou HAVING sur une condition de ligne — produit au mieux une erreur, au pire un résultat faux-plausible ; en cas de doute, rejouer l'ordre logique de la méthode du cours : le WHERE s'exécute avant que les groupes n'existent.)

SELECT l1.titre, l2.titre FROM livre AS l1                          -- (a)
JOIN livre AS l2 ON l1.id_auteur = l2.id_auteur
WHERE l1.id_livre < l2.id_livre;

SELECT u1.nom, u2.nom FROM usager AS u1                             -- (b)
JOIN usager AS u2 ON u1.ville = u2.ville
WHERE u1.id_usager < u2.id_usager;

SELECT l1.titre, l2.titre, l1.annee FROM livre AS l1                -- (c)
JOIN livre AS l2 ON l1.annee = l2.annee
WHERE l1.id_auteur <> l2.id_auteur AND l1.id_livre < l2.id_livre;

(a) Cinq paires : trois chez Verne ($\left(\genfrac{}{}{0ex}{}{3}{2}\right)=3$), une chez Tolstoï, une chez Colette. (b) Alice–Chloé, Bruno–Emma, David–Farid. (c) Résultat vide : aucune année ne porte deux livres d'auteurs différents dans notre base — et une requête au résultat vide est une réponse, pas un échec (le « zéro ligne » se lit : « la situation décrite n'existe pas »). (Compter avant d'exécuter, à la $\left(\genfrac{}{}{0ex}{}{n}{2}\right)$ : l'autojointure filtrée par < rend exactement les paires non ordonnées — le lien combinatoire rend les vérifications immédiates, et un compte inattendu signale presque toujours un < oublié : paires doublées, ou diagonale parasite.)

(a) titre n'est ni dans le GROUP BY ni agrégé : pour le groupe Verne, trois titres candidats, la requête est ambiguë — SQL99 la rejette. Les moteurs laxistes (certains, hors norme) rendent un titre arbitraire du groupe, pas forcément celui de l'année maximale : le pire des comportements, plausible et faux (le LIMIT sans ORDER BY du chapitre 15, en pire). (b) La tournure légale : garder les livres dont l'année égale le maximum de leur propre auteur —

SELECT nom, titre, annee
FROM livre AS l JOIN auteur ON l.id_auteur = auteur.id_auteur
WHERE annee = (SELECT MAX(annee) FROM livre AS l2
               WHERE l2.id_auteur = l.id_auteur);

Verne   | Michel Strogoff     | 1876
Austen  | Orgueil et préjugés | 1813
Tolstoï | Anna Karénine       | 1877
Colette | Le Blé en herbe     | 1923

La sous-requête est corrélée : elle mentionne l.id_auteur, la ligne en cours d'examen — elle se réévalue pour chaque ligne, comme une fonction appelée dans une boucle. (C'est la requête la plus subtile du chapitre, et un grand classique d'épreuve ; la retenir comme un schéma : « le superlatif par groupe $=$ égalité avec l'agrégat corrélé » — et noter qu'elle rend tous les ex æquo, le bon comportement.)

Le chemin : l'emprunt connaît l'usager, l'usager connaît la ville — deux tables suffisent. Puis grouper par ville, sommer les lignes, filtrer les groupes :

SELECT ville, COUNT(*) AS nb
FROM emprunt JOIN usager ON emprunt.id_usager = usager.id_usager
GROUP BY ville
HAVING COUNT(*) >= 3
ORDER BY nb DESC;

Lyon  | 6
Paris | 3

Vérification manuelle : Lyon $=$ Alice ($3$) $+$ Chloé ($3$) $=6$ ✓ ; Paris $=$ Bruno ($2$) $+$ Emma ($1$) $=3$ ✓ ; Nantes $=$ David ($1$) $+$ Farid ($0$) $=1<3$, éliminé par le HAVING — et noter que le zéro de Farid, invisible dans la jointure, n'a ici aucune conséquence : il ne manquait que pour les classements par usager, pas pour les totaux par ville (l'absence d'une ligne nulle ne fausse pas une somme). (Ce distinguo — quand l'angle mort de la jointure interne est inoffensif et quand il est fatal — est le genre de raisonnement qui sépare l'utilisateur du concepteur de requêtes : toujours se demander qui devrait être là avec un zéro, et si ce zéro change la réponse.)

(a) Globale : $4608/8=576$. Par auteur : Verne $1280/3\approx 426,7$ ; Austen $480$ ; Tolstoï $1200$ ; Colette $224$. Moyenne des moyennes : $(426,7+480+1200+224)/4\approx 582,7\ne 576$. (b) La moyenne globale pondère chaque livre également ; la moyenne des moyennes pondère chaque auteur également — les trois livres de Verne, courts, pèsent $3/8$ de la première et $1/4$ de la seconde. « Un livre typique » : la globale. « Une œuvre d'auteur typique » : celle des moyennes. Les deux questions sont légitimes ; les confondre est l'erreur statistique la plus répandue de la presse (le « salaire moyen par entreprise » moyenné sur les entreprises, où la PME de trois personnes pèse autant que la multinationale). (c) Si tous les groupes ont le même effectif — ou si toutes les moyennes de groupes sont égales. (SQL exécute docilement les deux calculs : c'est l'analyste qui choisit la pondération en choisissant son GROUP BY — l'agrégation n'est pas neutre, et la requête est déjà une décision de modélisation, chapitre 4 : la moyenne n'a de sens qu'accompagnée de ce sur quoi elle moyenne.)

SELECT COUNT(*) FROM emprunt WHERE jour >= '2025-10-01';            -- (1) 5

SELECT titre, nom, COUNT(*) AS nb                                   -- (2)
FROM emprunt
JOIN livre  ON emprunt.id_livre  = livre.id_livre
JOIN auteur ON livre.id_auteur = auteur.id_auteur
GROUP BY livre.id_livre, titre, nom
ORDER BY nb DESC, titre LIMIT 3;

SELECT nom, COUNT(*) AS nb                                          -- (3)
FROM emprunt JOIN usager ON emprunt.id_usager = usager.id_usager
GROUP BY usager.id_usager, nom
HAVING COUNT(*) > 2.0;        -- la moyenne 10/5 = 2, calculée en (3')

SELECT id_livre FROM livre EXCEPT SELECT id_livre FROM emprunt;     -- (4) 8

SELECT ville, COUNT(DISTINCT emprunt.id_usager) AS emprunteurs      -- (5)
FROM usager JOIN emprunt ON emprunt.id_usager = usager.id_usager
GROUP BY ville;

(2) Guerre et paix (Tolstoï), Orgueil et préjugés (Austen), Vingt mille lieues (Verne) — $2$ emprunts chacun, le ORDER BY total (titre en second critère) rend le podium reproductible malgré les ex æquo. (3) Alice et Chloé ($3>2$) ; en toute rigueur le seuil devrait être une sous-requête (la moyenne des effectifs par emprunteur), que la norme autorise mais qu'on a chiffrée ici pour la lisibilité — l'honnêteté du rapport exige la requête (3') qui calcule ce $2$. (4) Les Vrilles de la vigne : la requête-vigie, à publier avec le palmarès (exercice 4). (5) Lyon $2$, Nantes $1$, Paris $2$ (DISTINCT dans un agrégat : au-delà de la liste officielle, signalé) — mais Nantes-sans-Farid illustre la limite : une ville de purs non-emprunteurs disparaîtrait entièrement ; le « villes sans emprunteur comprises » de l'énoncé exige l'EXCEPT d'appoint sur les villes, et le rapport le signale. (Un rapport n'est pas cinq requêtes : c'est cinq requêtes, leurs angles morts déclarés, et les requêtes-vigies qui les couvrent — la sixième compétence du programme, communiquer, appliquée aux données.)