Cours SQL — requêter, modifier et structurer des données avec SQLite comme terrain d'essai

Objectifs d'apprentissage

À la fin de ce cours, l'apprenant peut :

1. écrire des SELECT avec filtres, tris, limites et alias ;
2. expliquer l'ordre logique d'évaluation d'une requête et en déduire où placer chaque clause ;
3. calculer des agrégats avec GROUP BY et HAVING ;
4. combiner des tables avec INNER JOIN et LEFT JOIN, et prévoir la taille du résultat ;
5. employer sous-requêtes, CTE (WITH) et fonctions de fenêtre ;
6. modifier des données (INSERT, UPDATE, DELETE) dans une transaction et annuler ;
7. définir des tables, contraintes et index ; lire un plan d'exécution ;
8. repérer les pièges de NULL, des doublons et des jointures ;
9. distinguer ce qui relève de la norme et ce qui est propre à un dialecte.

Prérequis

• Avoir lu, ou connaître, les notions de table, clé primaire et clé étrangère (cours « Bases de données » du même dossier).
• Savoir ouvrir Terminal et lire une commande (cours Terminal macOS).
• Un client SQLite en ligne de commande, sqlite3 : sa présence et sa version sur le poste de Stéphane sont non vérifiées. L'installation ne fait pas partie des exercices ; le cours n'en décrit aucune.
• Un dossier de test jetable : tous les fichiers du cours (demo.sql, demo.db) y sont créés, jamais ailleurs.

Parcours et chapitres

1. Préparer le terrain d'essai

SQLite est une bibliothèque de base embarquée : une base est un fichier. Le shell sqlite3 ouvre ou crée ce fichier ; sans nom de fichier, la base est en mémoire et disparaît à la sortie (documentation du shell).

Étape 1 : créer le script demo.sql dans le dossier de test (copier le bloc ci-dessous).

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS ligne_commande;
DROP TABLE IF EXISTS commande;
DROP TABLE IF EXISTS produit;
DROP TABLE IF EXISTS client;

CREATE TABLE client (
  id       INTEGER PRIMARY KEY,
  nom      TEXT NOT NULL,
  ville    TEXT NOT NULL,
  courriel TEXT UNIQUE
);

CREATE TABLE produit (
  id        INTEGER PRIMARY KEY,
  nom       TEXT NOT NULL,
  categorie TEXT NOT NULL,
  prix      REAL NOT NULL CHECK (prix >= 0)
);

CREATE TABLE commande (
  id            INTEGER PRIMARY KEY,
  client_id     INTEGER NOT NULL REFERENCES client(id),
  date_commande TEXT NOT NULL,
  statut        TEXT NOT NULL CHECK (statut IN ('en_cours', 'livree', 'annulee'))
);

CREATE TABLE ligne_commande (
  commande_id INTEGER NOT NULL REFERENCES commande(id),
  produit_id  INTEGER NOT NULL REFERENCES produit(id),
  quantite    INTEGER NOT NULL CHECK (quantite > 0),
  PRIMARY KEY (commande_id, produit_id)
);

INSERT INTO client (id, nom, ville, courriel) VALUES
  (1, 'Alpha',   'Ville-A', 'alpha@example.test'),
  (2, 'Bravo',   'Ville-B', 'bravo@example.test'),
  (3, 'Charlie', 'Ville-A', NULL),
  (4, 'Delta',   'Ville-C', 'delta@example.test'),
  (5, 'Echo',    'Ville-B', NULL);

INSERT INTO produit (id, nom, categorie, prix) VALUES
  (1, 'Cahier',    'Papeterie', 3.50),
  (2, 'Stylo',     'Papeterie', 1.20),
  (3, 'Classeur',  'Papeterie', 4.80),
  (4, 'Lampe',     'Maison',   18.00),
  (5, 'Mug',       'Maison',    6.50),
  (6, 'Sac',       'Maison',   25.00);

