420-4A5 Développement de scripts 420-1D5 Introduction aux bases de données

Département d'informatique
Automne 2024
S04Lab – Requêtes sur plusieurs tables
Formatif

Durée 3 heures et plus

VOTRE NOM et PRÉNOM : Ahmed Alouane_ Groupe : ______

Ce laboratoire doit être fait individuellement
Ce travail devra être complété à la maison

Notions pratiquées :
 Définition de Clé primaire
 Définition de clé étrangère
 Jointures INNER JOIN
 Classement des résultats d'une jointure
 Jointure externe: LEFT JOIN … WHERE…IS NULL

 Avec révision des notions précédentes.

Remise
1) Inscrivez votre nom dans l’entête de ce fichier.
2) Remplacez votre nom et prénom dans le nom du fichier
3) Remettez votre fichier Word dans LEA

Cette icône indique qu’une ou plusieurs captures d’écran partielles sont

demandées. Utilisez l’outil capture de Windows
Cette icône indique qu’une saisie d’informations est demandée
420-1D5 Introduction aux Bases de données

Section1 : Création des clés primaires et liaison des tables avec clé étrangère

Sur le serveur SQL, exécutez le script ‘Script BD 1D5-S04-BdCommerciale.sql’

pour créer la base de données « 1D5-S04-BdCommerciale».

S’assurer que vous pouvez enregistrer les modifications des structures de table
Vérifier et mettre à jour cette option si nécessaire :
Dans Outils choisir Options puis Concepteurs de base de données et décocher
« Empêcher l’enregistrement de modifications qui nécessitent une récréation de table »

2 / 27
420-1D5 Introduction aux Bases de données

a) Créez un diagramme de la base de données

Créez un diagramme de données et ajoutez toutes les tables. Observez le résultat et notez que
pour plusieurs des tables, leurs clés primaires, leurs clés étrangères et l’intégrité des données
entre les tables sont respectées. Mais ce n’est pas le cas pour 2 tables que vous allez arranger
soit la table Categorie et la table Representant.

Faites une capture d’écran du diagramme de base de données tel qu’il est présentement. (Vous
pouvez déplacer les tables pour les rapprocher pour que la capture soit plus facile à prendre.
Vous n’avez pas à faire la capture de toutes les tables mais on doit voir que Categorie et
Representant sont à part).

b) Création des clés primaires

3 / 27
420-1D5 Introduction aux Bases de données

Directement dans le diagramme mettez la colonne «CategorieID » en clé primaire dans la

table « Categorie. »

Faites une capture d’écran de cette modification de la table

Directement dans le diagramme, mettez la colonne « RepresentantID » en clé primaire dans la

table « Representant»

Faites une capture d’écran de cette modification de la table

c) Liaison des tables

Directement dans le diagramme, faites la liaison entre les tables Categorie et Article.

- Déplacez la table Categorie pour la mettre près de la table Article.

- Glissez la clé primaire de la table Categorie vers le champ correspondant dans la table Article.

Faites une capture d’écran de ce diagramme de liaisons des tables

4 / 27
420-1D5 Introduction aux Bases de données

Directement dans le diagramme, faites la liaison entre les tables Representant et Client.

- Déplacez la table Representant pour la mettre près de la table Client.

- Glissez la clé primaire de la table Representant vers le champ correspondant dans la table
Client.

Faites une capture d’écran de ce diagramme de liaisons des tables

5 / 27
420-1D5 Introduction aux Bases de données

Sauvegardez le diagramme modifié. S’il y a des erreurs, lisez attentivement le message d’erreur
pour voir les deux tables qui ne sont pas bien liées.

d) Tester l’intégrité des données

En mode graphique dans Management Studio, sélectionnez la table Client et activez l’option
« Modifier les 200 premières lignes »,
Dans la table « Client », modifiez pour le representantID du client avec le clientID 1 pour que
sont representantID passe de 1 à 5

6 / 27
420-1D5 Introduction aux Bases de données

