MODULE 2 : SQL INTERMÉDIAIRE
Introduction
Dans ce module, nous allons approfondir les notions fondamentales de SQL en nous
concentrant sur la création et la gestion des bases de données, l'utilisation des jointures pour
relier des tables, ainsi que l'exploitation des fonctions d'agrégation, des fonctions de chaînes et
des fonctions de dates. Nous appliqueront ces notions dans le cadre d'un contexte
professionnel concret, celui de l'entreprise Globatech Solutions.
1. Langage de Définition des Données (DDL)
1.1. CREATE DATABASE
La commande CREATE DATABASE permet de créer une nouvelle base de données. Chaque
base de données sert à stocker des tables et autres objets SQL (vues, procédures, etc.).
Exemple
Nous allons créer une base de données nommée EntrepriseGlobatech :
CREATE DATABASE EntrepriseGlobatech;
Explication
● CREATE DATABASE : C'est la commande SQL pour créer une base de données.
● EntrepriseGlobatech : Nom donné à la base de données.
Une fois la base créée, nous devons l’utiliser pour créer des tables :
USE EntrepriseGlobatech;
1.2. CREATE TABLE
La commande CREATE TABLE permet de créer une table avec des colonnes spécifiques.
Chaque colonne doit avoir un type de données défini.
Exemple
Créons une table nommée Employés :
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50),
nom VARCHAR(50),
salaire DECIMAL(10,2),
date_embauche DATE
);
Explication
● id_employé : Identifiant unique pour chaque employé (clé primaire).
● prénom, nom : Colonnes pour stocker les noms des employés, limitées à 50
caractères.
● salaire : Le salaire est un nombre décimal avec 10 chiffres au total et 2 chiffres après la
virgule.
● date_embauche : Colonne pour la date d'embauche, au format DATE.
1.3. ALTER TABLE
La commande ALTER TABLE est utilisée pour modifier une table existante.
Ajouter une colonne
Ajoutons une colonne poste à la table Employés :
ALTER TABLE Employés ADD poste VARCHAR(50);
Modifier une colonne
Changeons le type de données de la colonne salaire :
ALTER TABLE Employés MODIFY salaire DECIMAL(12,2);
Supprimer une colonne
Supprimons la colonne poste de la table Employés :
ALTER TABLE Employés DROP COLUMN poste;
1.4. DROP TABLE
La commande DROP TABLE permet de supprimer une table, ainsi que toutes les données
qu’elle contient.
Exemple
Supprimons la table Employés :
DROP TABLE Employés;
⚠️ Attention : Cette commande est irréversible. Une fois une table supprimée, toutes les
données qu'elle contenait sont perdues.
2. Langage de Manipulation des Données (DML)
2.1. INSERT INTO
La commande INSERT INTO permet d’ajouter des données dans une table.
Exemple
Ajoutons des données dans la table Employés :
INSERT INTO Employés (id_employé, prénom, nom, salaire, date_embauche)
VALUES (1, 'Oscar', 'Aksanti', 3000.00, '2023-01-10');
Explication
● (id_employé, prénom, nom, salaire, date_embauche) : Liste des colonnes où les
valeurs seront insérées.
● VALUES : Indique les valeurs à insérer dans chaque colonne.
2.2. UPDATE
La commande UPDATE permet de modifier des données existantes.
Exemple
Mettons à jour le salaire de l’employé ayant l’identifiant 1 :
UPDATE Employés
SET salaire = 3500.00
WHERE id_employé = 1;
Explication
● SET : Spécifie la nouvelle valeur pour la colonne.
● WHERE : Condition pour indiquer quelles lignes doivent être mises à jour.
2.3. DELETE
La commande DELETE permet de supprimer des données spécifiques dans une table.
Exemple
Supprimons l'employé ayant l’identifiant 1 :
DELETE FROM Employés WHERE id_employé = 1;
Explication
● DELETE FROM : Spécifie la table à partir de laquelle les données doivent être
supprimées.
● WHERE : Condition pour déterminer quelles lignes supprimer.
2.4. TRUNCATE TABLE
La commande TRUNCATE TABLE supprime toutes les données d'une table, mais conserve sa
structure.
Exemple
Vidons complètement la table Employés :
TRUNCATE TABLE Employés;
3. Les Jointures (JOINS)
Les jointures permettent de combiner les données de plusieurs tables dans une seule requête.
Elles sont essentielles pour travailler avec des bases de données relationnelles.
3.1. INNER JOIN
L'INNER JOIN retourne les lignes qui ont une correspondance dans les deux tables.
Exemple
Supposons que nous ayons deux tables :
Table Employés
id_employé prénom nom id_département
1 Jean Dupont 101
2 Marie Durand 102
3 Paul Martin 103
Table Départements
id_département nom_département
101 Ressources Humaines
102 Comptabilité
Pour afficher les noms des employés et leur département :
SELECT Employé[Link]énom, Employé[Link],
Départements.nom_département
FROM Employés
INNER JOIN Départements
ON Employés.id_département = Départements.id_département;
Résultat
prénom nom nom_département
Jean Dupont Ressources Humaines
Marie Durand Comptabilité
3.2. LEFT JOIN
Le LEFT JOIN retourne toutes les lignes de la table de gauche, même si elles n’ont pas de
correspondance dans la table de droite.
Exemple
Pour afficher tous les employés, même ceux qui n'ont pas de département :
SELECT Employé[Link]énom, Employé[Link], Départements.nom_département
FROM Employés
LEFT JOIN Départements
ON Employés.id_département = Départements.id_département;
Résultat
prénom nom nom_département
Jean Dupont Ressources Humaines
Marie Durand Comptabilité
Paul Martin NULL
3.3. RIGHT JOIN
Le RIGHT JOIN retourne toutes les lignes de la table de droite, même si elles n’ont pas de
correspondance dans la table de gauche.
Exemple
Pour afficher tous les départements, même ceux sans employés :
SELECT Employé[Link]énom, Employé[Link], Départements.nom_département
FROM Employés
RIGHT JOIN Départements
ON Employés.id_département = Départements.id_département;
3.4. FULL JOIN
Le FULL JOIN retourne toutes les lignes lorsqu'il y a une correspondance dans l'une des
tables. Si aucune correspondance n’est trouvée, des valeurs NULL seront retournées.
Exemple
Pour afficher tous les employés et tous les départements, qu'ils aient ou non des
correspondances :
SELECT Employé[Link]énom, Employé[Link], Départements.nom_département
FROM Employés
FULL JOIN Départements
ON Employés.id_département = Départements.id_département;
4. Les Fonctions d'Agrégation
Les fonctions d'agrégation permettent de calculer des valeurs à partir d’un ensemble de lignes.
4.1. COUNT
Retourne le nombre total de lignes.
Exemple
Compter le nombre total d’employés :
SELECT COUNT(*) AS nombre_employés FROM Employés;
4.2. SUM
Retourne la somme des valeurs d'une colonne numérique.
Exemple
Calculons la somme des salaires :
SELECT SUM(salaire) AS somme_salaires FROM Employés;
4.3. AVG
Retourne la moyenne des valeurs d'une colonne.
Exemple
Calculons le salaire moyen :
SELECT AVG(salaire) AS salaire_moyen FROM Employés;
4.4. MAX et MIN
Retourne respectivement la valeur maximale et minimale d'une colonne.
Exemple
Trouver le salaire le plus élevé et le plus bas :
SELECT MAX(salaire) AS salaire_max, MIN(salaire) AS salaire_min FROM
Employés;
5. Les Fonctions de Chaînes (String Functions)
Les fonctions de chaînes sont utilisées pour manipuler et transformer les données textuelles
dans une base de données. Ces fonctions permettent de modifier, combiner ou extraire des
parties de texte.
5.1. CONCAT
Concatène (combine) plusieurs chaînes de caractères en une seule.
Exemple
Ajouter un espace entre le prénom et le nom d’un employé :
SELECT CONCAT(prénom, ' ', nom) AS nom_complet FROM Employés;
Résultat
nom_complet
Jean Dupont
Marie Durand
Paul Martin
5.2. LOWER
Convertit une chaîne de caractères en minuscules.
Exemple
Afficher tous les noms en minuscules :
SELECT LOWER(nom) AS nom_minuscule FROM Employés;
Résultat
nom_minuscule
dupont
durand
martin
5.3. UPPER
Convertit une chaîne de caractères en majuscules.
Exemple
Afficher tous les prénoms en majuscules :
SELECT UPPER(prénom) AS prénom_majuscule FROM Employés;
Résultat
prénom_majuscule
JEAN
MARIE
PAUL
5.4. LENGTH
Retourne le nombre de caractères dans une chaîne (y compris les espaces).
Exemple
Afficher la longueur des noms :
SELECT nom, LENGTH(nom) AS longueur_nom FROM Employés;
Résultat
nom longueur_nom
Dupont 6
Durand 6
Martin 6
5.5. TRIM
Supprime les espaces (ou autres caractères définis) au début et à la fin d’une chaîne de
caractères.
Exemple
Nettoyer les prénoms avec des espaces superflus :
SELECT TRIM(prénom) AS prénom_nettoyé FROM Employés;
5.6. SUBSTRING
Extrait une partie spécifique d'une chaîne de caractères.
Syntaxe
SUBSTRING(chaîne, position_départ, longueur)
Exemple
Afficher les trois premiers caractères des prénoms :
SELECT prénom, SUBSTRING(prénom, 1, 3) AS début_prénom FROM Employés;
Résultat
prénom début_prénom
Jean Jea
Marie Mar
Paul Pau
6. Les Fonctions de Dates (Date Functions)
Les fonctions de dates permettent de manipuler et d'extraire des informations sur les dates
dans une base de données.
6.1. NOW
Retourne la date et l’heure actuelles.
Exemple
Afficher la date et l’heure du système :
SELECT NOW() AS date_actuelle;
6.2. DAY, MONTH, YEAR
Ces fonctions permettent d'extraire le jour, le mois ou l'année d'une date.
Exemple
Extraire les composants de la date d’embauche :
SELECT DATE(d_embauche), DAY(d_embauche) AS jour, MONTH(d_embauche) AS
mois, YEAR(d_embauche) AS année
FROM Employés;
6.3. DATE_ADD et DATE_SUB
Ajoute ou soustrait des jours, mois ou années à une date.
Exemple : Ajouter 30 jours à une date
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY) AS date_plus_30;
Exemple : Soustraire 1 an à une date
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR) AS date_moins_1_an;
6.4. DATEDIFF
Retourne le nombre de jours entre deux dates.
Exemple
Calculer le nombre de jours depuis l’embauche d’un employé :
SELECT prénom, nom, DATEDIFF(NOW(), d_embauche) AS
jours_depuis_embauche
FROM Employés;
6.5. Exemple Complet sur les Dates
Supposons que nous avons la table suivante :
Table Employés
id_employé prénom nom d_embauche
1 Jean Dupont 2020-01-15
2 Marie Durand 2021-03-20
3 Paul Martin 2019-06-10
Affichons le temps écoulé en années, mois et jours depuis leur embauche :
SELECT prénom, nom,
YEAR(NOW()) - YEAR(d_embauche) AS années,
MONTH(NOW()) - MONTH(d_embauche) AS mois,
DAY(NOW()) - DAY(d_embauche) AS jours
FROM Employés;
7. Les Contraintes (Constraints)
Les contraintes sont utilisées pour imposer des règles sur les données dans les tables d'une
base de données. Elles assurent l'intégrité et la validité des données. Voici les contraintes les
plus courantes en SQL :
7.1. PRIMARY KEY : CLE PRIMAIRE
La contrainte PRIMARY KEY identifie de manière unique chaque enregistrement dans une table.
Une table ne peut avoir qu'une seule clé primaire, et les colonnes définies comme clés
primaires ne peuvent pas contenir de valeurs NULL ni de doublons.
Exemple : Créer une clé primaire
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50),
nom VARCHAR(50)
);
7.2. FOREIGN KEY : CLE ETRANGERE
La contrainte FOREIGN KEY est utilisée pour lier deux tables. Elle fait référence à une clé
primaire d'une autre table et assure l'intégrité référentielle.
Exemple : Définir une clé étrangère
CREATE TABLE Départements (
id_département INT PRIMARY KEY,
nom_département VARCHAR(50)
);
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50),
nom VARCHAR(50),
id_département INT,
FOREIGN KEY (id_département) REFERENCES
Départements(id_département)
);
Remarque :
Si vous essayez d'insérer un id_département dans la table Employés qui n'existe pas dans
la table Départements, une erreur sera générée.
7.3. UNIQUE
La contrainte UNIQUE empêche l'insertion de valeurs en double dans une colonne ou un
ensemble de colonnes. Contrairement à PRIMARY KEY, plusieurs contraintes UNIQUE peuvent
être appliquées à une table.
Exemple : Créer une contrainte UNIQUE
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
7.4. NOT NULL
La contrainte NOT NULL empêche une colonne d'accepter des valeurs NULL.
Exemple : Définir une colonne comme NOT NULL
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50) NOT NULL,
nom VARCHAR(50) NOT NULL
);
7.5. CHECK
La contrainte CHECK impose une condition pour les valeurs insérées dans une colonne.
Exemple : Appliquer une contrainte CHECK
Limiter les salaires à des montants supérieurs ou égaux à 1000 :
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50),
salaire DECIMAL(10, 2) CHECK (salaire >= 1000)
);
7.6. DEFAULT
La contrainte DEFAULT définit une valeur par défaut pour une colonne si aucune valeur n'est
spécifiée.
Exemple : Ajouter une valeur par défaut
CREATE TABLE Employés (
id_employé INT PRIMARY KEY,
prénom VARCHAR(50),
poste VARCHAR(50) DEFAULT 'Employé'
);
Si aucune valeur n'est spécifiée pour la colonne poste lors de l'insertion, la valeur "Employé"
sera utilisée.
Synthèse
Avec les notions de ce module, vous disposez de tout le nécessaire pour :
● Gérer des tables et bases de données avec DDL et DML.
● Réaliser des jointures avancées entre plusieurs tables.
● Effectuer des analyses avec des fonctions d'agrégation.
● Manipuler des chaînes et des dates pour enrichir leurs analyses.