INSERT INTO commande (id, client_id, date_commande, statut) VALUES
  (1, 1, '2026-01-10', 'livree'),
  (2, 1, '2026-02-05', 'livree'),
  (3, 2, '2026-02-07', 'annulee'),
  (4, 2, '2026-03-01', 'livree'),
  (5, 3, '2026-03-15', 'en_cours'),
  (6, 4, '2026-03-20', 'livree'),
  (7, 1, '2026-04-02', 'en_cours');

INSERT INTO ligne_commande (commande_id, produit_id, quantite) VALUES
  (1, 1, 10), (1, 2, 20), (2, 4, 1), (3, 5, 4), (4, 1, 5), (4, 3, 2),
  (5, 2, 50), (6, 4, 2), (6, 5, 3), (7, 1, 2), (7, 5, 1);

Les clients, produits et commandes sont inventés. Particularités voulues : deux clients sans courriel (NULL), un client sans commande (Echo), un produit jamais commandé (Sac), une commande annulée, des villes en double.

Étape 2 : créer la base à partir du script.

sqlite3 demo.db < demo.sql

• Contexte : Terminal, dossier de test jetable contenant demo.sql ; sqlite3 présent (non vérifié). La forme avec redirection de l'entrée est décrite dans la documentation du shell.
• Droits : utilisateur normal, écriture dans le dossier courant.
• Effet : crée (ou ouvre) demo.db, y exécute le script ; les tables de même nom existantes sont supprimées puis recréées.
• Vérification : sqlite3 demo.db "SELECT COUNT(*) FROM client;" affiche 5 (forme « SQL en argument » décrite dans la documentation du shell).
• Retour arrière : supprimer le fichier demo.db du dossier de test.

Étape 3 : ouvrir une session interactive et régler l'affichage.

sqlite3 demo.db

• Contexte : même dossier. L'invite change : on est dans le shell SQLite.
• Droits : utilisateur normal ; la session peut modifier demo.db.
• Effet : ouvre la base ; rien n'est modifié tant qu'on n'écrit pas.
• Vérification : l'invite du shell SQLite apparaît ; .tables liste client, commande, ligne_commande, produit.
• Retour arrière : .quit ou Ctrl-D.

Dans la session, saisir ces commandes « point » (elles commencent en début de ligne et tiennent sur une ligne, selon la documentation ; les instructions SQL se terminent par ;) :

.headers on
.mode box
.tables
.schema client

2. Anatomie d'un SELECT et ordre logique

Forme générale (les clauses entre crochets sont facultatives, l'ordre d'écriture est imposé) :

SELECT [DISTINCT] colonnes_ou_expressions
FROM table
[JOIN autre_table ON condition]
[WHERE condition_sur_les_lignes]
[GROUP BY colonnes]
[HAVING condition_sur_les_groupes]
[ORDER BY colonnes]
[LIMIT n [OFFSET m]];

Norme et dialecte : SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INNER/LEFT JOIN, UNION, INTERSECT, EXCEPT sont du SQL courant ; SQLite documente UNION, UNION ALL, INTERSECT et EXCEPT. La limitation du nombre de lignes varie selon les moteurs : voir chapitre 14.

3. Choisir des colonnes, des alias, supprimer les doublons

SELECT nom, ville FROM client;
SELECT nom AS client, ville AS localite FROM client;
SELECT DISTINCT ville FROM client ORDER BY ville;

• Effet : lecture seule. AS donne un nom au résultat ; DISTINCT retire les lignes identiques.
• Vérification : la troisième requête renvoie trois lignes : Ville-A, Ville-B, Ville-C.

4. Filtrer avec WHERE

Opérateurs courants : =, <>, <, <=, >, >=, AND, OR, NOT, IN (...), BETWEEN a AND b, IS NULL, IS NOT NULL. LIKE existe aussi ; sa sensibilité à la casse et ses caractères spéciaux varient : non vérifié, relire la documentation du moteur.