Faites une capture d’écran de l’enregistrement du client dont l’id est 1 avant la
modification

Faites une capture d’écran de la liste des clients après tentative de modification et l’erreur
obtenue

Faites OK et remettez la valeur à 1.

Section 2 : Requête avec Jointure entre 2 tables en utilisant INNER JOIN

1. Affichez le numéro d’article, description de l’article, la catégorie ID et le nom de la catégorie

pour tous les articles.

Comme le nom de la catégorie est dans la table Categorie et que les autres infos demandées sont
dans la table Article, il vous faudra faire une jointure entre ces deux tables.

 Notions utilisées : Alias de champs AS, jointure INNER JOIN

7 / 27
420-1D5 Introduction aux Bases de données

 Tables utilisées : Article, Categorie avec leurs Alias de tables

 Dans le SELECT, l’alias de la table doit apparaitre devant la colonne CategorieID car
cette colonne est dans les 2 tables et il faut dire au serveur laquelle des colonnes ont veut
utiliser.
Exemple : C.CategorieID

Une partie du résultat attendu :

Copiez la requête SQL

SELECT NumArticle, A.Description AS [DescriptionArticle],

C.CategorieID, C.NomCategorie

FROM Article A

INNER JOIN Categorie C

ON A.CategorieID = C.CategorieID

Faites une capture d’écran de la requête et du résultat

8 / 27
420-1D5 Introduction aux Bases de données

2. Affichez les mêmes infos que précédemment mais seulement pour les articles qui
appartiennent à la CategorieID 3

 Notion additionnelle utilisée : WHERE

Une partie du résultat attendu :

Copiez la requête SQL

SELECT NumArticle, A.Description AS [DescriptionArticle],
C.CategorieID, C.NomCategorie
FROM Article A
INNER JOIN Categorie C
ON A.CategorieID = C.CategorieID
WHERE C.CategorieID = 3

Faites une capture d’écran de la requête et du résultat

3. Affichez le numéro d’article, la description de l’article, le prix unitaire, le stock disponible,

l’id du magasin de l’article, le nom du magasin, le directeur et le numéro de téléphone pour
tous les articles.

9 / 27
420-1D5 Introduction aux Bases de données

Affichez les résultats en ordre de nom de magasin.

 Notions utilisées : Alias de champs AS, alias de table AS, INNER JOIN

 Tables utilisées : Article, Magasin

 Alias des tables : Article A, Magasin M

 Tri : Nom de magasin ascendant

Une partie du résultat attendu :

Copiez la requête SQL

SELECT NumArticle , Description, PrixRegulier , StockDispo,M.MagasinID,
NomMagasin,Directeur,NoTelephone
FROM Article A
INNER JOIN Magasin M
ON A.MagasinID = M.MagasinID
ORDER BY M.NomMagasin

Faites une capture d’écran de la requête et du résultat

10 / 27
420-1D5 Introduction aux Bases de données

4. Affichez le magasin, le nom du magasin, le directeur du magasin et le numéro de téléphone

du magasin, l’adresse du magasin, la ville du magasin et la population de la ville où se situe le
magasin.

 Notions utilisées : Alias de champs AS, alias de table AS, INNER JOIN

 Tables utilisées : Magasin, Ville

 Alias des tables : Magasin M, Ville V

 Tri : Ville ascendant

Une partie du résultat attendu :

Copiez la requête SQL

SELECT Ville, NomMagasin,Directeur,NoTelephone, Adresse
from Ville V
INNER JOIN Magasin M
ON V.VilleID = M.VilleID
ORDER BY V.Ville

Faites une capture d’écran des résultats

11 / 27
420-1D5 Introduction aux Bases de données

5. Affichez le numéro du représentant, le nom du représentant, le prénom du représentant, le

numéro du client, nom du client, le prénom du client, le solde du client, le taux de la
commission du représentant et le calcul de la commission (solde du client * taux de la
commission)

Attention, vous devrez utiliser des alias de champs pour pouvoir distinguer les prenoms et noms
des représentants et ceux des clients.

