Exercice en SQL
1. Création des Tables
| Liste_Produit |
IdLP INT (clé primaire auto incrementée)
Produit CHAR(50)
Prix FLOAT
PrixR FLOAT
LPidTR INT
LPidLF INT
NbStock INT |
|
| Type_Reduc |
IdTR INT (clé primaire auto incrementée)
Reduction CHAR(50)
Montant INT |
|
| Liste_Famille |
IdLF INT (clé primaire auto incrementée)
Famille CHAR(50) |
|
Ecrivez les requêtes SQL correspondantes. Aucun champ ne doit pouvoir être vide.
Afficher la solution
CREATE TABLE Liste_Produit (IdLP INT Primary Key AUTO_INCREMENT, Produit CHAR(50) NOT NULL, Prix FLOAT NOT NULL, PrixR FLOAT NOT NULL, LPidTR INT NOT NULL, LPidLD INT NOT NULL, NbStock INT NOT NULL);
CREATE TABLE Type_Reduc (IdTR INT Primary Key AUTO_INCREMENT, Reduction CHAR(50) NOT NULL, Montant INT NOT NULL);
CREATE TABLE Liste_Famille (IdLF INT Primary Key AUTO_INCREMENT, Famille CHAR(50) NOT NULL);
Nous n'allons pas saisir les relations dans la base car l'intéressant est de tout gérer manuellement.
2. Saisie d'information
Maintenant, il faut entrer du contenu dans les tables. Voici des tableaux contenant les informations qui doivent
y être ajoutées pour l'instant :
2. 1. Table Liste_Produit
| IdLP |
Produit |
Prix |
PrixR |
LPidTR |
LPidLF |
NbStock |
| 1 | CD Delerm | 12 | 0 | 2 | 1 | 200 |
| 2 | CD The Darkness | 18 | 0 | 2 | 1 | 200 |
| 3 | CD Placebo | 14 | 0 | 2 | 1 | 200 |
| 4 | Livre | 8 | 0 | 1 | 1 | 2000 |
| 5 | DVD | 25 | 0 | 2 | 1 | 1000 |
| 6 | Lecteur Mini-Disc | 150 | 0 | 3 | 4 | 100 |
| 7 | Riz | 5 | 0 | 4 | 5 | 3000 |
| 8 | Pates | 5 | 0 | 4 | 5 | 3000 |
| 9 | Lait | 2 | 0 | 5 | 5 | 6000 |
| 10 | Eau | 1 | 0 | 5 | 5 | 6000 |
2. 2. Table Type_Reduc
| IdTR |
Reduction |
Montant |
| 1 | Aucune | 0 |
| 2 | Multimedia | 20 |
| 3 | Materiel | 10 |
| 4 | Alimentaire Base | 10 |
| 5 | Alimentaire Vital | 25 |
2. 3. Table Liste_Famille
| IdLF |
Famille |
| 1 | Multimedia |
| 2 | Litterature |
| 3 | Informatique |
| 4 | Hi-Fi |
| 5 | Alimentaire |
Afficher la solution
// Liste_Produit
INSERT INTO Liste_Produit VALUES ('','CD Delerm',12,0,2,1,200),('','CD The Darkness',18,0,2,1,200),('','CD Placebo',14,0,2,1,200),
('','Livre',8,0,1,1,2000),('','DVD',25,0,2,1,1000),('','Lecteur Mini-Disc',150,0,3,4,100),
('','Riz',5,0,4,5,3000),('','Pates',5,0,4,5,3000),('','Lait',2,0,5,5,6000),('','Eau',1,0,5,5,6000);
// Type_Reduc
INSERT INTO Type_Reduc (Reduction, Montant) VALUES ('Aucune',0), ('Multimedia',20),('Materiel',10),('Alimentaire Base',10),('Alimentaire Vital',25);
// Liste_Famille
INSERT INTO Liste_Famille (Famille) VALUES ('Multimedia'),('Litterature'),('Informatique'),('Hi-Fi'),('Alimentaire');
3. Mise à jour des Prix Réduit
Faites une requête qui met à jour le champ "PrixR" de la table Liste_Produit en faisant le pourcentage depuis la table réduc. Par
exemple, pour le CD, le résultat doit être : Prix = 14, Montant = 20 (pourcentage !), donc PrixR = 14*(100-20)/100 = 11,2
Afficher la solution
UPDATE Liste_Produit, Type_Reduc SET PrixR=Prix*(100-Montant)/100 where LPidTR=IdTR;
4. Ajout d'une colonne dans une table existante
On va maintenant ajouter la colonne "Commentaire" à la table Liste_Produit en initialisant tous les enregistrements à "" (chaine vide)
et d'une taille maximale de 250 caractères.
Afficher la solution
ALTER TABLE Liste_Produit ADD (Commentaire CHAR(250) Default '');
5. Vente d'un Livre
On vient de vendre un livre. Il faut donc réduire le stock d'une unité...
Ecrivez la requête correspondante.
Afficher la solution
UPDATE Liste_Produit Set NbStock=NbStock-1 Where IdLP=2;
6. Affichage des CDs selon le prix
6. 1. Prix compris entre 13 et 15
Pour un cadeau, on vous demande la liste des CDs dont le prix est compris entre 13 et 15.
Vous devez donc récupérer uniquement les CD (grâce au nom du produit) et ceux dont le prix est 13, 14 ou 15.
Ecrivez la requête correspondante
Afficher la solution
SELECT * FROM Liste_Produit WHERE Produit LIKE 'CD%' and Prix BETWEEN 13 and 15 ;
6. 2. Commentaire en fonction du prix
Toujours pour un cadeau, on souhaite afficher tous les CD ayant pour chaque prix les commentaires suivants :
- inférieur à 13 : bon marché
- entre 13 et 15 : parfait
- supérieur à 15 : trop cher
Ce commentaire doit être dans un champs "Comment". Seuls les champs "Produit" et "Comment" doivent être affiché.
Afficher la solution
select Produit, case when prix < 13 then 'bon marché' when prix <= 15 then 'parfait' Else 'trop cher' END as Comment from Liste_Produit WHERE Produit LIKE 'CD%';
Page modifiée le : 20/04/2004
Site modifié le : 14/09/2020
