Correction - Modèle relationnel et introduction à SQL
Exercice 1 : Création de la table Category
CREATE TABLE Category (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT
);
Cette commande crée une table avec :
- Une clé primaire entière
- Un champ nom obligatoire (NOT NULL)
- Un champ description optionnel de type TEXT
Exercice 2 : Schéma DBML complet
Table Customer {
id int [pk]
name varchar(100)
address text
phone varchar(20)
}
Table Product {
id int [pk]
description text
price decimal(10,2)
ref_category int [ref: > Category.id]
}
Table Sale {
id int [pk]
ref_customer int [ref: > Customer.id]
ref_product int [ref: > Product.id]
date date
}
Table Supplier {
id int [pk]
name varchar(100) [not null]
contact_person varchar(100)
email varchar(100) [unique]
}
Table Category {
id int [pk]
name varchar(100) [not null]
description text
}
Requêtes SQL
Exercice 3 : Recherche de motifs avec LIKE
SELECT *
FROM Product
WHERE description LIKE '%Ordinateur portable%';
Exercice 4 : Opérateur de comparaison
SELECT *
FROM Sale
WHERE date < '2024-01-01';
Exercice 5 : Jointures
SELECT c.name, p.description, p.price, s.date
FROM Sale s
JOIN Customer c ON s.ref_customer = c.id
JOIN Product p ON s.ref_product = p.id
WHERE p.price > 500;
Exercice 6 : Ajout d’une contrainte
ALTER TABLE Customer
ADD CONSTRAINT chk_phone_not_empty
CHECK (phone IS NOT NULL AND phone <> '');
Exercice 7 : Test de la contrainte de clé étrangère
Il faut d’abord pensez à ajouter une clef référentielle ref_category
dans produit qui référence la clef primaire de la table Category
en suivant l’exemple donné dans la section précédente :
ALTER TABLE Product
ADD COLUMN ref_category INT,
ADD CONSTRAINT fk_category
FOREIGN KEY (ref_category)
REFERENCES Category(id);
Tentative d’insertion d’un produit avec une catégorie inexistante :
INSERT INTO Product (id, description, price, ref_category)
VALUES (1, 'Test Product', 99.99, 999);
Cette requête échouera avec une violation de contrainte de clé étrangère car la catégorie 999
n’existe pas dans la table Category
:
RuntimeError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "product" violates foreign key constraint "fk_category"
DETAIL: Key (ref_category)=(999) is not present in table "category".
[SQL: INSERT INTO Product (id, description, price, ref_category)
VALUES (6, 'Test Product', 99.99, 999);]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
If you need help solving this issue, send us a message: https://ploomber.io/community
Exercice 8 : Clé étrangère NULL
Test de la clef étrangère :
INSERT INTO Product (id, description, price, ref_category)
VALUES (6, 'Test Product', 99.99, NULL);
Cette insertion réussira puisque une clef étrangère autorise les valeurs NULL. S’il y a une erreur c’est peut-être que l’identifiant de produit choisi existe déjà.
Exercice 9 : Négation
SELECT p.*
FROM Product p
WHERE p.id NOT IN (
SELECT DISTINCT ref_product
FROM Sale
);
Exercice 10 : Agrégation
SELECT
p.id,
p.description,
AVG(p.price) as average_price
FROM Sale s
JOIN Product p ON s.ref_product = p.id
GROUP BY p.id, p.description
ORDER BY p.id;
Points clés à retenir
-
Création de tables : Toujours considérer les types de données et contraintes appropriés.
-
Intégrité des données : Les contraintes de clé étrangère maintiennent l’intégrité référentielle.
-
Valeurs NULL : NULL dans les clés étrangères permet des relations optionnelles.
-
Jointures : Utiliser les JOINs pour combiner les données de plusieurs tables.
-
Agrégation : GROUP BY avec des fonctions comme AVG() permet d’analyser les données à différents niveaux.
N’oubliez pas que SQL est insensible à la casse pour les mots-clés, mais il est conventionnel d’écrire les mots-clés SQL en majuscules pour une meilleure lisibilité.