SELECT nom FROM client WHERE ville = 'Ville-A';
SELECT nom, prix FROM produit WHERE categorie = 'Maison' AND prix < 20;
SELECT id, date_commande FROM commande WHERE date_commande BETWEEN '2026-02-01' AND '2026-03-31';
SELECT nom FROM client WHERE ville IN ('Ville-A', 'Ville-C');

• Vérification : première requête : Alpha et Charlie. Deuxième : Lampe (18) et Mug (6,5). Troisième : commandes 2, 3, 4, 5, 6. Quatrième : Alpha, Charlie, Delta.

5. Trier et limiter

SELECT nom, prix FROM produit ORDER BY prix DESC;
SELECT nom, courriel FROM client ORDER BY courriel, nom;
SELECT nom, prix FROM produit ORDER BY prix DESC LIMIT 3;
SELECT nom, prix FROM produit ORDER BY prix DESC LIMIT 3 OFFSET 3;

• Vérification : la première commence par Sac (25) puis Lampe (18). La deuxième place en premier les clients au courriel NULL (Charlie, Echo) : SQLite considère NULL comme inférieur à toute valeur. La troisième : Sac, Lampe, Mug. La quatrième : Classeur, Cahier, Stylo.

6. NULL : la logique à trois valeurs

NULL n'est pas une valeur mais « inconnu ». Toute comparaison avec NULL donne NULL (ni vrai ni faux), et WHERE ne garde que les lignes vraies.

SELECT nom FROM client WHERE courriel = NULL;
SELECT nom FROM client WHERE courriel IS NULL;
SELECT nom FROM client WHERE courriel <> 'alpha@example.test';
SELECT nom FROM client WHERE ville NOT IN ('Ville-A', NULL);
SELECT COUNT(*) AS lignes, COUNT(courriel) AS courriels FROM client;

• Vérification (calculée, non exécutée) : la première ne renvoie rien ; la deuxième Charlie et Echo ; la troisième Bravo et Delta (Charlie et Echo manquent : NULL <> x est inconnu) ; la quatrième ne renvoie rien (un NULL dans NOT IN rend tout inconnu) ; la dernière 5 et 3.

Faits documentés par SQLite : count(*) compte les lignes, count(X) compte les valeurs non NULL ; sum() renvoie NULL s'il n'y a aucune valeur non nulle, alors que total() (propre à SQLite) renvoie 0.0 ; avg(), min(), max() ignorent les NULL. Pour UNIQUE, les NULL sont distincts (plusieurs lignes sans courriel sont permises) ; pour DISTINCT et UNION, ils sont confondus. La page SQLite signale que d'autres moteurs traitent l'unicité des NULL différemment (SQL Server notamment).

7. Agrégations : GROUP BY et HAVING

Fonctions d'agrégat : COUNT, SUM, AVG, MIN, MAX (et group_concat en SQLite, alias string_agg pour la compatibilité avec PostgreSQL et SQL Server). FILTER (WHERE ...) restreint un agrégat ; COUNT(DISTINCT x) compte les valeurs distinctes.

SELECT statut, COUNT(*) AS nb FROM commande GROUP BY statut ORDER BY statut;

SELECT commande_id, ROUND(SUM(l.quantite * p.prix), 2) AS total
FROM ligne_commande l
JOIN produit p ON p.id = l.produit_id
GROUP BY commande_id
HAVING total > 50
ORDER BY commande_id;

• Vérification (calculée) : la première : annulee 1, en_cours 2, livree 4. La seconde : commandes 1 (59,0), 5 (60,0) et 6 (55,5). Totaux de toutes les commandes : 1 → 59,0 ; 2 → 18,0 ; 3 → 26,0 ; 4 → 27,1 ; 5 → 60,0 ; 6 → 55,5 ; 7 → 13,5.
• WHERE filtre les lignes avant le regroupement, HAVING filtre les groupes après.

