Modèle relationnel et introduction à SQL

Présentation & objectifs

Dans cette activité, vous approfondirez votre compréhension du modèle relationnel et découvrirez les bases du SQL (Structured Query Language). À la fin de cette session, vous serez capable de :

• Comprendre et appliquer les concepts de clés primaires et étrangères dans un contexte pratique
• Créer des tables SQL simples avec des contraintes d’intégrité
• Écrire des requêtes SQL basiques pour interroger et manipuler des données
• Analyser l’impact des contraintes d’intégrité sur les opérations de la base de données

Contexte

Cette activité s’appuie sur le modèle de données que vous avez exploré dans l’activité précédente, concernant les clients, les produits et les ventes d’une entreprise de vente en ligne. Votre tâche est d’utiliser SQL pour interagir avec cette base de données, en vous concentrant sur l’intégrité des données et les relations entre les tables.

La base de données SALES contient les tables Customer, Product et Sale. Vous utiliserez ces tables pour effectuer diverses opérations SQL, en assurant l’intégrité des données tout au long du processus.

Table Customer {
  id int [pk]
  name varchar(100)
  address text
  phone varchar(20)
}

Table Product {
  id int [pk]
  description text
  price decimal(10,2)
}

Table Sale {
  id int [pk]
  ref_customer int [ref: > Customer.id]
  ref_product int [ref: > Product.id]
  date date
}

Création des tables

La syntaxe générale pour créer une table en SQL est la suivante :

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1),
    CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES other_table(column)
);

Exemple : Création d’une table “Supplier”

CREATE TABLE Supplier (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Dans cet exemple :

• id est défini comme clé primaire
• name ne peut pas être nul (contrainte NOT NULL)
• email doit être unique pour chaque fournisseur

Exercice 1 : Créez une nouvelle table nommée Category avec les colonnes suivantes :

• id (entier, clé primaire)
• name (chaîne de caractères, non nul)
• description (chaîne de caractères)

Assurez-vous d’utiliser les types de données appropriés et d’ajouter les contraintes nécessaires.

Exercice 2 : Après avoir créé la table Category, représentez le schéma complet de la base de données en utilisant le DBML. Écrivez le code DBML pour toutes les tables de la base de données, incluant Customer, Product, Sale, Supplier et la nouvelle table Category.

SELECT et WHERE : Les bases de la sélection

La syntaxe générale pour interroger une table en SQL est la suivante :

SELECT columns
FROM table
[WHERE condition]
[GROUP BY columns [HAVING condition]]
[ORDER BY columns [ASC|DESC]];

SELECT nous permet de choisir les colonnes que nous voulons voir, tandis que WHERE filtre les lignes selon certaines conditions.

Recherche de motifs avec LIKE

L’opérateur LIKE est utilisé pour la recherche de motifs dans les chaînes. Il utilise deux caractères spéciaux :

• % représente zéro, un ou plusieurs caractères
• _ représente un seul caractère

Exemple :

SELECT name, phone
FROM Customer
WHERE address LIKE '%Paris%';

Cette requête trouve tous les clients dont l’adresse contient “Paris”.

Exercice 3 : Écrivez une requête pour trouver tous les produits dont la description contient le mot “Ordinateur portable”.

Utilisation des opérateurs de comparaison

Les opérateurs de comparaison (<, >, <=, >=, =, <>) peuvent être utilisés pour filtrer les résultats basés sur des valeurs numériques ou des dates.

Exemple : Tous les produits dont le prix est supérieur à 100.

SELECT *
FROM Product
WHERE price > 100;

Exercice 4 : Écrivez une requête pour trouver toutes les ventes effectuées avant le 1er janvier 2024.

Les jointures

Les jointures permettent de combiner des données de plusieurs tables. Il existe plusieurs types de jointures, mais la plus courante est la jointure interne.

Exemple : Jointure entre les tables Sale, Customer et Product.

SELECT c.name, p.description, s.date
FROM Sale s
JOIN Customer c ON s.ref_customer = c.id
JOIN Product p ON s.ref_product = p.id;

Exercice 5 : Écrivez une requête pour afficher le nom du client, le prix du produit et la date pour toutes les ventes où le prix du produit est supérieur à 500.

Modification de la structure des tables

La commande ALTER TABLE permet de modifier la structure d’une table existante. Voici quelques opérations courantes :

1. Ajouter une nouvelle colonne :

   ALTER TABLE table_name
   ADD COLUMN column_name data_type constraints;

2. Modifier une colonne existante :

   ALTER TABLE table_name
   ALTER COLUMN column_name SET data_type constraints;

3. Ajouter une contrainte :

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name constraint_definition;

Exemple d’ajout d’un nouveau champ qui est aussi une clé étrangère :

ALTER TABLE Product
ADD COLUMN ref_category INT,
ADD CONSTRAINT fk_category
    FOREIGN KEY (ref_category) 
    REFERENCES Category(id);

Exercice 6 : Modifiez la table Customer pour ajouter une contrainte assurant que le numéro de téléphone n’est pas vide.

Intégrité des données

L’intégrité des données est maintenue grâce aux contraintes de clés primaires et étrangères. Voici comment insérer des données dans une table :

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Exercice 7 : Essayez d’insérer un nouveau produit avec un ref_category qui n’existe pas dans la table Category. Que se passe-t-il ?

Exercice 8 : Maintenant, essayez d’insérer un produit sans spécifier de ref_category. Que se passe-t-il ? Expliquez le résultat.

Négation

La négation en SQL permet de trouver les enregistrements qui ne correspondent pas à certains critères.

Exemple : Tous les clients qui n’ont jamais fait d’achat.

SELECT c.name
FROM Customer c
WHERE c.id NOT IN (SELECT DISTINCT ref_customer FROM Sale);

Exercice 9 : Écrivez une requête pour trouver tous les produits qui n’ont jamais été vendus.

Comptages et agrégations

Les fonctions d’agrégation comme COUNT(), SUM(), AVG(), MAX(), MIN() permettent d’effectuer des calculs sur un ensemble de valeurs.

Exercice 10 : Écrivez une requête pour trouver le montant moyen des ventes par produit. Utilisez la fonction AVG() et groupez par ref_product.

Testez vos connaissances