Dans cette section, vous trouverez des ressources sur les cours que j'enseigne à savoir de la documentation, du code, des vidéos etc. Dès que je trouve quelque chose d'intéressant à partager, je le mettrai à disposition ici pour mes étudiants.
Travaux dirigés 9
##Auteur : Enseignant
##École : Collège Marie Victorin
##Session : Hiver 2025
##Titre du cours : Introduction aux bases de données
##Titre du programme : Travaux dirigés 9 - Commandes DDL - DML
##Version : 1.0
##Date : 00/00/2025
##Langage et version : SQL
##Moteur : SQL Server Express
/*****************************************************************************************
## =======================================
## Travaux dirigés 9
## Les commandes :
## CREATE DATABASE/TABLE
## ALTER TABLE
## DROP DATABASE/TABLE
## INSERT / DELETE
## =======================================
******************************************************************************************/
-- Ce TD doit être réalisé dans une nouvelle base de données.
-- Créer une nouvelle base de données
-- Nom de la base : TD9
-- Se positionner dans cette nouvelle base pour réaliser le travail
-- *************** A T T E N T I O N ***************
-- ****** REGLES D'INTÉGRITÉ RÉFÉRENCIELLE ********
-- *************************************************
-- CRÉATION DE TABLE
-- La table contenant la clé primaire doit être créée avant la table contenant la clé étrangère
-- La clé primaire doit exister avant la création de la clé étrangère.
-- INSERTION DE DONNÉES
-- Les données de la tables 'maitre' doivent être insérées avant celles de la table "détail".
-- SUPPRESSION DE DONNÉES
-- Les données de la table "détail" celle qui contient la clé étrangère doivent être supprimées
-- AVANT celles de la table contenant la clé primaire.
-- SUPPRESSION DE LA TABLE
-- La table "détail" doit être supprimée AVANT la table 'maitre'
/* ## Énoncé
## Partie I : Création d'une base de données
--===========================================
-- Vérifer l'existence de la base de données avant sa création
-- Vérifier si la base de données existe
*/
-- Version 1
-- Votre code ici
USE master;
GO
IF DB_ID('TD9') IS NOT NULL
BEGIN
-- Supprimer la base de données si elle existe
PRINT 'La base de données TD9 existe. Suppression en cours...';
DROP DATABASE TD9;
END
ELSE
BEGIN
PRINT 'La base de données TD9 n''existe pas.';
END;
-- Créer la base de données
PRINT 'Création de la base de données TD9...';
CREATE DATABASE TD9;
PRINT 'Opération terminée.';
-- Version 2
USE master;
GO
IF DB_ID('TD9') IS NOT NULL
BEGIN
PRINT 'La base de données TD9 existe. Suppression en cours...';
DROP DATABASE TD9;
END
ELSE
BEGIN
PRINT 'La base de données TD9 n''existe pas.';
END;
-- Créer la base de données
PRINT 'Création de la base de données TD9...';
CREATE DATABASE TD9;
PRINT 'Opération terminée.';
USE TD9;
GO
-- Suppression en ordre inverse des tables
-- Suppression de tblProjets
-- Suppression tblArticlesCommandes
-- Suppression de tblCommandes
-- Suppression tblResultatsExamens
-- Suppression tblMessages
-- Suppression tblCours
-- Suppression tblClients
-- Suppression tblEtudiants
-- Suppression tblEmployes
/*******************************************************************/
-- Nettoyage de l'environnement
-- Suppression de tblProjets
DROP TABLE IF EXISTS tblProjets;
-- Suppression de tblCommandes
DROP TABLE IF EXISTS tblCommandes;
-- Suppression tblArticlesCommandes
DROP TABLE IF EXISTS tblArticlesCommandes;
-- Suppression tblResultatsExamens
DROP TABLE IF EXISTS tblResultatsExamens;
-- Suppression tblMessages
DROP TABLE IF EXISTS tblMessages;
-- Suppression tblCours
DROP TABLE IF EXISTS tblCours;
-- Suppression tblProduits
DROP TABLE IF EXISTS tblProduits;
-- Suppression tblEmployes
DROP TABLE IF EXISTS tblEmployes;
-- Suppression tblClients
DROP TABLE IF EXISTS tblClients;
-- Suppression tblEtudiants
DROP TABLE IF EXISTS tblEtudiants;
/************************************************************************************************
## Partie II : Création de tables
## ==============================
## Écrire une requête qui crée la table tblEmployes en respectant les spécifications ci-dessous
Table "tblEmployes" :
EmpID (Entier - Clé primaire)
Nom (Chaîne de caractères)
Prenom (Chaîne de caractères)
DateNaissance (Date)
Salaire (monétaire)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblEmployes (
EmpID INT PRIMARY KEY,
Nom VARCHAR(50),
Prenom VARCHAR(50),
DateNaissance DATE,
Salaire MONEY
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblProjets en respectant les spécifications ci-dessous
Table "tblProjets" :
ProjetID (Entier - Clé primaire)
NomProjet (Chaîne de caractères)
DateDebut (Date)
DateFin (Date)
ChefProjetID (Entier - Clé étrangère faisant référence à l'ID de l'employe)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblProjets (
ProjetID INT PRIMARY KEY,
NomProjet VARCHAR(50),
DateDebut DATE,
DateFin DATE,
ChefProjetID INT,
FOREIGN KEY (ChefProjetID) REFERENCES tblEmployes(EmpID)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblClients en respectant les spécifications ci-dessous
Table "tblClients" :
ClientID (Entier - Clé primaire)
NomClient (Chaîne de caractères)
Adresse (Chaîne de caractères)
Email (Chaîne de caractères)
Telephone (Chaîne de caractères)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblClients (
ClientID INT PRIMARY KEY,
NomClient VARCHAR(50),
Adresse VARCHAR(50),
Email VARCHAR(50),
Telephone VARCHAR(20)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblProduits en respectant les spécifications ci-dessous
Table "tblProduits" :
ProduitID (Entier - Clé primaire)
NomProduit (Chaîne de caractères)
Description (Texte)
PrixUnitaire (Nombre décimal)
StockDisponible (Entier)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblProduits (
ProduitID INT PRIMARY KEY,
NomProduit VARCHAR(50),
Description TEXT,
PrixUnitaire DECIMAL(10, 2),
StockDisponible INT
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblCommandes en respectant les spécifications ci-dessous
Table "tblCommandes" :
CommandeID (Entier - Clé primaire)
DateCommande (Date)
ClientID (Entier - Clé étrangère faisant référence à l'ID du client)
TotalCommande (Nombre décimal)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblCommandes (
CommandeID INT PRIMARY KEY,
DateCommande DATE,
ClientID INT,
TotalCommande DECIMAL(10, 2),
FOREIGN KEY (ClientID) REFERENCES tblClients(ClientID)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblArticlesCommandes en respectant les spécifications ci-dessous
Table "tblArticlesCommandes" :
ArticleCommandeID (Entier - Clé primaire)
CommandeID (Entier - Clé étrangère faisant référence à l'ID de la commande)
ProduitID (Entier - Clé étrangère faisant référence à l'ID du produit)
Quantite (Entier)
PrixUnitaire (Nombre décimal)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblArticlesCommandes (
ArticleCommandeID INT PRIMARY KEY,
CommandeID INT,
ProduitID INT,
Quantite INT,
PrixUnitaire DECIMAL(10, 2),
FOREIGN KEY (CommandeID) REFERENCES tblCommandes(CommandeID),
FOREIGN KEY (ProduitID) REFERENCES tblProduits(ProduitID)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblCours en respectant les spécifications ci-dessous
Table "tblCours" :
CoursID (Entier - Clé primaire)
NomCours (Chaîne de caractères)
ProfesseurID (Entier - Clé étrangère faisant référence à l'ID de l'employé)
HeureDebut (Heure)
HeureFin (Heure)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblCours (
CoursID INT PRIMARY KEY,
NomCours VARCHAR(50),
ProfesseurID INT,
HeureDebut TIME,
HeureFin TIME,
FOREIGN KEY (ProfesseurID) REFERENCES tblEmployes(EmpID)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblEtudiants en respectant les spécifications ci-dessous
Table "tblEtudiants" :
EtudiantID (Entier - Clé primaire)
NomEtudiant (Chaîne de caractères)
PrenomEtudiant (Chaîne de caractères)
DateNaissance (Date)
Adresse (Chaîne de caractères)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblEtudiants (
EtudiantID INT PRIMARY KEY,
NomEtudiant VARCHAR(50),
PrenomEtudiant VARCHAR(50),
DateNaissance DATE,
Adresse VARCHAR(50)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblResultatsExamens en respectant les spécifications ci-dessous
Table "tblResultatsExamens" :
ResultatID (Entier - Clé primaire)
EtudiantID (Entier - Clé étrangère faisant référence à l'ID de l'étudiant)
CoursID (Entier - Clé étrangère faisant référence à l'ID du cours)
Note (Nombre décimal)
DateExamen (Date)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblResultatsExamens (
ResultatID INT PRIMARY KEY,
EtudiantID INT,
CoursID INT,
Note DECIMAL(5, 2),
DateExamen DATE,
FOREIGN KEY (EtudiantID) REFERENCES tblEtudiants(EtudiantID),
FOREIGN KEY (CoursID) REFERENCES tblCours(CoursID)
);
/***********************************************************************************************
## Écrire une requête qui crée la table tblMessages en respectant les spécifications ci-dessous
Table "tblMessages" :
MessageID (Entier - Clé primaire)
ExpediteurID (Entier - Clé étrangère faisant référence à l'ID de l'employé)
DestinataireID (Entier - Clé étrangère faisant référence à l'ID de l'employé)
ContenuMessage (Texte)
DateEnvoi (Date et Heure)
************************************************************************************************/
-- Votre code ici
CREATE TABLE tblMessages (
MessageID INT PRIMARY KEY,
ExpediteurID INT,
DestinataireID INT,
ContenuMessage TEXT,
DateEnvoi DATETIME,
FOREIGN KEY (ExpediteurID) REFERENCES tblEmployes(EmpID),
FOREIGN KEY (DestinataireID) REFERENCES tblEmployes(EmpID)
);
/******************************************************************************************************/
/*****************************************************************************************
## Partie II : Insertion de données
## =================================
## Ecrire des requêtes SQL qui insèrent des données dans la tables créées précédemment
Code d'insertion de données
===========================
*****************************************************************************************/
-- Insertion dans la table tblEmployes
-- Votre code ici
INSERT INTO tblEmployes (EmpID, Nom, Prenom, DateNaissance, Salaire)
VALUES
(1, 'Dupont', 'Jean', '1980-05-15', 60000.00),
(2, 'Martin', 'Marie', '1985-08-22', 55000.00),
(3, 'Tremblay', 'Pierre', '1990-02-10', 62000.00);
-- Insertion dans la table tblProjets
-- Votre code ici
INSERT INTO tblProjets (ProjetID, NomProjet, DateDebut, DateFin, ChefProjetID)
VALUES
(101, 'ProjetA', '2022-03-01', '2022-06-30', 1),
(102, 'ProjetB', '2022-04-15', '2022-09-30', 2),
(103, 'ProjetC', '2022-02-01', '2022-05-15', 3);
-- Insertion dans la table tblClients
-- Votre code ici
INSERT INTO tblClients (ClientID, NomClient, Adresse, Email, Telephone)
VALUES
(201, 'ClientA', '123 Rue Principale', 'clientA@email.com', '555-1234'),
(202, 'ClientB', '456 Avenue Centrale', 'clientB@email.com', '555-5678'),
(203, 'ClientC', '789 Boulevard Principal', 'clientC@email.com', '555-9876');
-- Insertion dans la table tblProduits
-- Votre code ici
INSERT INTO tblProduits (ProduitID, NomProduit, Description, PrixUnitaire, StockDisponible)
VALUES
(301, 'ProduitA', 'Description du ProduitA', 29.99, 100),
(302, 'ProduitB', 'Description du ProduitB', 49.99, 75),
(303, 'ProduitC', 'Description du ProduitC', 19.99, 150);
-- Insertion dans la table tblCommandes
-- Votre code ici
INSERT INTO tblCommandes (CommandeID, DateCommande, ClientID, TotalCommande)
VALUES
(401, '2022-05-10', 201, 150.00),
(402, '2022-06-02', 202, 220.50),
(403, '2022-04-18', 203, 75.25);
-- Insertion dans la table tblArticlesCommandes
-- Votre code ici
INSERT INTO tblArticlesCommandes (ArticleCommandeID, CommandeID, ProduitID, Quantite, PrixUnitaire)
VALUES
(501, 401, 301, 2, 29.99),
(502, 401, 302, 1, 49.99),
(503, 402, 303, 3, 19.99);
-- Insertion dans la table tblCours
-- Votre code ici
INSERT INTO tblCours (CoursID, NomCours, ProfesseurID, HeureDebut, HeureFin)
VALUES
(601, 'Mathématiques', 1, '08:00', '10:00'),
(602, 'Informatique', 2, '10:30', '12:30'),
(603, 'Sciences', 3, '13:00', '15:00');
-- Insertion dans la table tblEtudiants
-- Votre code ici
INSERT INTO tblEtudiants (EtudiantID, NomEtudiant, PrenomEtudiant, DateNaissance, Adresse)
VALUES
(701, 'Smith', 'John', '1998-03-15', '456 University St.'),
(702, 'Johnson', 'Emily', '1999-06-22', '789 College Ave.'),
(703, 'Davis', 'Michael', '2000-01-10', '123 School Rd.');
-- Insertion dans la table tblResultatsExamens
-- Votre code ici
INSERT INTO tblResultatsExamens (ResultatID, EtudiantID, CoursID, Note, DateExamen)
VALUES
(801, 701, 601, 85.5, '2022-05-20'),
(802, 702, 602, 92.0, '2022-06-10'),
(803, 703, 603, 78.3, '2022-04-30');
-- Insertion dans la table tblMessages
-- Votre code ici
INSERT INTO tblMessages (MessageID, ExpediteurID, DestinataireID, ContenuMessage, DateEnvoi)
VALUES
(901, 1, 2, 'Bonjour Marie,', '2022-05-15 08:30'),
(902, 2, 3, 'Salut Pierre,', '2022-06-01 12:45'),
(903, 3, 1, 'Hello Jean,', '2022-04-20 17:15');
/*****************************************************************************************************************
## Partie III : Modification de la structure des tables
======================================================
## Écrire une requête qui modifie la structure de la table tblEmployes en respectant les spécification ci-dessous :
Ajouter la colonne Departement de type Chaine de caractères et de longueur 50
Ajouter la colonne DateEmbauche de type Date
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblEmployes
ADD Departement NVARCHAR(50),
DateEmbauche DATE;
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblProjets en respectant les spécification ci-dessous :
Ajouter la colonne Budget de type Réel avec 2 décimales
Ajouter la colonne DescriptionProjet de type Texte long
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblProjets
ADD Budget DECIMAL(12, 2),
DescriptionProjet NVARCHAR(255);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblClients en respectant les spécification ci-dessous :
Ajouter la colonne DateCreationClient de type Date
Ajouter la colonne Commentaire de type Texte long
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblClients
ADD DateCreationClient DATE,
Commentaires NVARCHAR(255);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblProduits en respectant les spécification ci-dessous :
Ajouter la colonne DateAjoutProduit de type Date
Ajouter la colonne Fournisseur de type Texte de longueur 55
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblProduits
ADD DateAjoutProduit DATE,
Fournisseur NVARCHAR(55);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblCommandes en respectant les spécification ci-dessous :
Ajouter la colonne DateCommande de type Date
Ajouter la colonne StatutCommande de type Texte de longueur 50
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblCommandes
ADD DateLivraison DATE,
StatutCommande NVARCHAR(50);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblArticlesCommandes en respectant les spécification ci-dessous :
Ajouter la colonne Remise de type Réel avec 2 décimales
Ajouter la colonne MontantTotal de type monétaire
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblArticlesCommandes
ADD Remise DECIMAL(5, 2),
MontantTotal MONEY;
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblCours en respectant les spécification ci-dessous :
Ajouter la colonne DescriptionCours de type Texte long
Ajouter la colonne SalleCours de type Texte de longueur 50
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblCours
ADD DescriptionCours NVARCHAR(255),
SalleCours NVARCHAR(50);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblEtudiants en respectant les spécification ci-dessous :
Ajouter la colonne AnneeEtudes de type entier
Ajouter la colonne MoyenneGenerale de Type Réel à 2 décimales
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblEtudiants
ADD AnneeEtudes TINYINT,
MoyenneGenerale DECIMAL(5, 2);
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblResultatsExamens en respectant les spécification ci-dessous :
Ajouter la colonne Appreciation de type texte de longueur 50
Ajouter la colonne MentionHonneur de type booléen
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblResultatsExamens
ADD Appreciation NVARCHAR(50),
MentionHonneur BIT;
/****************************************************************************************************************
## Écrire une requête qui modifie la structure de la table tblMessages en respectant les spécification ci-dessous :
Ajouter la colonne Lu de type Booléen
Ajouter la colonne Priorite de type entier
*****************************************************************************************************************/
-- Votre code ici
ALTER TABLE tblMessages
ADD Lu BIT,
Priorite TINYINT;
/***************************************************************************************************************
## Partie IV : Suppression de données
****************************************************************************************************************/
-- Code SQL de suppression des données
-- ===================================
-- Ordre inverse des tables
-- Suppression tblProjets
-- Suppression tblArticlesCommandes
-- Suppression tblCommandes
-- Suppression tblResultatsExamens
-- Suppression tblMessages
-- Suppression tblCours
-- Suppression tblClients
-- Suppression tblEtudiants
-- Suppression tblEmployes
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblProjets
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblProjets;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblArticlesCommandes
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblArticlesCommandes;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblCommandes
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblCommandes;
/****************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblResultatsExamens
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblResultatsExamens;
/****************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblMessages
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblMessages;
/****************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblEtudiants
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblEtudiants;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblCours
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblCours;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblProduits
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblProduits;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblEmployes
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblEmployes;
/***************************************************************************************************************
## Écrire une requête qui supprime le contenu de la table tblClients
*****************************************************************************************************************/
-- Votre code ici
DELETE FROM tblClients;
/*************************************************************************
## Partie V : Suppression des tables
=====================================
*************************************************************************/
--## Attention, ne pas confondre avec les requêtes précédentes
/*************************************************************************
## Écrire une requête qui supprime la table tblProjets
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblProjets;
/*************************************************************************
## Écrire une requête qui supprime la table tblArticlesCommandes
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblArticlesCommandes;
/*************************************************************************
## Écrire une requête qui supprime la table tblCommandes
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblCommandes;
/*************************************************************************
## Écrire une requête qui supprime la table tblResultatsExamens
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblResultatsExamens;
/*************************************************************************
## Écrire une requête qui supprime la table tblMessages
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblMessages;
/*************************************************************************
## Écrire une requête qui supprime la table tblEtudiants
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblEtudiants;
/*************************************************************************
## Écrire une requête qui supprime la table tblCours
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblCours;
/*************************************************************************
## Écrire une requête qui supprime la table tblProduits
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblProduits;
/*************************************************************************
## Écrire une requête qui supprime la table tblEmployes
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblEmployes;
/*************************************************************************
## Écrire une requête qui supprime la table tblClients
*************************************************************************/
-- Votre code ici
DROP TABLE IF EXISTS tblClients;
W3schools
Python.org