Jan 1, 0001

Correction – Exercices SQL (PostgreSQL)

Table of Contents

Astuce pratique : exécutez chaque exercice dans une transaction si on veut facilement revenir en arrière pendant tes tests.

BEGIN;
-- … vos essais …
ROLLBACK;  -- ou COMMIT si on veut conserver

Exercice 1 — Colonnes auto-générées et contraintes de base

1.1 Comparaison des méthodes d’auto-incrément

DROP TABLE IF EXISTS test_serial, test_identity_always, test_identity_default;

CREATE TABLE test_serial (
  id SERIAL PRIMARY KEY,
  libelle TEXT
);

CREATE TABLE test_identity_always (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  libelle TEXT
);

CREATE TABLE test_identity_default (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  libelle TEXT
);

INSERT INTO test_serial (libelle) VALUES ('a');
INSERT INTO test_identity_always (libelle) VALUES ('b');
INSERT INTO test_identity_default (libelle) VALUES ('c');

-- Forcer une valeur d'id :
-- ALWAYS : doit échouer sans OVERRIDING
INSERT INTO test_identity_always (id, libelle) VALUES (100, 'x');          -- ❌ erreur attendue

-- BY DEFAULT : accepté
INSERT INTO test_identity_default (id, libelle) VALUES (100, 'y');         -- ✅

-- Démonstration OVERRIDING pour ALWAYS :
INSERT INTO test_identity_always (id, libelle) OVERRIDING SYSTEM VALUE
VALUES (100, 'x-override');                                                -- ✅

À observer

  • ALWAYS refuse une valeur fournie par l’utilisateur (sauf OVERRIDING SYSTEM VALUE).
  • BY DEFAULT accepte une valeur explicite ou en génère une.
  • SERIAL crée une séquence séparée (regarde \d test_serial).

1.2 Schéma « employe » lisible et sûr

DROP TABLE IF EXISTS employe;

CREATE TABLE employe (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  prenom TEXT NOT NULL,
  nom TEXT NOT NULL,
  salaire NUMERIC(10,2) NOT NULL,
  CONSTRAINT ck_employe_salaire CHECK (salaire > 0 AND salaire < 10000)
);

-- Unicité (nom, prenom) pour éviter les doublons exacts de personnes
CREATE UNIQUE INDEX uq_employe_nom_prenom ON employe(nom, prenom);

-- Test
INSERT INTO employe (prenom, nom, salaire) VALUES ('Alice','Durand', 2500);
INSERT INTO employe (prenom, nom, salaire) VALUES ('Alice','Durand', 2500); -- ❌ viol UNIQUE
INSERT INTO employe (prenom, nom, salaire) VALUES ('Bob','Martin', -10);    -- ❌ viol CHECK

À retenir

  • Préférer IDENTITY (standard) à SERIAL (historique).
  • ALWAYS vs BY DEFAULT : contrôle d’override.
  • Noms explicites de contraintes (lecture, logs d’erreurs).

Exercice 2 — Types de données et validation

DROP TABLE IF EXISTS produit;

CREATE TABLE produit (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom TEXT NOT NULL,
  prix NUMERIC(10,2) NOT NULL,
  stock INT NOT NULL,
  CONSTRAINT ck_produit_prix CHECK (prix BETWEEN 0 AND 10000),
  CONSTRAINT ck_produit_stock CHECK (stock >= 0)
);

-- Insertion valides
INSERT INTO produit (nom, prix, stock) VALUES
('Stylo', 1.20, 100),
('Cahier', 2.50, 50);

-- Insertion invalides (déclenchent des erreurs)
INSERT INTO produit (nom, prix, stock) VALUES ('Livre', -5, 10);   -- ❌ prix < 0
INSERT INTO produit (nom, prix, stock) VALUES ('Gomme', 1.00, -1); -- ❌ stock < 0

Renommer + UNIQUE

ALTER TABLE produit RENAME CONSTRAINT ck_produit_prix TO ck_produit_prix_nonneg_limite;

ALTER TABLE produit ADD CONSTRAINT uq_produit_nom UNIQUE (nom);
-- Test
INSERT INTO produit (nom, prix, stock) VALUES ('Stylo', 0.80, 20); -- ❌ viol UNIQUE

Pourquoi NUMERIC(10,2) ?

  • Représentation exacte en décimal (pas d’erreur binaire) → idéal pour les montants.
  • REAL/DOUBLE PRECISION = approximations binaires → à éviter pour des prix.

À retenir

  • NUMERIC(10,2) pour l’argent ; REAL/DOUBLE pour mesures scientifiques.
  • BETWEEN est inclusif (0 et 10000 autorisés si bornes inclusives).