L'argent doit se stocker en entier (centimes) ou en décimal exact ; REAL (virgule flottante) produit des arrondis : d'où ROUND ici. Le cours choisit REAL pour la simplicité.

8. Jointures

Une jointure associe des lignes de deux tables selon une condition.

SELECT c.nom, o.id AS commande, o.date_commande
FROM commande o
JOIN client c ON c.id = o.client_id
ORDER BY o.id;

SELECT c.nom
FROM client c
LEFT JOIN commande o ON o.client_id = c.id
WHERE o.id IS NULL;

SELECT COUNT(*) AS lignes, COUNT(DISTINCT o.id) AS commandes
FROM commande o
JOIN ligne_commande l ON l.commande_id = o.id;

• Vérification (calculée) : la première renvoie 7 lignes ; la deuxième Echo (le seul client sans commande, repéré grâce au NULL côté droit) ; la troisième 11 et 7.

9. Sous-requêtes

Une sous-requête est un SELECT imbriqué : scalaire (une valeur), liste (pour IN), ou corrélée (qui dépend de la ligne extérieure).

SELECT nom, prix FROM produit WHERE prix > (SELECT AVG(prix) FROM produit);

SELECT nom FROM produit
WHERE id NOT IN (SELECT produit_id FROM ligne_commande);

SELECT p.nom FROM produit p
WHERE NOT EXISTS (SELECT 1 FROM ligne_commande l WHERE l.produit_id = p.id);

• Vérification (calculée) : la moyenne des prix vaut 59/6 environ 9,83 ; première requête : Lampe (18) et Sac (25). Les deux autres : Sac (jamais commandé).

10. Expressions de table commune (CTE)

WITH nom AS (SELECT ...) nomme une requête pour l'utiliser comme une table temporaire le temps d'une instruction (documentation SQLite : « comme des vues temporaires »). Utile pour la lisibilité et pour décomposer un calcul.

WITH total_commande AS (
  SELECT commande_id, SUM(l.quantite * p.prix) AS total
  FROM ligne_commande l
  JOIN produit p ON p.id = l.produit_id
  GROUP BY commande_id
)
SELECT c.nom, ROUND(SUM(t.total), 2) AS total_client
FROM total_commande t
JOIN commande o ON o.id = t.commande_id
JOIN client c ON c.id = o.client_id
WHERE o.statut <> 'annulee'
GROUP BY c.nom
ORDER BY total_client DESC;

