Terminale NSI • Bases de données • QCM /20

SQL approfondi

Jointures, agrégations, regroupements et sous-requêtes : comprendre les requêtes, les lire et savoir les écrire.

SELECT
JOIN
GROUP BY

1. Une base relationnelle pour s'entraîner

Deux tables : Classe et Eleve. Une classe peut accueillir plusieurs élèves.

Classe

id (PK)	nom
1	TNSI-A
2	TNSI-B
3	TNSI-C

Eleve

id (PK)	prenom	classe_id (FK)	note
11	Ada	1	16
12	Alan	1	12
13	Grace	2	18
14	Linus	2	14

La clé étrangère Eleve.classe_id référence la clé primaire Classe.id. La classe TNSI-C ne contient aucun élève : ce cas permettra de comparer les jointures.

2. INNER JOIN et LEFT JOIN

Relier les données de plusieurs tables.

INNER JOIN

Ne conserve que les lignes qui satisfont la condition de jointure.

LEFT JOIN

Conserve toutes les lignes de gauche, même sans correspondance à droite (NULL).

ON

Précise la relation entre les tables. Sans bonne condition, on risque de multiplier les lignes.

SELECT Eleve.prenom, Classe.nom
FROM Eleve
INNER JOIN Classe ON Eleve.classe_id = Classe.id;

Résultat : Ada et Alan avec TNSI-A ; Grace et Linus avec TNSI-B. La classe TNSI-C n'apparaît pas.

SELECT Classe.nom, Eleve.prenom
FROM Classe
LEFT JOIN Eleve ON Eleve.classe_id = Classe.id;

Cette fois, TNSI-C apparaît avec prenom = NULL.

3. Les fonctions d'agrégation

Produire des statistiques à partir des lignes.

COUNT

Compte des lignes (COUNT(*)) ou les valeurs non NULL d'une colonne.

SUM et AVG

Calculent la somme et la moyenne des valeurs non NULL.

MIN et MAX

Renvoient les valeurs minimale et maximale.

SELECT COUNT(*) AS effectif, AVG(note) AS moyenne
FROM Eleve
WHERE note >= 14;

Les élèves concernés sont Ada (16), Grace (18) et Linus (14) : effectif = 3, moyenne = 16.

4. GROUP BY, HAVING et ORDER BY

Regrouper, filtrer les groupes et trier.

SELECT Classe.nom, COUNT(Eleve.id) AS effectif,
       AVG(Eleve.note) AS moyenne
FROM Classe
LEFT JOIN Eleve ON Eleve.classe_id = Classe.id
GROUP BY Classe.id, Classe.nom
ORDER BY moyenne DESC;

Effectifs : TNSI-A = 2, TNSI-B = 2, TNSI-C = 0. La moyenne de TNSI-C est NULL. On utilise COUNT(Eleve.id), et non COUNT(*), pour obtenir zéro pour la classe vide.

SELECT classe_id, AVG(note) AS moyenne
FROM Eleve
WHERE note >= 10
GROUP BY classe_id
HAVING COUNT(*) >= 2;

Différence fondamentale : WHERE filtre les lignes avant les regroupements ; HAVING filtre les groupes après agrégation ; ORDER BY trie le résultat.

5. Les sous-requêtes

Utiliser le résultat d'une requête dans une autre.

SELECT prenom, note
FROM Eleve
WHERE note > (
    SELECT AVG(note) FROM Eleve
);

La moyenne générale est 15. Ada (16) et Grace (18) ont une note supérieure à cette moyenne.

SELECT prenom
FROM Eleve
WHERE classe_id IN (
    SELECT id FROM Classe WHERE nom = 'TNSI-A'
);

Résultat : Ada et Alan. Une sous-requête scalaire produit une seule valeur ; avec IN, on peut comparer à un ensemble de valeurs.

6. Atelier interactif : prédire le résultat

Sélectionne une requête pour voir son résultat sur notre jeu de données.

Choisis une requête :

Simulation pédagogique sur un jeu de données fixe : ce n'est pas un moteur SQL libre.

7. Exercices corrigés

Écris d'abord la requête sur papier ou dans ton éditeur.

Exercice 1 : afficher les prénoms et notes par ordre décroissant.

SELECT prenom, note FROM Eleve ORDER BY note DESC;

Exercice 2 : compter les élèves de chaque classe, y compris TNSI-C.

SELECT Classe.nom, COUNT(Eleve.id) AS effectif
FROM Classe LEFT JOIN Eleve ON Classe.id = Eleve.classe_id
GROUP BY Classe.id, Classe.nom;

Exercice 3 : afficher les classes dont la moyenne est strictement supérieure à 14.

SELECT classe_id, AVG(note) AS moyenne
FROM Eleve GROUP BY classe_id HAVING AVG(note) > 14;

Résultat : classe 2 (moyenne 16). La classe 1 a une moyenne de 14, donc elle n'est pas retenue.

8. QCM — 20 questions

Une seule bonne réponse par question, correction expliquée et note sur 20.