Observez le résultat attendu pour identifier les alias à donner aux champs.

Également observez que les données sont présentées en ordre descendant du calcul de la
commission : La commission la plus élevée en premier jusqu'à celle qui est la plus basse.

Une partie du résultat attendu :

Copiez la requête SQL

SELECT R.RepresentantID , R.Nom AS [NomRepresentant], R.Prenom AS [PrenomRepresentant],
NumClient,C.Nom AS [NomClient],C.Prenom AS [PrenomClient],Solde, TauxCommission,
(Solde * TauxCommission) AS [CalculCommission]
FROM Representant AS R
INNER JOIN Client AS C
ON R.RepresentantID = C.RepresentantID
ORDER BY (Solde * TauxCommission) DESC

Faites une capture d’écran de votre requête et du résultat

12 / 27
420-1D5 Introduction aux Bases de données

6. Faites comme la requête précédente mais n’affichez que les données pour les commissions
entre 40 $ et 100 $

Attention à l’ordre des clauses. N’oubliez pas que la clause ORDER BY est toujours la dernière
clause de la commande SELECT

Une partie du résultat attendu :

Copiez la requête SQL

SELECT R.RepresentantID , R.Nom AS [NomRepresentant], R.Prenom AS [PrenomRepresentant],
NumClient,C.Nom AS [NomClient],C.Prenom AS [PrenomClient],Solde, TauxCommission,
(Solde * TauxCommission) AS [CalculCommission]
FROM Representant AS R
INNER JOIN Client AS C
ON R.RepresentantID = C.RepresentantID
WHERE (Solde * TauxCommission) BETWEEN 40 AND 100
ORDER BY (Solde * TauxCommission) DESC

Faites une capture d’écran de la requête et du résultat

13 / 27
420-1D5 Introduction aux Bases de données

Section 3 : Requête avec Jointure entre 3 tables en utilisant INNER JOIN

7. Affichez l’id de la commande, le numéro d’article, la description de l’article, le nom de la

catégorie de l’article, la quantité commandée, le prix vendu, le prix régulier et la différence
entre ces deux prix (prix vendu – prix régulier).

Observez le résultat attendu pour déterminer l’alias à utiliser pour le champ calculé que vous
venez de faire.

 Vous ferez une jointure entre les tables LigneCommande et Article ET une jointure
entre les tables Article et Categorie.

Observez quel champ commun aux tables LigneCommande et Article pourra être utilisé pour
faire la jointure entre les tables LigneCommande et Article.

Observez quel champ commun aux tables Article et Categorie pourra être utilisé pour faire la
jointure entre les tables Article et Categorie.

Une partie du résultat attendu :

14 / 27
420-1D5 Introduction aux Bases de données

Copiez la requête SQL

SELECT CommandeID , NumArticle , Description, NomCategorie,
QuantiteCommande, PrixVendu, PrixRegulier,(PrixVendu - A.PrixRegulier) AS
[Difference de Prix]
FROM LigneCommande L
INNER JOIN Article A
ON L.ArticleID = A.ArticleID
INNER JOIN Categorie C
ON A.CategorieID = C.CategorieID

Faites une capture d’écran de la requête et du résultat attendu

8. Faites comme la requête précédente mais n’affichez que les commandes vendues à perte, donc
celles dont la différence de prix est inférieure à zéro.

Vous aurez une clause WHERE avec le calcul de la différence dans le critère de sélection.

Une partie du résultat attendu :

Copiez la requête SQL

15 / 27
420-1D5 Introduction aux Bases de données

SELECT CommandeID , NumArticle , Description, NomCategorie,

QuantiteCommande, PrixVendu, PrixRegulier,(PrixVendu - A.PrixRegulier) AS
[Difference de Prix]
FROM LigneCommande L
INNER JOIN Article A
ON L.ArticleID = A.ArticleID
INNER JOIN Categorie C
ON A.CategorieID = C.CategorieID
WHERE (PrixVendu - A.PrixRegulier) < 0

