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é.