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 Client, Produit et Vente. Vous utiliserez ces tables pour effectuer diverses opérations SQL, en assurant l’intégrité des données tout au long du processus.

Table Client {
  Numero int [pk]
  Nom string
  Adresse string
  Telephone string
}

Table Produit {
  Numero int [pk]
  Description string
  Prix float
}

Table Vente {
  Numero int [pk]
  NumeroClient int [ref: > Client.Numero]
  NumeroProduit int [ref: > Produit.Numero]
  Date date
}

Création des tables

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

CREATE TABLE nom_table (
    nom_colonne1 type_donnees contraintes,
    nom_colonne2 type_donnees contraintes,
    ...
    CONSTRAINT nom_contrainte PRIMARY KEY (colonne1, colonne2),
    CONSTRAINT nom_contrainte FOREIGN KEY (colonne) REFERENCES autre_table(colonne)
);

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

CREATE TABLE Fournisseur (
    IDFournisseur INT PRIMARY KEY,
    Nom VARCHAR(100) NOT NULL,
    PersonneContact VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

Dans cet exemple :

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

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

• IDCategorie (entier, clé primaire)
• Nom (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 Categorie, représentez le schéma complet de la base de données en utilisant le DBML (Database Markup Language). Écrivez le code DBML pour toutes les tables de la base de données, incluant Client, Produit, Vente, Fournisseur et la nouvelle table Categorie. Utilisez dbdiagram.io pour générer une représentation visuelle de votre schéma.

SELECT et WHERE : Les bases de la sélection

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

SELECT colonnes
FROM table
[WHERE condition]
[GROUP BY colonnes [HAVING condition]]
[ORDER BY colonnes [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 Nom, Telephone
FROM Client
WHERE Adresse 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 Produit
WHERE Prix > 100;

Pour les dates, vous pouvez également utiliser ces opérateurs. Cependant, la syntaxe exacte pour la manipulation des dates peut varier selon le système de base de données. Dans de nombreux systèmes SQL, vous pouvez utiliser des fonctions comme DATE() pour convertir des chaînes en dates, ou extraire des parties de dates en utilisant des fonctions comme YEAR(), MONTH(), DAY().

Exemple : Toutes les ventes effectuées en 2023.

SELECT *
FROM Vente
WHERE Date > '2023-01-01' AND Date < '2023-12-31';

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

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. Notez qu’en syntaxe SQL, INNER JOIN est équivalent à simplement utiliser JOIN.

Exemple : Jointure entre les tables Vente, Client et Produit.

SELECT c.Nom, p.Description, v.Date
FROM Vente v
JOIN Client c ON v.NumeroClient = c.Numero
JOIN Produit p ON v.NumeroProduit = p.Numero;

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 nom_table
   ADD COLUMN nom_colonne type_donnees contraintes;

2. Modifier une colonne existante :

   ALTER TABLE nom_table
   ALTER COLUMN nom_colonne SET type_donnees contraintes;

3. Ajouter une contrainte :

   ALTER TABLE nom_table
   ADD CONSTRAINT nom_contrainte definition_contrainte;

4. Supprimer une contrainte :

   ALTER TABLE nom_table
   DROP CONSTRAINT nom_contrainte;

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

ALTER TABLE Produit
ADD COLUMN IDCategorie INT,
ADD CONSTRAINT fk_categorie
    FOREIGN KEY (IDCategorie) 
    REFERENCES Categorie(IDCategorie);

Cette commande ajoute une colonne IDCategorie à la table Produit et crée une contrainte de clé étrangère référençant la table Categorie.

Exercice 6 : Modifiez la table Client 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 nom_table (colonne1, colonne2, ...)
VALUES (valeur1, valeur2, ...);

Exercice 7 : Essayez d’insérer un nouveau produit avec un IDCategorie qui n’existe pas dans la table Categorie. Que se passe-t-il ? Comment cela démontre-t-il l’intégrité référentielle ?

Exercice 8 : Maintenant, essayez d’insérer un produit sans spécifier d’IDCategorie. 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.Nom
FROM Client c
WHERE c.Numero NOT IN (SELECT DISTINCT NumeroClient FROM Vente);

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 en SQL permettent d’effectuer des calculs sur un ensemble de valeurs pour retourner une seule valeur. Ces fonctions sont particulièrement utiles lorsqu’elles sont combinées avec la clause GROUP BY, qui regroupe les lignes ayant les mêmes valeurs dans des colonnes spécifiées.

Considérons un exemple utilisant la table Vente :

Vente
-----------------------
Numero | NumeroClient | NumeroProduit | Date       | Montant
1      | 101          | 201           | 2023-01-15 | 100.00
2      | 102          | 202           | 2023-01-16 | 150.00
3      | 101          | 203           | 2023-01-17 | 200.00
4      | 103          | 201           | 2023-01-18 | 100.00

Si nous voulons calculer le montant total des ventes pour chaque client, nous pouvons utiliser la fonction SUM avec GROUP BY :

SELECT NumeroClient, SUM(Montant) as MontantTotal
FROM Vente
GROUP BY NumeroClient;

Cette requête produira :

NumeroClient | MontantTotal
101          | 300.00
102          | 150.00
103          | 100.00

Voici une représentation visuelle du fonctionnement de l’agrégation :

NumeroClient | Montant
101          | 100.00 ┐
101          | 200.00 ┘ SUM = 300.00
102          | 150.00 → SUM = 150.00
103          | 100.00 → SUM = 100.00

La clause GROUP BY regroupe les lignes par NumeroClient, puis la fonction SUM est appliquée à la colonne Montant pour chaque groupe.

Voici quelques fonctions d’agrégation couramment utilisées en SQL :

1. COUNT() : Compte le nombre de lignes
2. SUM() : Calcule la somme d’un ensemble de valeurs
3. AVG() : Calcule la moyenne d’un ensemble de valeurs
4. MAX() : Retourne la valeur maximale d’un ensemble
5. MIN() : Retourne la valeur minimale d’un ensemble

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

Testez vos connaissances

Cette activité vous a permis d’explorer les concepts fondamentaux de SQL tout en renforçant votre compréhension du modèle relationnel. Vous avez appris à créer des tables, maintenir l’intégrité des données et effectuer des requêtes de base.

Maintenant, testons votre compréhension des concepts que nous avons couverts dans cette activité. Essayez de répondre aux questions suivantes :