Exercice 3 — Références et politiques ON DELETE

DROP TABLE IF EXISTS commande, client;

CREATE TABLE client (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom TEXT NOT NULL
);

CREATE TABLE commande (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  id_client BIGINT REFERENCES client(id) ON DELETE CASCADE,
  date_commande DATE NOT NULL
);

INSERT INTO client(nom) VALUES ('Alpha'), ('Beta') RETURNING id;
-- suppose : 1=Alpha, 2=Beta

INSERT INTO commande(id_client, date_commande) VALUES
(1, '2025-01-10'), (1, '2025-02-10'), (2, '2025-03-05');

-- Supprimer Alpha : ses commandes doivent partir (CASCADE)
DELETE FROM client WHERE id = 1;

SELECT * FROM commande; -- → ne restent que celles de Beta

Comparer avec RESTRICT

DROP TABLE IF EXISTS commande;

CREATE TABLE commande (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  id_client BIGINT REFERENCES client(id) ON DELETE RESTRICT,
  date_commande DATE NOT NULL
);

-- Recréation données
INSERT INTO commande(id_client, date_commande) VALUES (2,'2025-04-01');

-- Tenter de supprimer Beta :
DELETE FROM client WHERE id = 2;    -- ❌ RESTRICT bloque la suppression

À retenir

  • CASCADE supprime automatiquement les lignes filles.
  • RESTRICT empêche de supprimer un parent utilisé.
  • Choisir selon le sens métier et les exigences d’historique.

Exercice 4 — Jointures

DROP TABLE IF EXISTS a, b;

CREATE TABLE a (id INT, val TEXT);
CREATE TABLE b (id INT, val TEXT);

INSERT INTO a VALUES (1,'a1'), (2,'a2'), (3,'a3');
INSERT INTO b VALUES (2,'b2'), (3,'b3'), (4,'b4');

-- 1) INNER : intersection (id présents dans a ET b)
SELECT * FROM a INNER JOIN b USING(id);

-- 2) LEFT : tous les a + correspondances b (b peut être NULL)
SELECT * FROM a LEFT JOIN b USING(id);

-- 3) RIGHT : tous les b + correspondances a
SELECT * FROM a RIGHT JOIN b USING(id);

-- 4) FULL : union avec NULL côté manquant
SELECT * FROM a FULL JOIN b USING(id);

Équivalents « sans jointure » (anti-joins)

Clients sans séjour (exemple de pattern) :

-- via LEFT JOIN IS NULL
SELECT c.*
FROM client c
LEFT JOIN commande o ON o.id_client = c.id
WHERE o.id IS NULL;

-- via NOT EXISTS
SELECT c.*
FROM client c
WHERE NOT EXISTS (
  SELECT 1 FROM commande o WHERE o.id_client = c.id
);

À retenir

  • INNER = intersection, LEFT/RIGHT = conservation côté gauche/droit, FULL = union.
  • Anti-join : LEFT … IS NULL ou NOT EXISTS (souvent plus lisible et performant).

Exercice 5 — Agrégats et sous-requêtes

Préparer catégories

DROP TABLE IF EXISTS produit CASCADE;
DROP TABLE IF EXISTS categorie;

CREATE TABLE categorie (
  id SERIAL PRIMARY KEY,
  nom TEXT NOT NULL
);

CREATE TABLE produit (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom TEXT NOT NULL UNIQUE,
  prix NUMERIC(10,2) NOT NULL,
  stock INT NOT NULL CHECK (stock >= 0),
  id_categorie INT REFERENCES categorie(id)
);

INSERT INTO categorie(nom) VALUES ('Papeterie'), ('Livres');

INSERT INTO produit(nom, prix, stock, id_categorie) VALUES
('Stylo', 1.20, 100, 1),
('Cahier', 2.50, 50, 1),
('Roman', 12.90, 10, 2),
('BD', 9.50, 20, 2);

Statistiques par catégorie

SELECT c.nom AS categorie,
       COUNT(*)                  AS nb_produits,
       ROUND(AVG(p.prix), 2)     AS prix_moyen,
       MIN(p.prix)               AS prix_min,
       MAX(p.prix)               AS prix_max
FROM categorie c
JOIN produit p ON p.id_categorie = c.id
GROUP BY c.nom
ORDER BY prix_moyen DESC;

Filtre HAVING (prix moyen > 100)

SELECT c.nom, ROUND(AVG(p.prix),2) AS prix_moyen
FROM categorie c
JOIN produit p ON p.id_categorie = c.id
GROUP BY c.nom
HAVING AVG(p.prix) > 100;

