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 10
##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 10 - Commandes ALTER - UPDATE
##Version : 1.0
##Date : 00/00/2025
##Langage et version : SQL
##Moteur : SQL Server Express
/*****************************************************************************************
## =======================================
## Travaux dirigés 10
## Les commandes :
## ALTER TABLE
## UPDATE
## =======================================
******************************************************************************************/
/*****************************************************************************************
## Cette série de requête vous permettra de vous exercer à la commande UPDATE
## Comme d'habitude, essayez d'en faire le plus possible afin de bien la maitriser
******************************************************************************************/
USE master;
GO
-- Créer une nouvelle base de données TD10
DROP DATABASE If EXISTS TD10;
CREATE DATABASE TD10;
GO
USE TD10;
GO
-- -----------------------------------------------------------------------------------------------------------------------
-- Effectuer une copie des tables suivantes situées dans la base de données Xtreme vers la nouvelle base de données TD10.
-- Le nom des copie sera précédé de tbl. Toutes les requêtes seront effectuées sur les copies dans la base de données TD10
-- -----------------------------------------------------------------------------------------------------------------------
/* ***********************************************************************************************************************
achats --> tblAchats,
clients --> tblClients,
employés --> tblEmployes,
adresse des employés --> tblAdresseEmployes,
commandes --> tblCommandes,
détail des commandes --> tblDetailCommandes
fournisseurs --> tblFournisseurs,
crédits --> tblCredits,
Régions --> tblRegions,
produits --> tblProduits
type de produits --> tblTypeProduits
**************************************************************************************************************************/
-- Création de la table tblAchats --
DROP TABLE IF EXISTS tblAchats;
SELECT *
INTO tblAchats
FROM [xtremefr].[dbo].[Achats];
-- Création de la table tblAdresseEmployes
DROP TABLE IF EXISTS tblAdresseEmployes;
SELECT *
INTO tblAdresseEmployes
FROM [xtremefr].[dbo].[Adresses des employés];
-- Création de la table tblClients
DROP TABLE IF EXISTS tblClients;
SELECT *
INTO tblClients
FROM [xtremefr].[dbo].[Clients];
-- Création de la table tblCommandes
DROP TABLE IF EXISTS tblCommandes;
SELECT *
INTO tblCommandes
FROM [xtremefr].[dbo].[Commandes];
-- Création de la table tblCredits
DROP TABLE IF EXISTS tblCredits;
SELECT *
INTO tblCredits
FROM [xtremefr].[dbo].[Crédits];
-- Création de la table tblDetailCommandes
DROP TABLE IF EXISTS tblDetailCommandes;
SELECT *
INTO tblDetailCommandes
FROM [xtremefr].[dbo].[Détails des commandes];
-- Création de la table tblEmployes
DROP TABLE IF EXISTS tblEmployes;
SELECT *
INTO tblEmployes
FROM [xtremefr].[dbo].[Employés];
-- Création de la table tblFournisseurs
DROP TABLE IF EXISTS tblFournisseurs;
SELECT *
INTO tblFournisseurs
FROM [xtremefr].[dbo].[Fournisseurs];
-- Création de la table tblRegions
DROP TABLE IF EXISTS tblRegions;
SELECT *
INTO tblRegions
FROM [xtremefr].[dbo].[Régions];
-- Création de la table tblProduits
DROP TABLE IF EXISTS tblProduits;
SELECT *
INTO tblProduits
FROM [xtremefr].[dbo].[Produits];
-- Création de la table tblTypeProduit
DROP TABLE IF EXISTS tblTypeProduit;
SELECT *
INTO tblTypeProduit
FROM [xtremefr].[dbo].[Types de produit];
-- *****************************************************************************************************************************************
-- Toutes les requêtes de mise à jour doivent suivre les trois étapes :
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (Une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
-- Série I
-- =======
-- **Requête 1 :**
-- Ecrire une requête qui met à jour le prix du produit dont le type est 7 pour le modifier à 15.99.
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT *
FROM tblproduits
WHERE [ID type de produit] = 7;
-- 2 - Mise à jour
UPDATE tblproduits
SET [Prix conseillé] = 15.99
WHERE [ID type de produit] = 7;
-- 3 - Vérification
SELECT *
FROM tblproduits
WHERE [ID type de produit] = 7;
-- **Requête 2 :**
-- Écrire une requête qui effectue la mise à jour de la quantité en stock du produit dont le seuil de réapprovisionnement est à 50 pour la changer à 50.
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblachats
SET [Unités en stock] = 50
WHERE [Seuil de réapprovisionnement] = 50;
-- **Requête 3 :**
-- Écrire une requête qui effectue la mise à jour de la couleur du produit du fournisseur 1 pour la changer en "Bleu marine"
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblproduits
SET Couleur = 'Noir'
WHERE [ID fournisseur] = 1;
-- **Requête 4 :**
-- Écrire une requête qui effectue la mise à jour du type de produit de "VTT" à "Vélo Tout Terrain".
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tbltypeproduit
SET [Nom du type de produit] = 'Velo tout terrain'
WHERE [Nom du type de produit] = 'VTT';
-- **Requête 5 :**
-- Écrire une requête qui augmente de 10% le prix de tous les produits de type 7 et 3 .
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblproduits
SET [Prix conseillé] = [Prix conseillé] * 1.1
WHERE [ID type de produit] IN (7, 3);
-- **Requête 6 :**
-- Écrire une requête qui effectue la mise à jour du poste des employés qui occupent le poste "Représentant" par "Commercial"
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblemployes
SET [Poste occupé] = 'Commercial'
WHERE [Poste occupé] = 'Représentant';
-- **Requête 7 :**
-- Écrire une requête qui remplace le poste téléphonique des employés qui occupent le poste de "Commercial" par "1111"
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblemployes
SET Poste = '1111'
WHERE [Poste occupé] = 'Commercial';
-- **Requête 8 :**
-- Écrire une requête qui met à jour la site web du transporteur "Pickup" par "#http://www.pickup.com#"
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
UPDATE tblcommandes
SET [Site Web du transporteur] = '#http://www.pickup.com#'
WHERE Transporteur = 'Pickup';
-- **Requête 9 :**
-- Écrire une requête qui augmente le salaire des employés ayant plus de 25 ans d'ancienneté de 200$.
-- Votre code ici
-- Identification des employés ayant 25 ans d'ancienneté
SELECT
YEAR(GETDATE()) - YEAR([Date d'embauche]) Anciennete
FROM tblemployes
WHERE YEAR(GETDATE()) - YEAR([Date d'embauche]) >= 26;
-- Mise à jour
UPDATE tblemployes
SET Salaire = Salaire + 200
WHERE YEAR(GETDATE()) - YEAR([Date d'embauche]) = 25;
-- Vérification
SELECT *
FROM tblemployes
WHERE YEAR(GETDATE()) - YEAR([Date d'embauche]) = 25;
-- **Requête 10 :**
-- Écrire une requête qui met à jour le prix de tous les produits ayant une quantité en stock inférieure à 75 en réduisant son prix de 5%.
-- Votre code ici
-- Identification des produits dont la quantité en stock est inférieure à 75.
SELECT [ID produit]
FROM tblachats
WHERE [Unités en stock] < 75;
-- Mise à jour
UPDATE tblproduits
SET [Prix conseillé] = [Prix conseillé] * 0.95
WHERE [ID produit] IN (
SELECT [ID produit]
FROM tblachats
WHERE [Unités en stock] < 75
);
-- Vérification
SELECT [ID produit], [Unités en stock]
FROM tblachats
WHERE [Unités en stock] < 75;
-- Série II
-- ========
-- **Requête 11 :**
-- Augmentez de 20% le prix de tous les produits dont le type est 'gants' et dont la quantité en stock est supérieure à 100.
-- Votre code ici
-- Identification des enregistrements
SELECT
pro.[ID produit],
pro.[Nom du produit],
typ.[Nom du type de produit],
typ.[ID type de produit],
ach.[Unités en stock]
FROM
tblAchats ach,
tblproduits pro,
tbltypeproduit typ
WHERE
ach.[ID produit] = pro.[ID produit] AND
pro.[ID type de produit] = typ.[ID type de produit] AND
TYP.[ID type de produit] = 5 AND
ach.[Unités en stock] > 100;
-- Mise à jour
UPDATE tblproduits
SET [Prix conseillé] = [Prix conseillé] * 1.20
WHERE [ID produit] IN (
SELECT
pro.[ID produit]-- ,
-- pro.`Nom du produit`,
-- typ.`Nom du type de produit`,
-- typ.`ID type de produit`,
-- ach.`Unités en stock`
FROM tblachats ach, tblproduits pro, tbltypeproduit typ
WHERE
ach.[ID produit] = pro.[ID produit] AND
pro.[ID type de produit] = typ.[ID type de produit] AND
TYP.[ID type de produit] = 5 AND
ach.[Unités en stock] > 100
);
SELECT [Prix conseillé] FROM tblproduits;
-- **Requête 12 :**
-- Écrire une requête qui met à jour le salaire de tous les employés par un pourcentage de 5% si leur année d'embauche est inférieure ou égale à 1996.
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
[Nom],
[Prénom],
[Salaire],
[Date d'embauche]
FROM tblemployes
WHERE YEAR([Date d'embauche]) <= 1996;
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblemployes
SET Salaire = Salaire * 1.05
WHERE YEAR([Date d'embauche]) <= 1996;
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[Nom],
[Prénom],
[Salaire],
[Date d'embauche]
FROM tblemployes
WHERE YEAR([Date d'embauche]) <= 1996;
-- **Requête 13 :**
-- Écrire une requête qui met à jour la quantité en stock de chaque produit en fonction du nombre de commandes passées.
-- Ajoutez à la quantité en stock actuelle de chaque produit la somme des quantités commandées dans la table "OrderDetails" pour ce produit.
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
[ID produit],
[Unités en stock]
FROM tblachats;
SELECT
[ID produit],
SUM(det.[Quantité])
FROM tbldetailcommandes det
GROUP BY [ID produit];
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblachats
SET [Unités en stock] = [Unités en stock] + (
SELECT SUM(det.[Quantité])
FROM tbldetailcommandes det
WHERE det.[ID produit] = tblachats.[ID produit]
);
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[ID produit],
[Unités en stock]
FROM tblachats;
-- **Requête 14 :**
-- Écrire une requête qui insére la valeur 'Quantité limitée' dans la colonne catégorie pour tous les produits ayant une quantité en stock inférieure à 100.
-- Avant d'effectuer cette mise à jour, modifier la structure de la table en rajoutant la colonne "Categorie" de type texte le longueur 20.
-- Votre code ici
-- Modification de la table et ajout de "Categorie"
ALTER TABLE tblproduits
DROP COLUMN IF EXISTS Categorie;
ALTER TABLE tblproduits
ADD Categorie VARCHAR(20);
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT *
FROM tblachats
WHERE [Unités en stock] < 100;
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblproduits
SET Categorie = 'Quantité limitée'
WHERE [ID produit] IN
(
SELECT [ID produit]
FROM tblachats
WHERE [Unités en stock] < 100
AND tblachats.[ID produit] = tblproduits.[ID produit]
);
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[ID produit],
Categorie
FROM tblproduits;
-- **Requête 15 :**
-- Modifiez la table produit (tblProduits) en rajoutant la colonne "Remarques" de type texte et de longueur 50
-- Écrire une requête qui met à jour la colonne "Remarques" du produit ayant le prix le plus élevé avec le message "le plus cher".
-- Votre code ici
-- Ajout de la colonne "Remarques"
ALTER TABLE tblproduits
DROP COLUMN IF EXISTS Remarques;
ALTER TABLE tblproduits
ADD Remarques VARCHAR(50);
EXEC sp_help tblproduits;
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
MAX([Prix conseillé])
FROM tblproduits;
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblproduits
SET Remarques = 'Le plus cher'
WHERE [Prix conseillé] = (SELECT MAX([Prix conseillé]) FROM tblproduits);
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[ID produit],
[Prix conseillé],
Remarques
FROM tblproduits;
-- **Requête 16 :**
-- Modifiez la table tblEmployés en rajoutant la colonne StatutEmploi de type texte de longueur 15.
-- Écrire une requête qui met à jour la colonne StatutEmploi avec "Cadre' pour les employés ayant un salaire supérieur à 60 000.
-- Votre code ici
-- Ajout de la colonne StatutEmploi
ALTER TABLE tblemployes
DROP COLUMN IF EXISTS StatutEmploi;
ALTER TABLE tblemployes
ADD StatutEmploi VARCHAR(15);
EXEC sp_help tblemployes;
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
[Nom],
[Prénom],
[Salaire]
FROM tblemployes
WHERE Salaire >= 60000;
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblemployes
SET StatutEmploi = 'Cadre'
WHERE Salaire >= 60000;
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[Nom],
[Prénom],
[Salaire],
StatutEmploi
FROM tblemployes
WHERE Salaire >= 60000;
-- **Requête 17 :**
-- Écrire une requête qui met à jour la colonne "Couleur" des produits de type 5 en respectant les spécifications suivantes :
-- Lorsque la taille du produit est XSS, XS, S la couleur est "Noir".
-- Lorsque la taille du produit est M, alors la couleur est "Rouge"
-- Lorsque la taille du produit est L, XL, alors la coouleur est "Gris"
-- Votre code ici
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
[ID produit],
[Nom du produit],
[Taille],
[Couleur]
FROM tblproduits
WHERE
Taille IN ('XSS','XS','S','M','L','XL') AND
[ID type de produit] = 5;
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblproduits
SET couleur = (
CASE
WHEN Taille IN ('XSS','XS','S') THEN 'Noir'
WHEN taille IN ('M') THEN 'Rouge'
WHEN taille IN ('L','XL') THEN 'Gris'
END
)
WHERE [ID type de produit] = 5;
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[ID produit],
[Nom du produit],
Taille,
Couleur
FROM tblproduits
WHERE
Taille IN ('XSS','XS','S','M','L','XL') AND
[ID type de produit] = 5;
-- **Requête 18 :**
-- Nous allons affecter une commission aux représentants ayant réalisé un certain montant de ventes.
-- Modifier la table employes (tblEmployes) en rajoutant la colonne "Commission"
-- Mettre à jour la colonne "Commission" avec le montant de la commission réalisée qui représente 2% des vantes réalisées.
-- Je donne volontairement peu de précision pour vous laisser réfléchir à la facon de procéder et ou aller chercher l'information
-- Votre code ici
-- Ajouter la colonne "Commission" à la table tblEmployes
ALTER TABLE tblemployes
DROP COLUMN IF EXISTS Commission;
ALTER TABLE tblemployes
ADD Commission int;
-- 1- Identification des enregistrements à mettre à jour (une instruction SELECT)
SELECT
[ID employé],
SUM([Montant commande])
FROM tblcommandes
GROUP BY [ID employé];
-- 2- Mise à jour à proprement parler (une instruction UPDATE)
UPDATE tblemployes
SET Commission = (
SELECT SUM([Montant commande]) * 0.02
FROM tblcommandes
WHERE tblcommandes.[ID employé] = tblemployes.[ID employé]
);
-- 3- Vérification de la mise à jour.(une instruction SELECT avec la condition appropriée pour vérifier que le UPDATE a bien fonctionné)
SELECT
[ID employé],
[Nom]Nom,
[Prénom],
[Poste occupé],
Commission
FROM tblemployes
GROUP BY [ID employé];
W3schools
Python.org