Faites une capture d’écran de votre requête et du résultat attendu:

Section 3 : Requêtes avec jointures en utilisant LEFT JOIN…WHERE …IS NULL

9. Nous voulons afficher tous les clients qui n’ont pas faits de commande encore

Observez le résultat attendu pour voir les colonnes à afficher.

 Notions utilisées : LEFT JOIN… WHERE….IS NULL

 Tables utilisées : Client, Commande

Une partie du résultat attendu :

16 / 27
420-1D5 Introduction aux Bases de données

Copiez la requête SQL

SELECT C.ClientID, NumClient, Nom, Prenom
FROM dbo.Client C
LEFT JOIN dbo.Commande Co
ON C.ClientID = Co.ClientID
WHERE Co.ClientID IS NULL

Faites une capture d’écran de la requête et du résultat

10. Affichez les articles qui n’ont jamais été commandés.

Observez le résultat attendu pour voir les colonnes à afficher.

 Tri : Par ordre alphabétique sur le numéro d’article de la table « Article »

Une partie du résultat attendu :

17 / 27
420-1D5 Introduction aux Bases de données

Copiez la requête SQL

SELECT NumArticle , Description, StockDispo, PrixRegulier
FROM Article A
LEFT JOIN LigneCommande L
ON A.ArticleID = L.ArticleID
WHERE L.ArticleID IS NULL
ORDER BY NumArticle

Faites une capture d’écran de la requête et du résultat :

Section 4 : Approfondissement des notions précédentes

Les mêmes notions mais avec moins d’explications.

Vous devrez observer le résultat attendu pour pouvoir bâtir votre requête.

La question vous aide à déterminer la table à utiliser et le résultat attendu vous permettra de
savoir quels champs utiliser et dans quel ordre les utiliser.

18 / 27
420-1D5 Introduction aux Bases de données

N’oubliez pas de regarder quel champ est commun entre deux tables pour pouvoir faire les
jointures. Les requêtes nécessitent des jointures entre 2 ou 3 tables.

Pour vous aider, nous allons aussi vous dire combien de lignes vous devriez obtenir au total.

11. Affichez le numéro du client, nom du client, prénom du client, solde du client, limite de crédit
du client, l’id du représentant, le nom du représentant, le prénom du représentant pour tous les
clients.

Une partie du résultat attendu (11 enregistrements au total):

Copiez la requête SQL

SELECT NumClient,C.Nom AS [NomClient], C.Prenom AS [PrenomClient],
Solde,LimiteCredit, R.RepresentantID ,
R.Nom AS [NomRepresentant], R.Prenom AS [PrenomRepresentant]
from Client C
INNER JOIN Representant R
ON C.RepresentantID = R.RepresentantID

Faites une capture d’écran de la requête et du résultat

19 / 27
420-1D5 Introduction aux Bases de données

12. Même requête que précédemment mais affichez uniquement les clients dont la limite de crédit
est supérieure ou égale à 1000$

Une partie du résultat attendu (2 enregistrements):

Copiez la requête SQL

SELECT NumClient,C.Nom AS [NomClient], C.Prenom AS [PrenomClient],
Solde,LimiteCredit, R.RepresentantID ,
R.Nom AS [NomRepresentant], R.Prenom AS [PrenomRepresentant]
from Client C
INNER JOIN Representant R
ON C.RepresentantID = R.RepresentantID
WHERE Solde >= 1000

Faites une capture d’écran de la requête et du résultat :

20 / 27
420-1D5 Introduction aux Bases de données

13. Affichez le numéro de la commande, la date de la commande, L’id du client, le nom du client,
le prénom du client et la ville du client pour les clients de la ville de Québec.

Une partie du résultat attendu (3 enregistrements au total):

Copiez la requête SQL

SELECT NumCommande, DateCommande, C.ClientID,Nom,Prenom,Ville
FROM Commande D
INNER JOIN Client C
ON D.ClientID = C.ClientID
INNER JOIN Ville V
ON C.VilleID = V.VilleID
WHERE Ville = 'Québec'

