Le Carburant de
l'Alchimie Digitale

Une Intelligence Artificielle (LLM) n'est que le reflet des données qu'elle ingère.

La pureté de vos requêtes SQL dicte l'avenir de vos modèles.

Avant le combat : votre arène

Tous les défis tournent sur un vrai jeu de données PostgreSQL : 50 clients, 200 000 achats, des fantômes et de la pollution.

Installation (une seule fois)
createdb -h localhost -U postgres sqlfun
psql -h localhost -U postgres -d sqlfun -f jeu-de-donnees-sql-fun.sql

Ou dans pgAdmin 4 : créer la base sqlfun, ouvrir le fichier dans l'outil de requête, puis Exécuter (F5).

📥 Télécharger jeu-de-donnees-sql-fun.sql

Activité 1 : Le Poison de l'Hallucination

Vous devez nourrir une API d'Intelligence Artificielle avec votre base `CLIENTS`. Mais la colonne `ville` est polluée : "Mtl", "Montréal ", "MONTREAL", "mtl".

Mission de Purification

Écrivez la requête SQL (UPDATE) utilisant les fonctions de chaînes de caractères pour standardiser cette colonne avant l'export vers l'IA.

🛠️ Armes requises : UPPER(), TRIM(), REPLACE() ou CASE WHEN

Solution 1 : Le Poison de l'Hallucination

TRIM enlève les espaces, UPPER écrase les majuscules, CASE choisit la forme officielle.

Solution
UPDATE clients
SET ville = CASE
  WHEN UPPER(TRIM(ville)) IN ('MTL', 'MONTREAL', 'MONTRÉAL')
    THEN 'Montréal'
  WHEN UPPER(TRIM(ville)) IN ('QUEBEC', 'QUÉBEC') THEN 'Québec'
  ELSE TRIM(ville)
END;

Vérification : SELECT ville, COUNT(*) FROM clients GROUP BY ville; donne 5 villes propres au lieu de 11 variantes.

💀 Mode hardcore : pourquoi UPPER('Montréal') donne-t-il 'MONTRÉAL' et pas 'MONTREAL' ? Quelle extension PostgreSQL enlève les accents ?

Activité 2 : Le Pont vers le JSON (NoSQL)

Vous maîtrisez les jointures relationnelles entre `UTILISATEUR` et `COMMANDES`. Bientôt, nous passerons au NoSQL (MongoDB/Firebase).

Mission d'Architecture

Au tableau : Dessinez comment le résultat d'un LEFT JOIN se traduit visuellement sous forme d'un document JSON imbriqué.

💡 Objectif : Comprendre que le SQL avancé prépare votre cerveau à manipuler des objets complexes pour le Web moderne.

Solution 2 : Le Pont vers le JSON

Chaque client devient un document, ses factures un tableau imbriqué. Le client sans facture, que le LEFT JOIN gardait avec des NULL, reçoit un tableau vide.

Solution (extrait de l'arène : clients et factures)
[
  { "nom": "Alice Tremblay",
    "factures": [ { "facture": 26,  "montant": 251.07 },
                  { "facture": 153, "montant": 156.65 } ] },
  { "nom": "Client 50",
    "factures": [] }
]

Les lignes répétées du JOIN (Alice, Alice, Alice...) disparaissent : l'imbrication remplace la répétition.

Activité 3 : La Machine à JSON

Dessiner le JSON, c'est bien. Le faire fabriquer par PostgreSQL, c'est mieux : la base sait écrire du JSON elle-même.

Mission d'usinage

En une seule requête, produisez les documents d'Alice (client 1) et de Client 50 de la solution précédente.

🛠️ Armes requises : json_build_object(), json_agg() et COALESCE.

Solution 3 : La Machine à JSON

FILTER retire les NULL du LEFT JOIN, COALESCE remplace l'absence par []. C'est exactement ce que l'API IA recevra.

Solution
SELECT json_build_object(
  'nom', clients.nom,
  'factures', COALESCE(
     json_agg(json_build_object('facture', factures.id,
                                'montant', factures.montant))
       FILTER (WHERE factures.id IS NOT NULL), '[]')) AS document
FROM clients
LEFT JOIN factures ON factures.client_id = clients.id
WHERE clients.id IN (1, 50)
GROUP BY clients.id, clients.nom;

Activité 4 : Le Détecteur de Pollution

Avant d'envoyer quoi que ce soit à l'IA, on cherche deux poisons : les doublons et les trous.

Mission d'inspection

1. Quels courriels sont utilisés par plus d'un client ?

2. Combien de clients n'ont aucun courriel ?

🛠️ Armes requises : HAVING COUNT(*) > 1 et COUNT(colonne).

Solution 4 : Le Détecteur de Pollution

COUNT(*) compte les lignes, COUNT(courriel) ignore les NULL : la différence, ce sont les trous.

Solution
SELECT courriel, COUNT(*) AS fois
FROM clients
WHERE courriel IS NOT NULL
GROUP BY courriel
HAVING COUNT(*) > 1;

SELECT COUNT(*) - COUNT(courriel) AS sans_courriel FROM clients;

Dans l'arène : alice@exemple.ca est partagé par deux clients, et 2 clients n'ont pas de courriel.

💀 Mode hardcore : à l'export, écrivez COALESCE(courriel, 'inconnu') : pourquoi est-ce mieux qu'un NULL pour un LLM ?

Activité 5 : Le Garde-Manger

Le tableau de bord des ventes par ville recalcule 200 000 achats à chaque visite. Et si la base gardait le résultat au frais ?

Mission de conservation

Créez un résultat précalculé qu'on lit instantanément, puis qu'on rafraîchit quand on le décide.

🛠️ Armes requises : CREATE MATERIALIZED VIEW et REFRESH MATERIALIZED VIEW.

Solution 5 : Le Garde-Manger

Une vue ordinaire recalcule à chaque lecture. Une vue matérialisée garde une photo : c'est déjà du cache, le principe du caching local de la deuxième partie du cours.

Solution
CREATE MATERIALIZED VIEW ventes_par_ville AS
SELECT clients.ville, COUNT(*) AS nombre_achats,
       SUM(achats.montant) AS total
FROM achats
JOIN clients ON clients.id = achats.client_id
GROUP BY clients.ville;

SELECT * FROM ventes_par_ville;             -- instantané
REFRESH MATERIALIZED VIEW ventes_par_ville; -- données fraîches

L'Épreuve Finale

Le traitement des données relationnelles est le passage obligé vers vos prochains défis.

Bientôt :
API IA, Caching local, Blockchain.

L'Alchimie de demain exige la rigueur d'aujourd'hui.

Flèches gauche/droite pour naviguer