Ce document constitue un corrigé détaillé des Cas Pratiques 201 et 202, spécifiquement conçu pour les étudiants universitaires inscrits en BTS CGO 2A dans le cadre de l'unité "Organisation du Système d’Informations". Son objectif est de renforcer la compréhension et la maîtrise des opérations fondamentales de gestion de bases de données relationnelles à travers l'application du langage SQL.
Il couvre les notions suivantes :
- La manipulation des données (requêtes INSERT, UPDATE, DELETE)
- L'interrogation complexe des données (requêtes SELECT)
- L'utilisation des jointures, des sous-requêtes et des fonctions d'agrégation
- L'application des clauses WHERE, GROUP BY et HAVING pour l'analyse.
Modélisation Merise : Application cas 201 et 202 corrige
Télécharger PDFBTS CGO 2A P10 - Organisation du Système d’Informations CAS 201 & 202 - Corrigé
CAS 201 - Correction
1. Les vendeurs qui ont un pourcentage sur le CA de 1 % voient celui-ci passer à 1,5 % (S01)
UPDATE Vendeur
SET pourcentage_sur_CA = 0.015
WHERE pourcentage_sur_CA = 0.01;
2. Les secteurs Nord et Ouest sont regroupés dans le même secteur : Nord Ouest (S02)
UPDATE Secteur
SET Nom_secteur = "Nord Ouest"
WHERE Nom_secteur = "Nord" OR Nom_secteur = "Ouest";
3. Les vendeurs dont le CA est inférieur à 500 000 € en janvier voient leur pourcentage sur le CA passer à 2,5 % (S03)
UPDATE (Vendeur INNER JOIN Ventes ON Vendeur.Num_vendeur = Ventes.Num_vendeur) INNER JOIN Mois ON Mois.Num_mois = Ventes.Num_mois
SET pourcentage_sur_CA = 0.025
WHERE CA_réalisé < 500000 AND Nom_mois = "janvier";
Equivalent avec jointure SQL1 (une jointure SQL1 n’étant pas à proprement parler une jointure) :
UPDATE (Vendeur, Ventes, Mois
SET pourcentage_sur_CA = 0.025
WHERE Vendeur.Num_vendeur = Ventes.Num_vendeur
AND Mois.Num_mois = Ventes.Num_mois
AND CA_réalisé < 500000 AND Nom_mois = "janvier";
4. Les vendeurs du secteur « Est » qui ont plus de 3 ans d’ancienneté voient leur fixe passer à 1 000 € (S04)
UPDATE Vendeur INNER JOIN Secteur ON Vendeur.Num_secteur = Secteur.Num_secteur
SET fixe = 1000
WHERE Ancienneté > 3 AND Nom_secteur = "Est";
5. Les vendeurs des zones « Est » et « Sud » voient leur fixe augmenter de 4 % (S05)
UPDATE Vendeur INNER JOIN Secteur ON Vendeur.Num_secteur = Secteur.Num_secteur
SET fixe = fixe * 1.04
WHERE Nom_secteur = "Est" OR Nom_secteur = "Sud";
6. Les vendeurs de produits cosmétiques ou d'hygiène voient leur pourcentage augmenter de 1 point (exemple : un vendeur qui touchait 4 % touchera maintenant 5 %) (S06)
UPDATE Catégorie_produit INNER JOIN Vendeur ON Catégorie_produit.Num_Catégorie_produit = Vendeur.Num_Catégorie_produit
SET pourcentage_sur_CA = pourcentage_sur_CA + 0.01
WHERE Nom_Catégorie_produit = "Cosmétique" OR Nom_Catégorie_produit = "Hygiène";
7. La société vend sa branche Cosmétique
7.1. Supprimer les ventes des vendeurs de la branche « Cosmétique » (S07A)
DELETE Ventes.*
FROM (Vendeur INNER JOIN Catégorie_produit ON Vendeur.Num_Catégorie_produit = Catégorie_produit.Num_Catégorie_produit) INNER JOIN Ventes ON Vendeur.Num_vendeur = Ventes.Num_vendeur WHERE Nom_Catégorie_produit = "Cosmétique";
7.2. Tous les vendeurs de Cosmétique sont licenciés (S07B)
DELETE Vendeur.*
FROM Vendeur INNER JOIN Catégorie_produit ON Vendeur.Num_Catégorie_produit = Catégorie_produit.Num_Catégorie_produit
WHERE Nom_Catégorie_produit = "Cosmétique";
7.3. La branche Cosmétique est supprimée
DELETE Catégorie_produit.*
FROM Catégorie_produit
WHERE Nom_Catégorie_produit = "Cosmétique";
8. Armelle Martin démissionne (S08A et S08B)
DELETE Ventes.*
FROM Vendeur INNER JOIN Ventes ON Vendeur.Num_vendeur = Ventes.Num_vendeur
WHERE Nom_vendeur = "Martin" AND Prénom_vendeur = "Armelle";
DELETE Vendeur.*
FROM Vendeur
WHERE Nom_vendeur = "Martin" AND Prénom_vendeur = "Armelle";
9. Supprimer les ventes des vendeurs du rayon Parfumerie dont le CA est inférieur à 400 000 € en janvier (S09)
DELETE Ventes.*
FROM ((Vendeur INNER JOIN Catégorie_produit ON Vendeur.Num_Catégorie_produit = Catégorie_produit.Num_Catégorie_produit) INNER JOIN Ventes ON Vendeur.Num_vendeur = Ventes.Num_vendeur) INNER JOIN Mois ON Mois.Num_mois = Ventes.Num_mois
WHERE Nom_Catégorie_produit = "Parfumerie"
AND CA_réalisé < 400000
AND Nom_mois = "janvier";
10. Gwenaëlle Pelletier est embauchée : son pourcentage est fixé à 3 %, son fixe à 1 100 €, son secteur : 1, sa catégorie de produits : 1 (S10)
INSERT INTO Vendeur (
Nom_vendeur, Prénom_vendeur, pourcentage_sur_CA, Fixe, Num_Secteur, Num_Catégorie_produit, ancienneté
) VALUES (
"Pelletier", "Gwenaëlle", 0.03, 1100, 1, 1, 0
);
11. Frédéric Ricard est embauché : son pourcentage est fixé à 2,3 %, son fixe à 1 000 €, son secteur : 1, sa catégorie de produits : 1 (S11)
INSERT INTO Vendeur (
Nom_vendeur, Prénom_vendeur, pourcentage_sur_CA, Fixe, Num_Secteur, Num_Catégorie_produit, ancienneté
)
VALUES (
"Ricard", "Frédéric", 0.023, 1000, 1, 1, 0
);
CAS 202 - Correction
1. Liste des produits dont le prix est supérieur à 2 €.
SELECT désignation
FROM Produit
WHERE Prix unitaire > 2;
2. Liste des produits de la commande n° 5.
SELECT désignation
FROM Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence WHERE N° commande = 5;
Equivalent avec jointure SQL1 (une jointure SQL1 n’étant pas à proprement parler une jointure) :
SELECT désignation
FROM Produit, Ligne Commande
WHERE Produit.Référence = Ligne Commande.Référence
AND N° commande = 5;
3. Liste des produits commandés le 3/8.
SELECT désignation
FROM (Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence) INNER JOIN Commande ON Ligne Commande.N° commande = Commande.N° commande
WHERE Date commande = #3/8#;
4. Liste des produits situés dans le rayon 2-6.
SELECT Désignation
FROM Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)
WHERE N° dépôt = 6
AND N° rayon = 2;
5. Liste des rayons du dépôt n° 3.
SELECT N° rayon
FROM Rayon
WHERE N° dépôt = 3;
6. Liste des produits situés dans le dépôt Ouest.
SELECT Désignation
FROM (Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)) INNER JOIN Dépôt ON Dépôt.N° dépôt = Rayon.N° dépôt
WHERE Nom dépôt = "Ouest";
7. Afficher le taux de remise accordé pour l’achat du produit n° 8 quand la quantité commandée est comprise dans la tranche B (Tranche B est la valeur prise par la propriété Libelle tranche).
SELECT Taux remise
FROM (Remise INNER JOIN Réduction ON Remise.N° remise = Réduction.N° remise) INNER JOIN Tranche ON Tranche.N° tranche = Réduction.N° tranche
WHERE Référence = 8
AND Libelle tranche = "Tranche B";
8. Liste des clients réguliers avec leur nom et le CA réalisé.
SELECT Nom client, CA réalisé
FROM Client INNER JOIN Régulier ON Client.N° client = Régulier.N° client;
9. Liste des clients réguliers avec leur CA réalisé et des clients occasionnels avec leur date de première commande.
SELECT Nom client, CA réalisé
FROM Client INNER JOIN Régulier ON Client.N° client = Régulier.N° client
UNION
SELECT Nom client, Date première commande
FROM Client INNER JOIN Occasionnel ON Client.N° client = Occasionnel.N° client;
10. Liste des commerciaux en contact avec le client Tavitian.
SELECT Nom commercial
FROM ((Commercial INNER JOIN Contact ON Commercial.N° commercial = Contact.N° commercial) INNER JOIN Régulier ON Contact.N° client = Régulier.N° client) INNER JOIN Client ON Régulier.N° client = Client.N° client
WHERE Nom client = "Tavitian";
11. Afficher le nombre de produits référencés.
SELECT Count(N° produit)
FROM Produit;
12. Afficher le taux moyen de remise accordé pour le produit n° 5.
SELECT Avg(taux remise)
FROM Remise INNER JOIN Réduction ON Remise.N° remise = Réduction.N° remise
WHERE N° produit = 5;
13. Afficher le nombre de contacts en lien avec le client Bolotte.
SELECT count(N° commercial)
FROM (Régulier ON Contact.N° client = Régulier.N° client) INNER JOIN Client ON Régulier.N° client = Client.N° client
WHERE Nom client = "Bolotte";
14. Afficher le montant HT de la commande n° 42.
SELECT Sum (Quantité commandée * Prix unitaire)
FROM Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence
WHERE N° commande = 42;
15. Afficher le CA total réalisé avec les clients réguliers.
SELECT SUM(CA réalisé)
FROM Régulier;
16. Afficher la date de première commande la plus ancienne d’un client occasionnel.
SELECT Min (date première commande)
FROM Occasionnel;
17. Afficher le nombre de produits différents stockés dans le dépôt n° 2.
SELECT Count(N° produit)
FROM Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)
WHERE N° dépôt = 2;
18. Afficher le nombre de commandes passées par le client n° 5.
SELECT Count(N° commande)
FROM Commande WHERE N° Client = 5;
19. Afficher le nombre de produits par commande.
SELECT Count(Référence)
FROM Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence GROUP BY N° commande;
20. Afficher le nombre de commandes moyen passées par les clients réguliers.
SELECT AVG(N° commande)
FROM (Régulier INNER JOIN Client ON Régulier.N° client = Client.N° client) INNER JOIN Commande ON Client.N° client = Commande.N° client;
21. Afficher le nombre de commandes passées par client occasionnel pour les clients ayant passé au moins 2 commandes.
SELECT Count(N° commande)
FROM (Occasionnel INNER JOIN Client ON Occasionnel.N° client = Client.N° client) INNER JOIN Commande ON Client.N° client = Commande.N° client
GROUP BY N° client
HAVING Count(N° commande) > 2;
22. Afficher le nombre de produits par commande pour les commandes ayant au moins 4 produits.
SELECT Count(Référence)
FROM Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence GROUP BY N° commande HAVING Count(Référence) > 4;
23. Afficher les produits de la commande n° (Numéro à saisir).
SELECT Référence
FROM Produit INNER JOIN Ligne Commande ON Produit.Référence = Ligne Commande.Référence
WHERE N° commande = [Saisir le numéro de commande];
24. Afficher les clients dont le nom commence par un A.
SELECT Nom client
FROM Client
WHERE Nom client LIKE "A*";
25. Ajouter le produit 121, Éponge synthétique, 2 €.
INSERT INTO Produit
VALUES (121, 'Éponge synthétique', 2);
26. Le prix unitaire du produit 120 passe à 4 €.
UPDATE Produit
SET Prix unitaire = 4
WHERE Référence = 120;
27. Le CA réalisé par les réguliers est remis à zéro.
UPDATE Régulier
SET CA réalisé = 0;
28. Les produits situés dans le dépôt n° 1 voient leur prix augmenter de 3 %.
UPDATE Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)
SET Prix unitaire = Prix unitaire * 1.03
WHERE N° dépôt = 1;
29. Le dépôt n° 7 est fermé. Le supprimer.
DELETE Dépôt.*
FROM Dépôt
WHERE N° dépôt = 7;
30. Les quantités commandées des commandes de l’année n-2 sont maintenant archivées ; les supprimer de la base de données.
DELETE Ligne commande.*
FROM Ligne commande INNER JOIN Commande ON Ligne commande.N° commande = Commande.N° commande
WHERE Date commande >= #1/1/N-2# AND Date commande <= #31/1/N-2#;
31. Afficher les produits dont le prix unitaire est supérieur au produit n° 28.
SELECT Référence
FROM Produit
WHERE Prix unitaire > (SELECT Référence FROM Produit WHERE Référence = 28);
32. Afficher les produits se trouvant dans le même dépôt que le produit n° 40.
SELECT Référence
FROM Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)
WHERE N° dépôt = (
SELECT N° dépôt
FROM Produit INNER JOIN Rayon ON (Produit.N° rayon = Rayon.N° rayon AND Produit.N° dépôt = Rayon.N° dépôt)
WHERE Référence = 40
);
FAQ
Qu'est-ce qu'une requête SQL UPDATE ?
Une requête SQL UPDATE est utilisée pour modifier des enregistrements existants dans une table de base de données. Elle permet de changer les valeurs d'une ou plusieurs colonnes pour une ligne ou un ensemble de lignes spécifiques qui correspondent à une condition donnée.
À quoi sert l'opérateur JOIN en SQL ?
L'opérateur JOIN est utilisé en SQL pour combiner des lignes de deux tables ou plus, basées sur une colonne commune entre elles. Il permet de récupérer des données liées qui sont stockées dans différentes tables, créant ainsi une vue plus complète des informations.
Comment insérer de nouvelles données dans une table SQL ?
Pour insérer de nouvelles données dans une table SQL, on utilise la requête INSERT INTO. Il est nécessaire de spécifier la table et les valeurs à ajouter, et il est recommandé de lister explicitement les colonnes concernées pour garantir la bonne insertion des données.