Faites une capture d’écran de la requête et du résultat :

21 / 27
420-1D5 Introduction aux Bases de données

Nous allons maintenant pratiquer les jointures ET les notions vues précédemment : les
regroupements que nous avons fait la semaine passée, les sous-requêtes...

Vous devrez observer le résultat attendu pour pouvoir bâtir votre requête.

La question vous aide à déterminer la table à utiliser et le résultat attendu vous permettra de
savoir quels champs utiliser et dans quel ordre les utiliser

N’oubliez pas de regarder quel champ est commun entre deux tables pour pouvoir faire les
jointures. Les requêtes nécessitent des jointures entre 2 ou 3 tables.

Pour vous aider, nous allons aussi vous dire combien de lignes vous devriez obtenir au total.

14. Affichez les commandes avec leur nombre d’articles dans la commande

Une partie du résultat attendu (3 enregistrements au total):

Copiez la requête SQL

22 / 27
420-1D5 Introduction aux Bases de données

SELECT Co.CommandeID, COUNT( ArticleID) AS [Nb d''articles dans la commande]

FROM dbo.Commande Co
INNER JOIN dbo.LigneCommande LC
ON Co.CommandeID = LC.CommandeID
GROUP BY Co.CommandeID

Faites une capture d’écran de la requête et du résultat :

15. Affichez les commandes avec leur nombre d’articles dans la commande en ordre du nombre
d’articles

Une partie du résultat attendu (3 enregistrements au total):

Copiez la requête SQL

SELECT Co.CommandeID, COUNT( ArticleID) AS [Nb d''articles dans la commande]
FROM dbo.Commande Co
INNER JOIN dbo.LigneCommande LC
ON Co.CommandeID = LC.CommandeID
GROUP BY Co.CommandeID
ORDER BY COUNT(ArticleID)

Faites une capture d’écran de la requête et du résultat :

23 / 27
420-1D5 Introduction aux Bases de données

16. Affichez toutes les commandes passées

Observez l’ordre du tri

Une partie du résultat attendu (5 enregistrements au total):

Copiez la requête SQL

SELECT Nom + ', ' + Prenom AS [Nom Complet], DateCommande, NumCommande
FROM Commande Co
INNER JOIN Client C
ON Co.ClientID = C.ClientID
ORDER BY Nom, Prenom

Faites une capture d’écran de la requête et du résultat :

24 / 27
420-1D5 Introduction aux Bases de données

17. Affichez les commandes avec le détail des commandes.

Une partie du résultat attendu (9 enregistrements au total):

Copiez la requête SQL

SELECT Co.CommandeID, Co.DateCommande, Co.NumCommande, LigneCommandeID, QuantiteCommande,
PrixVendu, ArticleID
FROM dbo.Commande Co
INNER JOIN dbo.LigneCommande LC
ON Co.CommandeID = LC.CommandeID
ORDER BY Co.CommandeID

Faites une capture d’écran de la requête et du résultat :

25 / 27
420-1D5 Introduction aux Bases de données

18. Affichez les articles des commandes ayant un prix vendu supérieur à la moyenne des prix
vendus des articles commandés

Utilisation d'une sous-requête pour calculer la moyenne des prix vendus des articles
commandés
Observez l’ordre de tri

Le résultat attendu (3 enregistrements au total):

Copiez la requête SQL

SELECT Co.CommandeID, DateCommande, NumCommande, LigneCommandeID,
QuantiteCommande, PrixVendu, ArticleID
FROM dbo.Commande Co
INNER JOIN dbo.LigneCommande LC
ON Co.CommandeID = LC.CommandeID
WHERE PrixVendu > (SELECT AVG(PrixVendu) FROM LigneCommande)
ORDER BY Co.CommandeID

Faites une capture d’écran de la requête et du résultat :

26 / 27
420-1D5 Introduction aux Bases de données

27 / 27