Exercices de consolidation SQL – Contraintes, Types et Requêtes

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

Objectif

Savoir créer des tables avec des identifiants auto-générés et des contraintes garantissant la cohérence des données.

1. Crée trois tables utilisant différentes méthodes d’auto-incrémentation :

   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
   );
   

   Copy

2. Insère quelques lignes dans chaque table et observe les valeurs générées. Que se passe-t-il si on force une valeur pour id dans les deux tables utilisant IDENTITY ? Explique la différence entre ALWAYS et BY DEFAULT.

3. Supprime les tables, puis recrée une table unique employe avec les colonnes suivantes :

   • id auto-généré
   • prenom et nom obligatoires
   • salaire positif et inférieur à 10 000
   • une contrainte nommée ck_employe_salaire

4. Ajoute ensuite une colonne poste TEXT qui peut être nulle, et impose qu’un salarié ne puisse pas avoir deux fois le même couple (nom, prenom).

Exercice 2 — Types de données et validation

Objectif

Choisir les types adaptés et poser les contraintes de validation adéquates.

1. Crée une table produit avec les colonnes suivantes :

   • id auto-généré,
   • nom obligatoire (TEXT ou VARCHAR),
   • prix de type NUMERIC(10,2) et contrainte CHECK (prix BETWEEN 0 AND 10000),
   • stock entier non négatif.

2. Insère plusieurs produits valides puis un produit invalide (prix négatif, stock nul, etc.). Que se passe-t-il ? Observe le message d’erreur généré par PostgreSQL.

3. Renomme la contrainte CHECK existante avec un nom explicite (ck_produit_prix) et ajoute une contrainte UNIQUE sur le nom du produit.

4. Explique la différence entre NUMERIC(10,2) et REAL dans ce contexte. Pourquoi l’un est-il préférable à l’autre ?

Exercice 3 — Contraintes de référence et politiques d’effacement

Objectif

Relier des tables entre elles et comprendre les stratégies ON DELETE.

1. Crée une table client :

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

   Copy

2. Crée une table commande liée à client :

   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
   );
   

   Copy

3. Insère quelques clients et commandes associées. Supprime un client, puis observe l’effet sur ses commandes.

4. Recrée la table avec ON DELETE RESTRICT et compare les comportements.

5. Explique dans quels cas on privilégierait l’une ou l’autre politique.

Exercice 4 — Jointures : combiner les données

Objectif

Comprendre les différences entre les types de jointures et savoir les illustrer.

1. Crée deux tables simples :

   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');
   

   Copy

2. Exécute et analyse :

   SELECT * FROM a INNER JOIN b USING(id);
   SELECT * FROM a LEFT JOIN b USING(id);
   SELECT * FROM a RIGHT JOIN b USING(id);
   SELECT * FROM a FULL JOIN b USING(id);
   

   Copy

3. Dessine les ensembles correspondants ou décris en mots les lignes présentes dans chaque résultat.

4. Reformule la requête LEFT JOIN ci-dessus sous forme équivalente utilisant NOT EXISTS ou IS NULL.

5. Décris un cas concret (dans un schéma client/commande par exemple) où un FULL JOIN est utile.

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

Objectif

Utiliser GROUP BY, HAVING, et des sous-requêtes pour extraire des informations synthétiques.

1. Reprends la table produit et ajoute une table categorie :

   CREATE TABLE categorie (
     id SERIAL PRIMARY KEY,
     nom TEXT
   );
   ALTER TABLE produit ADD COLUMN id_categorie INT REFERENCES categorie(id);
   

   Copy

2. Insère plusieurs catégories et produits.

3. Calcule par catégorie :

   • le nombre de produits,
   • le prix moyen,
   • le prix maximum et minimum.

4. Affiche uniquement les catégories dont le prix moyen est supérieur à 100.

5. Rédige une requête qui retourne les produits plus chers que la moyenne globale de tous les produits.

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

Objectif

Explorer des contraintes plus complexes et leur validation.

1. Crée une table reservation :

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

   Copy

2. Ajoute une contrainte CHECK (fin >= debut) et teste des cas valides / invalides.

3. Supprime la contrainte et recrée-la avec NOT VALID, puis valide-la ensuite avec :

   ALTER TABLE reservation VALIDATE CONSTRAINT ck_reservation_dates;
   

   Copy

4. Explique l’intérêt du mot-clé NOT VALID dans une base en production.

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

Objectif

Aller plus loin dans la compréhension des requêtes imbriquées et des comparaisons par groupe.

1. Reprends les tables client et commande. Ajoute une colonne montant NUMERIC(10,2) dans commande.

2. Calcule pour chaque client le montant total dépensé (SUM(montant)).

3. Trouve le client ayant dépensé le plus.

4. Même question, mais sans utiliser MAX() : avec une sous-requête corrélée.

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

Objectif

Différencier PRIMARY KEY, UNIQUE, et FOREIGN KEY.

1. Crée une table departement :

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

   Copy

2. Crée une table employe avec une clé étrangère vers departement(code).

3. Explique les différences entre :

   • PRIMARY KEY
   • UNIQUE
   • FOREIGN KEY et donne un exemple où chacune est pertinente.