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

Accès à la base de données

Pour accéder à la base de données vous allez vous connecter à l’espace JupyterHub qui contient déjà toutes les données nécessaires et les notebooks.

Si vous vous êtes connecté sur l’espace JupyterHub de l’UE avant le mail vous indiquant de créer votre espace, utilisez la procédure suivante.

Ne téléchargez les fichiers suivants que si vous désirez les consulter hors de JupyterHub.

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 ne être définie (contrainte NOT NULL)
• email doit être unique pour chaque fournisseur

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

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.

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;

Afin d’exprimer une contrainte sur les dates vous devez exprimer la date avec la syntaxe suivante : to_date('31/12/2030', 'dd/mm/yyyy') pour le 31 décembre 2030, par exemple.

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;

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

3. Ajouter une contrainte :

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name constraint_definition;

Plusieurs types de contraintes peuvent être ajoutés à une table. Remplacez constraints par :

• PRIMARY KEY : Identifie de manière unique chaque enregistrement
• FOREIGN KEY : Établit une relation avec une autre table (voir exemple ci-dessous)
• UNIQUE : Assure que toutes les valeurs d’une colonne sont différentes
• NOT NULL : Empêche l’insertion de valeurs NULL
• CHECK (condition) : Vérifie qu’une condition est respectée pour toutes les lignes

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

Exemple d’une contrainte pour vérifier une condition sur un attribut :

ALTER TABLE Employee
ADD CONSTRAINT chk_salary_positive 
CHECK (salary > 0);

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

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

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. Ces fonctions sont souvent utilisées avec la clause GROUP BY qui permet de regrouper les résultats par une ou plusieurs colonnes.

La clause GROUP BY

La clause GROUP BY permet de regrouper les lignes ayant les mêmes valeurs dans les colonnes spécifiées et de calculer des statistiques pour chaque groupe.

Syntaxe:

SELECT colonne1, colonne2, ..., fonction_agrégation(colonneX)
FROM table
GROUP BY colonne1, colonne2, ...;

Points importants:

• Toute colonne dans le SELECT qui n’est pas dans une fonction d’agrégation doit apparaître dans le GROUP BY
• L’ordre des colonnes dans GROUP BY n’affecte pas le résultat
• Peut être utilisé avec plusieurs colonnes pour créer des sous-groupes plus précis

Exemple :

Supposons que nous ayons les données suivantes dans la table Sale :

Et dans la table Product :

Si nous voulons compter le nombre de ventes par client, nous pouvons utiliser la requête suivante :

SELECT ref_customer, COUNT(*) as nb_achats
FROM Sale
GROUP BY ref_customer;

Voici comment fonctionne l’agrégation :

1. Les lignes sont d’abord regroupées par ref_customer :

2. Puis la fonction d’agrégation COUNT(*) est appliquée à chaque groupe :

Pour combiner les données de plusieurs tables, on peut utiliser des jointures avec GROUP BY :

SELECT c.name, COUNT(*) as nb_achats, SUM(p.price) as total_depense
FROM Sale s
JOIN Customer c ON s.ref_customer = c.id
JOIN Product p ON s.ref_product = p.id
GROUP BY c.name;

Cette requête regroupe les ventes par nom de client et calcule le nombre d’achats et le montant total dépensé par chaque client.

Nous pouvons aussi filtrer les résultats après l’agrégation avec la clause HAVING :

SELECT ref_customer, COUNT(*) as nb_achats
FROM Sale
GROUP BY ref_customer
HAVING COUNT(*) > 2;

Cela nous donnera uniquement les clients ayant effectué plus de 2 achats :

Testez vos connaissances