Jointures et Agrégations
INNER JOIN, GROUP BY, HAVING
Enonce
- Nom, prénom et filière de chaque étudiant
- Étudiants MPSI avec leur moyenne, triés par moyenne décroissante
- Filières où la moyenne en Info est supérieure à 14
📖 Référence SQL complète — CPGE
Fiche de référence couvrant toutes les commandes SQL au programme. Ordre d'exécution : FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
1 — SELECT : bases
SELECT col1, col2 FROM Table; -- colonnes spécifiques
SELECT * FROM Table; -- toutes les colonnes
SELECT DISTINCT ville FROM Users; -- supprime les doublons
SELECT nom AS famille, prenom AS p FROM Users; -- alias de colonnes
SELECT note * 2 AS double_note FROM Notes; -- expressions calculées
SELECT * FROM Table; -- toutes les colonnes
SELECT DISTINCT ville FROM Users; -- supprime les doublons
SELECT nom AS famille, prenom AS p FROM Users; -- alias de colonnes
SELECT note * 2 AS double_note FROM Notes; -- expressions calculées
2 — Filtrage (WHERE)
WHERE note > 14; -- comparaison : =, !=, <, >, <=, >=
WHERE ville = 'Paris' AND note >= 12; -- ET logique
WHERE ville = 'Paris' OR ville = 'Lyon'; -- OU logique
WHERE NOT ville = 'Paris'; -- négation
WHERE ville IN ('Paris', 'Lyon', 'Troyes'); -- appartenance à un ensemble
WHERE note BETWEEN 10 AND 15; -- intervalle fermé [10, 15]
WHERE nom LIKE 'D%'; -- commence par D (% = joker multi-car.)
WHERE nom LIKE '_u%'; -- 2e lettre = u (_ = joker 1 car.)
WHERE email IS NULL; -- teste la valeur NULL
WHERE email IS NOT NULL; -- exclut les NULL
WHERE ville = 'Paris' AND note >= 12; -- ET logique
WHERE ville = 'Paris' OR ville = 'Lyon'; -- OU logique
WHERE NOT ville = 'Paris'; -- négation
WHERE ville IN ('Paris', 'Lyon', 'Troyes'); -- appartenance à un ensemble
WHERE note BETWEEN 10 AND 15; -- intervalle fermé [10, 15]
WHERE nom LIKE 'D%'; -- commence par D (% = joker multi-car.)
WHERE nom LIKE '_u%'; -- 2e lettre = u (_ = joker 1 car.)
WHERE email IS NULL; -- teste la valeur NULL
WHERE email IS NOT NULL; -- exclut les NULL
3 — Tri et limite
ORDER BY note DESC; -- tri décroissant (ASC par défaut)
ORDER BY ville ASC, note DESC; -- tri multi-critères
LIMIT 10; -- 10 premiers résultats
LIMIT 5 OFFSET 10; -- 5 résultats à partir du 11e
ORDER BY ville ASC, note DESC; -- tri multi-critères
LIMIT 10; -- 10 premiers résultats
LIMIT 5 OFFSET 10; -- 5 résultats à partir du 11e
4 — Agrégation
COUNT(*) -- nombre de lignes
COUNT(col) -- nombre de valeurs non NULL
SUM(note), AVG(note) -- somme, moyenne
MIN(note), MAX(note) -- extrêmes
SELECT filiere, AVG(note) AS moy
FROM Notes
GROUP BY filiere; -- une ligne par groupe
HAVING AVG(note) > 14; -- filtre APRÈS agrégation
⚠ WHERE filtre avant GROUP BY, HAVING filtre après.
⚠ SELECT ne peut contenir que des colonnes du GROUP BY ou des agrégats.
COUNT(col) -- nombre de valeurs non NULL
SUM(note), AVG(note) -- somme, moyenne
MIN(note), MAX(note) -- extrêmes
SELECT filiere, AVG(note) AS moy
FROM Notes
GROUP BY filiere; -- une ligne par groupe
HAVING AVG(note) > 14; -- filtre APRÈS agrégation
⚠ WHERE filtre avant GROUP BY, HAVING filtre après.
⚠ SELECT ne peut contenir que des colonnes du GROUP BY ou des agrégats.
5 — Jointures
INNER JOIN -- lignes correspondantes dans les 2 tables
SELECT e.nom, f.nom AS filiere
FROM Etudiants e INNER JOIN Filieres f
ON e.filiere_id = f.id;
LEFT JOIN -- toutes les lignes de gauche + correspondances à droite (NULL sinon)
SELECT u.nom, COUNT(p.id)
FROM Users u LEFT JOIN Posts p ON u.id = p.id_user
GROUP BY u.id; -- inclut les users sans posts
RIGHT JOIN -- inverse du LEFT JOIN (toutes les lignes de droite)
CROSS JOIN -- produit cartésien (toutes les combinaisons)
SELECT * FROM T1 CROSS JOIN T2; -- |T1| × |T2| lignes
-- Auto-jointure (self-join) :
SELECT u1.nom, u2.nom
FROM Friendships f
JOIN Users u1 ON f.id_user1 = u1.id
JOIN Users u2 ON f.id_user2 = u2.id;
SELECT e.nom, f.nom AS filiere
FROM Etudiants e INNER JOIN Filieres f
ON e.filiere_id = f.id;
LEFT JOIN -- toutes les lignes de gauche + correspondances à droite (NULL sinon)
SELECT u.nom, COUNT(p.id)
FROM Users u LEFT JOIN Posts p ON u.id = p.id_user
GROUP BY u.id; -- inclut les users sans posts
RIGHT JOIN -- inverse du LEFT JOIN (toutes les lignes de droite)
CROSS JOIN -- produit cartésien (toutes les combinaisons)
SELECT * FROM T1 CROSS JOIN T2; -- |T1| × |T2| lignes
-- Auto-jointure (self-join) :
SELECT u1.nom, u2.nom
FROM Friendships f
JOIN Users u1 ON f.id_user1 = u1.id
JOIN Users u2 ON f.id_user2 = u2.id;
6 — Sous-requêtes
-- Dans WHERE (scalaire) :
WHERE note = (SELECT MAX(note) FROM Notes);
-- Dans WHERE (ensemble) :
WHERE id IN (SELECT id_user FROM Posts);
-- Dans FROM (table dérivée) :
SELECT * FROM
(SELECT id_user, COUNT(*) AS nb FROM Posts GROUP BY id_user) AS sub
WHERE sub.nb > 3;
-- Sous-requête corrélée :
SELECT * FROM Etudiants e
WHERE (SELECT AVG(note) FROM Notes n WHERE n.etudiant_id = e.id) > 15;
EXISTS -- vrai si la sous-requête retourne au moins une ligne
WHERE EXISTS (SELECT 1 FROM Posts p WHERE p.id_user = u.id);
WHERE NOT EXISTS (...); -- aucune ligne retournée
WHERE note = (SELECT MAX(note) FROM Notes);
-- Dans WHERE (ensemble) :
WHERE id IN (SELECT id_user FROM Posts);
-- Dans FROM (table dérivée) :
SELECT * FROM
(SELECT id_user, COUNT(*) AS nb FROM Posts GROUP BY id_user) AS sub
WHERE sub.nb > 3;
-- Sous-requête corrélée :
SELECT * FROM Etudiants e
WHERE (SELECT AVG(note) FROM Notes n WHERE n.etudiant_id = e.id) > 15;
EXISTS -- vrai si la sous-requête retourne au moins une ligne
WHERE EXISTS (SELECT 1 FROM Posts p WHERE p.id_user = u.id);
WHERE NOT EXISTS (...); -- aucune ligne retournée
7 — Opérations ensemblistes
UNION -- réunion sans doublons (mêmes colonnes requises)
SELECT nom FROM T1 UNION SELECT nom FROM T2;
UNION ALL -- réunion avec doublons (plus rapide)
INTERSECT -- intersection (lignes communes)
SELECT ville FROM Users INTERSECT SELECT ville FROM Villes;
EXCEPT -- différence (lignes de gauche absentes à droite)
SELECT id FROM Users EXCEPT SELECT id_user FROM Posts;
-- utilisateurs n'ayant jamais publié
SELECT nom FROM T1 UNION SELECT nom FROM T2;
UNION ALL -- réunion avec doublons (plus rapide)
INTERSECT -- intersection (lignes communes)
SELECT ville FROM Users INTERSECT SELECT ville FROM Villes;
EXCEPT -- différence (lignes de gauche absentes à droite)
SELECT id FROM Users EXCEPT SELECT id_user FROM Posts;
-- utilisateurs n'ayant jamais publié
8 — Fonctions utiles
-- Chaînes de caractères :
LENGTH(nom) -- longueur de la chaîne
UPPER(nom), LOWER(nom) -- majuscules / minuscules
SUBSTR(nom, 1, 3) -- sous-chaîne (début pos 1, longueur 3)
REPLACE(nom, 'a', 'e') -- remplacement de texte
nom || ' ' || prenom -- concaténation (SQLite)
-- Numériques :
ROUND(note, 2) -- arrondi à 2 décimales
ABS(valeur) -- valeur absolue
-- Gestion des NULL :
COALESCE(email, 'inconnu') -- 1re valeur non NULL
-- Expression conditionnelle :
CASE WHEN note >= 16 THEN 'TB'
WHEN note >= 14 THEN 'B'
WHEN note >= 12 THEN 'AB'
ELSE 'Passable' END AS mention
LENGTH(nom) -- longueur de la chaîne
UPPER(nom), LOWER(nom) -- majuscules / minuscules
SUBSTR(nom, 1, 3) -- sous-chaîne (début pos 1, longueur 3)
REPLACE(nom, 'a', 'e') -- remplacement de texte
nom || ' ' || prenom -- concaténation (SQLite)
-- Numériques :
ROUND(note, 2) -- arrondi à 2 décimales
ABS(valeur) -- valeur absolue
-- Gestion des NULL :
COALESCE(email, 'inconnu') -- 1re valeur non NULL
-- Expression conditionnelle :
CASE WHEN note >= 16 THEN 'TB'
WHEN note >= 14 THEN 'B'
WHEN note >= 12 THEN 'AB'
ELSE 'Passable' END AS mention
9 — Dates (SQLite)
JULIANDAY(date) -- convertit en nombre de jours (pour calculs)
JULIANDAY('2024-06-15') - JULIANDAY('2024-01-01') -- différence en jours
STRFTIME('%Y', date_ins) -- extraire l'année
STRFTIME('%m', date_ins) -- extraire le mois (01-12)
STRFTIME('%d', date_ins) -- extraire le jour
STRFTIME('%Y-%m', date_ins) -- année-mois
-- Comparaison de dates (format ISO 'YYYY-MM-DD') :
WHERE date_ins >= '2024-01-01' AND date_ins < '2025-01-01';
⚠ Les dates doivent être au format ISO pour que la comparaison fonctionne.
JULIANDAY('2024-06-15') - JULIANDAY('2024-01-01') -- différence en jours
STRFTIME('%Y', date_ins) -- extraire l'année
STRFTIME('%m', date_ins) -- extraire le mois (01-12)
STRFTIME('%d', date_ins) -- extraire le jour
STRFTIME('%Y-%m', date_ins) -- année-mois
-- Comparaison de dates (format ISO 'YYYY-MM-DD') :
WHERE date_ins >= '2024-01-01' AND date_ins < '2025-01-01';
⚠ Les dates doivent être au format ISO pour que la comparaison fonctionne.
10 — Fonctions de fenêtrage (OVER)
-- Numérotation :
ROW_NUMBER() OVER(ORDER BY note DESC) -- rang unique 1,2,3...
RANK() OVER(ORDER BY note DESC) -- rang avec trous (1,2,2,4)
DENSE_RANK() OVER(ORDER BY note DESC) -- rang sans trous (1,2,2,3)
-- Accès aux lignes voisines :
LAG(note, 1) OVER(ORDER BY date) -- valeur de la ligne précédente
LEAD(note, 1) OVER(ORDER BY date) -- valeur de la ligne suivante
-- Agrégat glissant :
SUM(note) OVER(PARTITION BY filiere ORDER BY date) -- somme cumulative par groupe
AVG(note) OVER(PARTITION BY filiere) -- moyenne par groupe sans réduire les lignes
⚠ OVER() sans PARTITION BY = fenêtre sur toute la table.
ROW_NUMBER() OVER(ORDER BY note DESC) -- rang unique 1,2,3...
RANK() OVER(ORDER BY note DESC) -- rang avec trous (1,2,2,4)
DENSE_RANK() OVER(ORDER BY note DESC) -- rang sans trous (1,2,2,3)
-- Accès aux lignes voisines :
LAG(note, 1) OVER(ORDER BY date) -- valeur de la ligne précédente
LEAD(note, 1) OVER(ORDER BY date) -- valeur de la ligne suivante
-- Agrégat glissant :
SUM(note) OVER(PARTITION BY filiere ORDER BY date) -- somme cumulative par groupe
AVG(note) OVER(PARTITION BY filiere) -- moyenne par groupe sans réduire les lignes
⚠ OVER() sans PARTITION BY = fenêtre sur toute la table.
11 — Modification de données
INSERT INTO Users (nom, prenom, ville)
VALUES ('Dupont', 'Alice', 'Paris');
INSERT INTO Archive SELECT * FROM Users WHERE annee < 2020;
-- insertion depuis une requête
UPDATE Users SET ville = 'Lyon' WHERE id = 3;
⚠ Sans WHERE, toutes les lignes sont modifiées !
DELETE FROM Users WHERE id = 3;
⚠ Sans WHERE, toutes les lignes sont supprimées !
CREATE TABLE Filieres (
id INTEGER PRIMARY KEY,
nom TEXT NOT NULL,
annee INTEGER DEFAULT 1
);
CREATE VIEW V_Moyennes AS
SELECT e.nom, AVG(n.note) AS moy
FROM Etudiants e JOIN Notes n ON e.id = n.etudiant_id
GROUP BY e.id; -- vue = requête nommée réutilisable
VALUES ('Dupont', 'Alice', 'Paris');
INSERT INTO Archive SELECT * FROM Users WHERE annee < 2020;
-- insertion depuis une requête
UPDATE Users SET ville = 'Lyon' WHERE id = 3;
⚠ Sans WHERE, toutes les lignes sont modifiées !
DELETE FROM Users WHERE id = 3;
⚠ Sans WHERE, toutes les lignes sont supprimées !
CREATE TABLE Filieres (
id INTEGER PRIMARY KEY,
nom TEXT NOT NULL,
annee INTEGER DEFAULT 1
);
CREATE VIEW V_Moyennes AS
SELECT e.nom, AVG(n.note) AS moy
FROM Etudiants e JOIN Notes n ON e.id = n.etudiant_id
GROUP BY e.id; -- vue = requête nommée réutilisable
12 — CTE (Common Table Expressions)
-- CTE simple (WITH) :
WITH Moyennes AS (
SELECT etudiant_id, AVG(note) AS moy
FROM Notes GROUP BY etudiant_id
)
SELECT e.nom, m.moy
FROM Etudiants e JOIN Moyennes m ON e.id = m.etudiant_id
WHERE m.moy > 15;
-- CTE récursive (parcours hiérarchique) :
WITH RECURSIVE Chemin(noeud, profondeur) AS (
SELECT id, 0 FROM Noeuds WHERE id = 1 -- cas de base
UNION ALL
SELECT a.fils, c.profondeur + 1 -- cas récursif
FROM Arcs a JOIN Chemin c ON a.parent = c.noeud
)
SELECT * FROM Chemin;
⚠ Toujours prévoir une condition d'arrêt pour éviter la boucle infinie.
WITH Moyennes AS (
SELECT etudiant_id, AVG(note) AS moy
FROM Notes GROUP BY etudiant_id
)
SELECT e.nom, m.moy
FROM Etudiants e JOIN Moyennes m ON e.id = m.etudiant_id
WHERE m.moy > 15;
-- CTE récursive (parcours hiérarchique) :
WITH RECURSIVE Chemin(noeud, profondeur) AS (
SELECT id, 0 FROM Noeuds WHERE id = 1 -- cas de base
UNION ALL
SELECT a.fils, c.profondeur + 1 -- cas récursif
FROM Arcs a JOIN Chemin c ON a.parent = c.noeud
)
SELECT * FROM Chemin;
⚠ Toujours prévoir une condition d'arrêt pour éviter la boucle infinie.
📖 Rappel de cours
Trois exercices sur le même fil : relier deux tables, agréger par groupe, puis filtrer les groupes obtenus.
⚠ Le piège : INNER JOIN écarte les lignes sans correspondance. Un étudiant sans note disparaît donc du résultat — ce qui est parfois voulu, souvent non. LEFT JOIN le conserverait.
← Exercices SQL pour la prépa — bases de données en CPGE
Exercices du meme theme
La correction commentee, les indices progressifs, l'execution du code dans le navigateur et la verification par l'IA sont reserves aux abonnes.