Niveau 16 : Structurer avec WITH (CTE)

Leçon

Quand une requête enchaîne plusieurs calculs, les sous-requêtes s'imbriquent et la lecture devient difficile : il faut lire de l'intérieur vers l'extérieur. WITH permet d'écrire les mêmes étapes de haut en bas, comme une recette.

WITH nom AS (SELECT …) crée une table temporaire nommée, utilisable dans la requête qui suit comme une vraie table. On appelle cela une CTE (Common Table Expression, « expression de table commune »). Elle n'existe que le temps de la requête : rien n'est enregistré dans la base.

Exemple : WITH totals AS (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) SELECT seller, total FROM totals WHERE total > 1200. La première étape calcule le total de chaque vendeur ; la seconde filtre ce résultat avec un simple WHERE, puisque total est désormais une colonne ordinaire de totals.

On peut enchaîner plusieurs CTE, séparées par des virgules, sans répéter WITH : WITH a AS (…), b AS (… FROM a …) SELECT … FROM b. Chaque CTE peut utiliser celles qui la précèdent. La requête finale vient après la dernière, sans virgule.

Une CTE peut être lue plusieurs fois dans la même requête. Une CTE monthly qui calcule le total de chaque mois peut servir à la fois dans le FROM et dans une sous-requête qui calcule la moyenne de ces totaux, sans réécrire le calcul.

Conseil de méthode : construis une CTE à la fois. Écris la première étape, exécute-la seule et vérifie son résultat, puis ajoute l'étape suivante. Si le résultat final est faux, tu sais dans quelle étape chercher.

Attention à la ponctuation : pas de point-virgule entre une CTE et la suite de la requête, il la couperait en deux.

Syntaxe

WITH etape1 AS (
  SELECT …
),
etape2 AS (
  SELECT …
  FROM etape1
)
SELECT …
FROM etape2;

Exemple commenté

WITH totals AS (
  SELECT seller, SUM(amount) AS total
  FROM sales
  GROUP BY seller
)
SELECT seller, total
FROM totals
WHERE total > 1200;

Étape 1 : le total par vendeur. Étape 2 : on filtre ce résultat comme une table ordinaire.

Résultat de l’exemple

À retenir

• WITH nom AS (requête) nomme une étape.
• Plusieurs CTE sont séparées par des virgules, sans répéter WITH.
• La requête finale vient après la dernière CTE.

Pièges fréquents

• Mettre un point-virgule après la parenthèse de la CTE coupe la requête en deux.
• Une CTE n'existe que le temps de la requête.

Les 5 exercices du niveau

1. Guidé · Avec une CTE it qui contient les employés du service 'IT', affiche le nom et la date d'embauche de ceux embauchés après 2018 (à partir du 1er janvier 2019). (Table : staff)
2. Entraînement · Avec une CTE, calcule la moyenne de chaque élève (student_id), puis affiche les élèves dont la moyenne est d'au moins 13, arrondie à 2 décimales (avg_grade). (Table : grades)
3. Entraînement · Avec une CTE done qui calcule les heures des tâches terminées (status = 'done') par projet, affiche le nom de chaque projet et ces heures. (Tables : tasks, projects)
4. Entraînement · Avec une CTE monthly qui calcule le total des ventes de chaque mois, affiche les mois dont le total dépasse la moyenne des totaux mensuels. (Table : sales)
5. Défi · Calcule le solde de chaque compte (somme de ses transactions), puis le patrimoine total de chaque propriétaire (owner), du plus riche au moins riche. Les comptes sans transaction sont ignorés. (Tables : transactions, accounts)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion