PROGRAMMATION DE BASES DE DONNEES
Laboratoire 1
Objectifs :
1. Prise en mains de MS SQL Server Management Studio.
2. Bref retour sur le SQL
Consignes :
1. Vos requêtes doivent être enregistrées dans un fichier de nom Laboratoire1.sql.
Toutes les requêtes doivent être parfaitement identifiées par leur numéro.
2. Pour la question 3 (1 à 3) vous répondrez directement dans votre fichier
Laboratoire1.sql en mettant vos réponses en commentaires.
3. Vous devez le déposer votre travail dans la boite à la date et à l’heure indiquée.
Question 1 : Création des tables et insertion
1. Connectez-vous à votre SGBD avec votre connexion (Authentification windows)
2. Créez la base de données StockClg
3. Utilisez la base de données StockClg pour créer les tables suivantes :
Table Clients
Colonnes Types, contraintes
idClient SMALLINT. IDENTITY, doit commencer à 1 et s’incrémente
de 1. idClient est une clé primaire de la table clients
nom VARCHAR(20) non nul
Prenom VARCHAR(20) non nul
typeClient CHAR(1). Default 'r' et prend ses valeurs dans (r, o et v)
Table Articles
Colonnes Types, contraintes
idArticle SMALLINT IDENTITY, doit commencer à 1 et s’incrémente
de 1. idArticle est une clé primaire de la table Articles
descriptions VARCHAR(30) non nul
prix MONEY not null. Le prix doit être plus grand que 0
quantiteStock SMALLINTnot nul. quantiteStock doit être plus grand ou
égal à 1
1
PROGRAMMATION DE BASES DE DONNEES
Table Commandes
Colonnes Types, contraintes
Idcommande INT, IDENTITY. Doit commencer à 10 et s’incrément de 1.
Idcommande est la clé primaire de la table commandes.
dateCommande DATE. Non nul
idClient SMALLINT Non nul. Est une clé étrangère faisant référence à
idClient de la table Clients
Table LigneCommande
Colonnes Types, contraintes
Idcommande INT, non nul.
Constitue une clé étrangère faisant référence à
Idcommande de la table commandes
idArticle SMALLINT , non nul
Constitue une clé étrangère faisant référence à idArticle
de la table Articles.
quantite INT non null
4. Pour la table LigneCommande, ajouter la clé primaire. La clé primaire de cette
table est la composition des deux attributs (idCommande, idArticle)
5. Pour la table Clients, ajouter la colonne Adresse de type VARCHAR (50)
6. Pour la table LigneCommande, renommer la colonne quantite par
quantiteCommande.
EXEC sp_rename 'emp.nom', 'nomEmp', 'COLUMN';
emp désigne le nom de la table
nom le nom actuelle de la colonne
nomEmp le nouveau nom.
7. Pour la table LigneCommande, ajouter la contrainte : quantiteCommande doit
être supérieur ou égale à 1
8. Dans chacune des tables, insérer les données suivantes en exécutant le script
suivant : Insertionstockclg.sql
2
PROGRAMMATION DE BASES DE DONNEES
Table Articles :
Table Clients
Table Commandes
3
PROGRAMMATION DE BASES DE DONNEES
Table LigneCommande
Question 2 : écrire les requêtes suivantes :
1. Liste des clients de Montréal. Afficher tous les attributs.
2. Afficher la description des articles commandés par le client ‘Patoche’ dans la
commande numéro 10.
3. Liste des Clients n’ayant fait aucune commande.
4. Afficher le numéro des Articles, la description des articles, la quantité
commandée. Cet affichage doit inclure les articles qui n’ont jamais été
commandés.
5. Combien de commandes sont passée par le Client : ‘Patoche’?
6. Afficher le nom du client ainsi que le nombre total de commande pour chaque
client
7. Quel est le nom du client qui a passé le plus de commande. Afficher également le
nombre de commandes.
8. Pour la table LigneCommande, ajouter la colonne Montant de type Money.
Attention! La table obtenue ne sera pas normalisée
4
PROGRAMMATION DE BASES DE DONNEES
9. Mettre à jour cette colonne comme suit : le montant est égal à la quantité
commandée (quantiteCommande) *le prix, pour chaque article (le prix est dans
la table Articles). Ici, on vous demande d’écrire une requête par article. Une pour
chaque Article. (Attention ! la solution n’est pas optimale, il existe une solution
avec des curseurs, voir chapitre 9)
10. Calculer la somme des montants pour chaque commande.
11. Afficher toutes les commandes avec les noms clients des commandes qui sont
passées après septembre 2022 et avant janvier 2023.
12. Créer une vue Vcommande_Client qui va contenir : le nom du client, le numéro
de commande et la somme pour chaque commande.
13. Utiliser la vue Vcommande_Client pour calculer pour chaque client le grand total
pour ses commandes.
Question 3
1. Dans la table Client, insérez l’enregitrement suivant :
50, ‘Lenom’,’Leprenom’,’l’’Adresse’). Pour cela vous devez mettre
INDENTITY_INSERT à ON
2. Remettez à nouveau NDENTITY_INSERT à OFF puis insérer un autre
enregistrement de votre choix. Quelle est la valeur de la clé primaire pour cet
enregistrement ? Qu’est-ce que nous pouvons conclure ?
3. Dans la table LigneCommande , pensez-vous que nous aurions pu déclarer
Idcommande ou idArticle avec l’option INDENTITY ? Justifiez votre réponse
4. Écrire le code Transact-SQL qui permet d’afficher le nom , le prénom du client
ainsi que le typeClient. Lorsque le typeClient est v alors, afficher VIP, lorsque le
typeClient est r afficher Régulier, lorsque le typeClient est o afficher Occasionnel.
5. Écrire le code Transact-SQL qui permet de faire les opérations suivantes :
(comme un TOUT, doit être dans un bloc BEGIN et END)
a. On crée une nouvelle commande pour le client numéro 6. (ou un client
de votre choix. (Donc on insère dans la table Commandes)
b. On insère dans la table LigneCommande la commande que l’on vient de
créer pour l’article numéro 7 (ou un article de votre choix)
5
PROGRAMMATION DE BASES DE DONNEES
c. Si la quantité dans la table Articles n’est pas suffisante : ce qui veut dire
que la quantité commandée par le client est plus grande ou égale à la
quantité en stock, alors on annule la transaction.
d. Sinon Si la quantité dans la table Articles est suffisante alors
i. On met à jour la table Articles par la nouvelle quantité en
stocke. La nouvelle quantité est égale à la quantité initiale moins
la quantité commandée.
ii. On met à jour le montant de la commande pour cet article dans
la table LigneCommande.
iii. On officialise la transaction
e. Faites les tests suivants :
i. La première fois la quantité en stock est insuffisante
ii. La deuxième fois la quantité en stock est suffisante
iii. Vous pouvez faire les tests : le client n’existe pas. La commande
n’existe pas.