• Vérification (calculée) : Alpha 90,5 ; Charlie 60,0 ; Delta 55,5 ; Bravo 27,1 (la commande annulée est exclue ; Echo, sans commande, n'apparaît pas).

CTE récursive : une requête composée dont une partie se réfère à la CTE elle-même, utile pour parcourir arbres et graphes ; les parties non récursives viennent d'abord ; UNION évite les boucles infinies là où UNION ALL ne s'arrête que par une condition ou un LIMIT (SQLite). Exemple minimal :

WITH RECURSIVE compteur(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM compteur WHERE n < 5
)
SELECT n FROM compteur;

• Vérification : cinq lignes, de 1 à 5. Attention : sans la condition WHERE n < 5, la récursion ne s'arrête pas ; toujours prévoir une borne.

MATERIALIZED / NOT MATERIALIZED (SQLite depuis 3.35.0) influencent l'optimiseur ; la documentation déconseille de les utiliser sans raison impérieuse.

11. Fonctions de fenêtre

Une fonction de fenêtre calcule une valeur sans réduire le nombre de lignes, en regardant un groupe de lignes voisines (OVER (PARTITION BY ... ORDER BY ...)). SQLite les propose depuis 3.25.0 (2018-09-15) ; la 3.28.0 a ajouté les cadres GROUPS et EXCLUDE. Fonctions natives : row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value, ainsi que les agrégats utilisés en fenêtre. Restrictions SQLite : pas de DISTINCT dans une fenêtre, usage limité au SELECT et à l'ORDER BY.

SELECT categorie, nom, prix,
       RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang
FROM produit
ORDER BY categorie, rang;

WITH t AS (
  SELECT commande_id, SUM(l.quantite * p.prix) AS total
  FROM ligne_commande l JOIN produit p ON p.id = l.produit_id
  GROUP BY commande_id
)
SELECT o.id, o.date_commande, ROUND(t.total, 2) AS total,
       ROUND(SUM(t.total) OVER (ORDER BY o.date_commande, o.id), 2) AS cumul
FROM t JOIN commande o ON o.id = t.commande_id
ORDER BY o.date_commande, o.id;

• Vérification (calculée) : première requête, Maison : Sac 1, Lampe 2, Mug 3 ; Papeterie : Classeur 1, Cahier 2, Stylo 3. Seconde, colonne cumul : 59,0 ; 77,0 ; 103,0 ; 130,1 ; 190,1 ; 245,6 ; 259,1.

12. Modifier les données : INSERT, UPDATE, DELETE

Toute modification est faite dans une transaction (BEGIN ... COMMIT ou ROLLBACK). Sans BEGIN, SQLite valide chaque instruction aussitôt (mode automatique, documentation).

Exercice complet, annulé à la fin (aucun changement durable) :

BEGIN;

INSERT INTO client (nom, ville) VALUES ('Foxtrot', 'Ville-C') RETURNING id;

UPDATE produit SET prix = prix * 1.10 WHERE categorie = 'Papeterie';
SELECT nom, ROUND(prix, 2) AS prix FROM produit WHERE categorie = 'Papeterie' ORDER BY nom;

DELETE FROM client WHERE nom = 'Foxtrot';
SELECT COUNT(*) FROM client;

ROLLBACK;

• Contexte : session sqlite3 demo.db (chapitre 1) ; SQLite 3.35.0 ou plus pour RETURNING.
• Droits : écriture dans demo.db.
• Effet : pendant la transaction, ajoute un client, augmente de 10 % les prix de papeterie et supprime le client ajouté ; ROLLBACK annule tout.
• Vérification (calculée) : RETURNING affiche 6 ; les prix deviennent Cahier 3,85, Classeur 5,28, Stylo 1,32 ; le COUNT(*) vaut 5. Après ROLLBACK, SELECT nom, prix FROM produit; redonne les prix d'origine.
• Retour arrière : ROLLBACK; (déjà dans le script). Si la session est restée dans une transaction ouverte, saisir ROLLBACK; avant de quitter.

Autres formes :

• INSERT INTO t (a, b) VALUES (1, 2), (3, 4); insère plusieurs lignes ; INSERT ... SELECT ... en insère depuis une requête.
• Upsert : INSERT ... ON CONFLICT (colonne) DO UPDATE SET c = excluded.c ; DO NOTHING ignore le conflit. SQLite : depuis 3.24.0, généralisé en 3.35.0 ; syntaxe empruntée à PostgreSQL, non standard.
• RETURNING : SQLite 3.35.0 ; la page précise que ce n'est pas du SQL standard, que l'ordre des lignes renvoyées est arbitraire et qu'il ne reflète pas les changements faits par des déclencheurs.
• DELETE supprime des lignes, DROP TABLE supprime la table entière (chapitre 13).

13. Définir la structure : DDL et contraintes

Le DDL (« Data Definition Language ») crée et modifie les objets : CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.

Contraintes (page CREATE TABLE de SQLite) :

Actions sur clé étrangère : NO ACTION (défaut), RESTRICT, SET NULL, SET DEFAULT, CASCADE. Types : SQLite a un typage dynamique (affinités) ; les tables STRICT (3.37.0) imposent les types.

Essai de contraintes (dans une transaction annulée) :

PRAGMA foreign_keys = ON;
BEGIN;
INSERT INTO commande (id, client_id, date_commande, statut) VALUES (99, 999, '2026-05-01', 'livree');
INSERT INTO commande (id, client_id, date_commande, statut) VALUES (98, 1, '2026-05-01', 'perdue');
INSERT INTO produit (nom, categorie, prix) VALUES ('Test', 'Maison', -1);
ROLLBACK;

• Contexte : session sqlite3 demo.db, PRAGMA exécuté avant le BEGIN.
• Effet : chaque INSERT doit être refusé : client inexistant (clé étrangère), statut hors liste, prix négatif (CHECK). Aucune ligne n'est ajoutée.
• Vérification : trois messages d'erreur ; SELECT COUNT(*) FROM commande; donne encore 7. La forme exacte des messages dépend de la version (non vérifiée).
• Retour arrière : ROLLBACK;.

Création, modification et suppression d'une table d'essai :

CREATE TABLE avis (
  id         INTEGER PRIMARY KEY,
  produit_id INTEGER NOT NULL REFERENCES produit(id),
  note       INTEGER NOT NULL CHECK (note BETWEEN 1 AND 5),
  cree_le    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE avis ADD COLUMN commentaire TEXT;
DROP TABLE avis;

• Contexte : session sqlite3 demo.db.
• Droits : écriture dans demo.db.
• Effet : crée avis, lui ajoute une colonne, puis la supprime avec ses données (ici vide). ALTER TABLE ... ADD COLUMN est la forme employée ; les autres modifications de ALTER TABLE de SQLite : non vérifiées.
• Vérification : .tables après CREATE montre avis, puis ne la montre plus après DROP.
• Retour arrière : après DROP, recréer la table vide ; les données supprimées ne sont pas récupérables sans sauvegarde.

14. Index et plan d'exécution

Un index accélère la recherche et la jointure sur ses colonnes, au prix de l'espace et des écritures. SQLite : index uniques, partiels (WHERE), sur expression, multi-colonnes ; DROP INDEX les supprime.

EXPLAIN QUERY PLAN SELECT * FROM commande WHERE client_id = 1;
CREATE INDEX idx_commande_client ON commande (client_id);
EXPLAIN QUERY PLAN SELECT * FROM commande WHERE client_id = 1;

• Contexte : session sqlite3 demo.db.
• Effet : EXPLAIN QUERY PLAN n'exécute pas la requête et ne modifie rien ; CREATE INDEX ajoute un index.
• Vérification (selon la documentation) : avant l'index, une ligne SCAN commande (parcours complet) ; après, SEARCH commande USING INDEX idx_commande_client (client_id=?) (recherche ciblée). USING COVERING INDEX signale qu'on n'a pas besoin de lire la table, USE TEMP B-TREE FOR ORDER BY un tri temporaire. La sortie n'est pas stable d'une version à l'autre (changements notés en 3.24.0 et 3.36.0) : ne jamais l'analyser par programme.
• Retour arrière : DROP INDEX idx_commande_client;.

Autres moteurs : PostgreSQL propose EXPLAIN (coûts estimés) et EXPLAIN ANALYZE, qui exécute réellement la requête : pour un INSERT/UPDATE/DELETE, l'entourer de BEGIN et ROLLBACK (conseil de la documentation). Les plans de MySQL/MariaDB et SQL Server ont leurs propres formes : non consultées.

15. Les pièges classiques

Vérifications utiles : SELECT 7 / 2, 7 / 2.0; (attendu en SQLite : 3 et 3.5, à confirmer à l'exécution).

16. Sauvegarder la base de démonstration

Pour une base de démonstration, une copie suffit quand aucune session n'écrit. La documentation de SQLite déconseille la copie brute d'une base en cours d'utilisation (risque de copie incohérente) et propose l'API de sauvegarde en ligne, VACUUM INTO et, dans le shell, .backup et .dump.

Attention, effet notable. Cette commande peut écrire ou écraser un fichier. Lire le contexte, les droits et l’effet attendu indiqués après le bloc ; ne jamais l’exécuter sur un système réel sans validation, d’abord dans un environnement de test.

sqlite3 demo.db ".backup demo-copie.db"sqlite3 demo.db .dump > demo-export.sql

• Contexte : dossier de test, demo.db existant, aucune autre session ouverte dessus ; formes .backup FICHIER et .dump décrites dans la documentation du shell.
• Droits : lecture de demo.db, écriture dans le dossier courant.
• Effet : crée demo-copie.db (copie cohérente) et demo-export.sql (instructions SQL qui reconstruisent la base). > écrase demo-export.sql s'il existe.
• Vérification : sqlite3 demo-copie.db "SELECT COUNT(*) FROM client;" donne 5 ; demo-export.sql contient des CREATE TABLE et des INSERT.
• Retour arrière : supprimer les deux fichiers créés.

Restauration à partir de l'export, vers un nouveau fichier : sqlite3 demo-restaure.db < demo-export.sql (forme décrite dans la documentation du shell pour une sauvegarde .dump). Attention : ne jamais restaurer sur un fichier existant sans copie préalable.

17. Norme SQL et dialectes

La norme est ISO/IEC 9075 « Database Language SQL ». Versions citées par la documentation de PostgreSQL : SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016, SQL:2023. D'après cette même documentation, PostgreSQL « supporte la plupart des fonctions majeures de SQL:2023 », se conforme à au moins 170 des 177 fonctions obligatoires du niveau Core, et aucun SGBD actuel ne revendique la conformité complète au Core SQL:2023. Le texte de la norme est payant ; il n'a pas été consulté.

Différences de dialecte relevées dans les pages consultées :

Exercices et vérifications de fin de cours

Tous sur demo.db, dans le dossier de test. Écrire la requête avant de regarder la correction du cours.

1. Mise en place. Créer demo.sql et demo.db ; vérifier les 5 clients.
2. Filtres. Lister les produits Papeterie de moins de 4 €, triés par prix décroissant. (Attendu : Classeur 4,8 exclu ; Cahier 3,5, Stylo 1,2.)
3. NULL. Lister les clients dont le courriel est inconnu ; expliquer pourquoi = NULL échoue.
4. Agrégation. Nombre de commandes par client, y compris les clients sans commande (indice : LEFT JOIN, COUNT(o.id)). Attendu : Alpha 3, Bravo 2, Charlie 1, Delta 1, Echo 0.
5. Jointure. Chiffre d'affaires par catégorie de produit, commandes annulées exclues. (Attendu, calculé à la main : Papeterie 153,1 ; Maison 80,0 ; total 233,1.)
6. Sous-requête. Produits dont le prix dépasse la moyenne de leur catégorie (sous-requête corrélée).
7. CTE. Réécrire l'exercice 5 avec un WITH.
8. Fenêtre. Pour chaque client, la date de sa première commande (MIN en fenêtre ou row_number).
9. Transaction. Mettre à zéro le prix d'un produit dans un BEGIN, constater, annuler. Que se passe-t-il avec CHECK (prix >= 0) si on met -1 ?
10. Contraintes. Provoquer volontairement une violation de clé étrangère, une d'unicité (courriel déjà présent) et une de CHECK.
11. Index. Comparer EXPLAIN QUERY PLAN avant et après CREATE INDEX, puis supprimer l'index.
12. Sauvegarde. Exporter avec .dump, restaurer vers un nouveau fichier, comparer les nombres de lignes de chaque table.
13. Dialecte. Pour RETURNING et FULL JOIN, dire dans quelle version de SQLite ils sont apparus et pourquoi ils ne sont pas portables sans vérification.

Critères de réussite

• Prévoir le nombre de lignes d'un JOIN et d'un LEFT JOIN avant d'exécuter.
• Expliquer pourquoi WHERE total > 50 échoue et HAVING fonctionne, en s'appuyant sur l'ordre logique.
• Ne jamais écrire UPDATE ou DELETE sans WHERE ni transaction.
• Lire SCAN contre SEARCH dans un plan.
• Nommer au moins trois différences de dialecte et la source qui les établit.