Produits plus chers que la moyenne globale

SELECT *
FROM produit
WHERE prix > (SELECT AVG(prix) FROM produit);

À retenir

  • GROUP BY regroupe, HAVING filtre après agrégation.
  • Sous-requête scalaire utile pour comparer à un agrégat global.

Exercice 6 — Contraintes avancées et validation différée

DROP TABLE IF EXISTS reservation;

CREATE TABLE reservation (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  id_client INT,
  debut DATE,
  fin DATE
);

-- Check simple
ALTER TABLE reservation
ADD CONSTRAINT ck_reservation_dates CHECK (fin >= debut);

-- Tests
INSERT INTO reservation (id_client, debut, fin) VALUES (1,'2025-06-01','2025-06-10'); -- ✅
INSERT INTO reservation (id_client, debut, fin) VALUES (1,'2025-06-10','2025-06-01'); -- ❌ check_violation

NOT VALID → VALIDATE

ALTER TABLE reservation DROP CONSTRAINT ck_reservation_dates;

ALTER TABLE reservation
ADD CONSTRAINT ck_reservation_dates CHECK (fin >= debut) NOT VALID;

-- Ici, corrections de données potentielles si base déjà peuplée…

ALTER TABLE reservation
VALIDATE CONSTRAINT ck_reservation_dates;  -- vérifie tout l'historique

À retenir

  • NOT VALID permet d’ajouter une contrainte sans bloquer immédiatement la prod ; on corrige, puis on VALIDATE.
  • Très utilisé en migration.

Exercice 7 — Agrégation et sous-requêtes corrélées

ALTER TABLE commande ADD COLUMN montant NUMERIC(10,2);

UPDATE commande SET montant = 100 WHERE montant IS NULL; -- jeu de test simple

-- Total dépensé par client
SELECT c.id, c.nom, COALESCE(SUM(o.montant),0) AS total
FROM client c
LEFT JOIN commande o ON o.id_client = c.id
GROUP BY c.id, c.nom
ORDER BY total DESC;

Client ayant dépensé le plus (avec MAX)

WITH totaux AS (
  SELECT id_client, SUM(montant) AS total
  FROM commande
  GROUP BY id_client
)
SELECT c.id, c.nom, t.total
FROM totaux t
JOIN client c ON c.id = t.id_client
WHERE t.total = (SELECT MAX(total) FROM totaux);

Sans MAX() (sous-requête corrélée)

WITH totaux AS (
  SELECT id_client, SUM(montant) AS total
  FROM commande
  GROUP BY id_client
)
SELECT c.id, c.nom, t1.total
FROM totaux t1
JOIN client c ON c.id = t1.id_client
WHERE NOT EXISTS (
  SELECT 1 FROM totaux t2
  WHERE t2.total > t1.total
);

À retenir

  • Pattern « meilleur de sa catégorie » : WHERE NOT EXISTS (SELECT 1 FROM T t2 WHERE t2.total > t1.total).

Exercice 8 — Clés et contraintes d’unicité

DROP TABLE IF EXISTS employe CASCADE;
DROP TABLE IF EXISTS departement CASCADE;

CREATE TABLE departement (
  code TEXT PRIMARY KEY,
  nom  TEXT UNIQUE
);

CREATE TABLE employe (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  prenom TEXT NOT NULL,
  nom    TEXT NOT NULL,
  code_dept TEXT REFERENCES departement(code)
);

INSERT INTO departement(code, nom) VALUES ('IT','Informatique'), ('HR','Ressources humaines');

INSERT INTO employe(prenom, nom, code_dept)
VALUES ('Alice','Durand','IT'), ('Bob','Martin','HR');

-- Tests d'erreur
INSERT INTO departement(code, nom) VALUES ('IT','DUP');  -- ❌ PK (code) déjà utilisé
INSERT INTO departement(code, nom) VALUES ('MK','Informatique'); -- ❌ UNIQUE (nom)
INSERT INTO employe(prenom, nom, code_dept) VALUES ('Chloé','Leroy','NA'); -- ❌ FK inconnue

À retenir

  • PRIMARY KEY : unicité et non-nullité, identifie la ligne.
  • UNIQUE : unicité seule (nullable par défaut, une seule NULL autorisée).
  • FOREIGN KEY : intégrité référentielle (doit référencer une PK/UNIQUE existante).
Pierre-Henri Paris
Pierre-Henri Paris
Associate Professor in Artificial Intelligence

My research interests include Knowlegde Graphs, Information Extraction, and NLP.