Probleme – Le bulletin, du détail au classement

1. Le détail, avec deux jointures externes.

SELECT e.nom, m.intitule, n.valeur, m.coef
FROM Eleve e
LEFT JOIN Note n ON n.eleve = e.id
LEFT JOIN Matiere m ON m.code = n.matiere
ORDER BY e.nom, m.intitule;

La deuxième jointure doit être externe elle aussi, et c'est le point technique. Si l'on écrivait LEFT JOIN Note ... JOIN Matiere ..., la seconde jointure exigerait une correspondance — et la ligne de Dupont, dont n.matiere vaut NULL, n'en aurait aucune : elle serait éliminée, annulant l'effet de la première. Une jointure interne placée après une jointure externe détruit l'externalité, silencieusement.

2. Le récapitulatif.

SELECT e.nom,
       COUNT(n.valeur) AS nb,
       AVG(n.valeur) AS moyenne,
       SUM(n.valeur * m.coef) * 1.0 / SUM(m.coef) AS ponderee
FROM Eleve e
LEFT JOIN Note n ON n.eleve = e.id
LEFT JOIN Matiere m ON m.code = n.matiere
GROUP BY e.id, e.nom
ORDER BY ponderee DESC;

Trois détails qui portent chacun une décision :

• COUNT(n.valeur) et non COUNT(*) — sans quoi Dupont afficherait $1$ note (exercice 21.3) ;
• GROUP BY e.id, e.nom et non GROUP BY e.nom — deux homonymes seraient fusionnés. On groupe toujours par la clé primaire, et l'on ajoute au GROUP BY les colonnes qu'on veut afficher ;
• ORDER BY ponderee DESC — l'alias est ici légitime, le tri venant après le SELECT (exercice 21.7). Sur le moteur employé ici, la ligne NULL de Dupont se range en dernier ; la place des NULL dans un tri n'est pas fixée par la norme et diffère d'un moteur à l'autre. Si elle importe, on l'écrit — par exemple en triant d'abord sur COUNT(n.valeur) = 0.

3. Écarter les élèves sans note, deux fois.

-- (a) par le HAVING : on regroupe tout, puis on jette les paquets vides
... GROUP BY e.id, e.nom HAVING COUNT(n.valeur) > 0 ORDER BY ponderee DESC;

-- (b) par la jointure interne : ils ne sont jamais entrés
SELECT e.nom, COUNT(*) AS nb, SUM(n.valeur*m.coef)*1.0/SUM(m.coef) AS ponderee
FROM Eleve e JOIN Note n ON n.eleve = e.id JOIN Matiere m ON m.code = n.matiere
GROUP BY e.id, e.nom ORDER BY ponderee DESC;

Les deux rendent les mêmes trois lignes : Cohen $15,00$, Alaoui $13,54$, Benali $11,60$.

Laquelle préférer ? La (b) est plus rapide — elle ne construit jamais le paquet de Dupont. Mais la (a) est plus honnête : elle dit, dans le texte de la requête, qu'un choix a été fait. La (b) le cache dans le mot JOIN, où personne ne le lira. Sur une requête qu'on relira dans six mois, écrire le choix vaut mieux que l'optimiser.

4. Le total.

SELECT COUNT(*) AS lignes, COUNT(valeur) AS notes,
       SUM(valeur) AS somme, AVG(valeur) AS moyenne_globale
FROM Note;
-- 5 | 5 | 66 | 13.2

Ici COUNT(*) et COUNT(valeur) coïncident, puisque Note n'a aucun NULL : c'est la jointure externe, et elle seule, qui en fabriquait. Écrire les deux est un contrôle, pas une redondance — s'ils diffèrent un jour, c'est qu'une valeur manque, et l'on veut le savoir. C'est l'esprit des assertions du chapitre chap:discipline, transposé aux données.

Noter enfin que $13,2$ n'est pas la moyenne des moyennes : celles-ci valent $14,5$, $11,0$ et $15,0$, dont la moyenne est $40,5/3=13,5$. Une moyenne de moyennes n'est pas une moyenne — elle donne le même poids à Cohen, qui a une note, qu'à Alaoui, qui en a deux. La bonne valeur est celle qui divise la somme des notes par leur nombre, ici $66/5=13,2$.