EI Bdd-I1 avr2024-S2-LocVaiss-CORR

Telechargé par Kabrel Lemofack tsague
EI – BDD Page 8 3IL I1FE
I1FE S2 – 23/04/2024 CORRECTION Sujet LocVaisselle
1- Trouver les articles dont le prix est supérieur à 1 euro. La liste obtenue sera affichée dans ordre al-
phabétique de leur désignation
SELECT * FROM ARTICLE
WHERE PrixhtArt > 1.0
ORDER BY DesignArt ;
2- Afficher les informations des clients (code, nom, ville) qui ont effectué des achats après le 1er janvier
2023. Proposer 2 solutions
d. Avec jointure
SELECT DISTINCT C.CodeCli, NomCli, AdrCli, CpCli, VilleCli
FROM CLIENT C
INNER JOIN FACTURE F
ON C.CodeCli=F.CodeCli
WHERE DateFac > '2023-01-01';
e. Avec une requête imbriquée
SELECT CodeCli, NomCli, AdrCli, CpCli, VilleCli
FROM CLIENT
WHERE CodeCli IN (
SELECT DISTINCT CodeCli
FROM FACTURE
WHERE DateFac > '2023-01-01');
3- Rechercher les articles associés à la facture avec le code "F460".
Afficher pour chaque article leur référence, leur désignation, leur prix unitaire hors taxes, la quan-
tité commandée, le montant à payer compte tenue de la quantité commandée.
La liste sera triée dans l’ordre croissant des références des articles
SELECT a.RefArt, a.DesignArt,
fa.QuantiteArt,fa.QuantiteArt*a.PrixhtArt as Montant
FROM FACT_ART fa
JOIN ARTICLE a ON fa.RefArt = a.RefArt
WHERE fa.CodeFact = 'F460'
ORDER BY a.RefArt;
EI – BDD Page 9 3IL I1FE
4- Trouver les clients ayant effectué des règlements et afficher leurs informations avec le montant
total réglé.
Afficher pour chaque client ses informations (code, nom, ville, téléphone) et le montant total de
ses règlements
SELECT c.CodeCli, c.NomCli, c.VilleCli, c.TelCli, SUM(r.MontantReg) AS MontantTotalRegle
FROM CLIENT c
JOIN FACTURE f ON c.CodeCli = f.CodeCli
JOIN REGLEMENT r ON f.CodeFac = r.CodeFac
GROUP BY c.CodeCli, c.NomCli, c.VilleCli, c.TelCli;
5- Ecrire une fonction stockée pour calculer le montant total des règlements effectués par un client
spécifique
CREATE OR REPLACE FUNCTION CalculerMontantTotalReglements(code_cli VARCHAR(6))
RETURNS DECIMAL
AS $$
DECLARE
total DECIMAL;
BEGIN
SELECT SUM(MontantReg) INTO total
FROM REGLEMENT
JOIN FACTURE ON REGLEMENT.CodeFac = FACTURE.CodeFac
WHERE FACTURE.CodeCli = code_cli;
RETURN total;
END;
$$ LANGUAGE plpgsql;
Cette fonction prend le code client en paramètre et calcule le montant total des règlements effectués
par ce client en consultant les enregistrements dans la table REGLEMENT et en les associant aux fac-
tures correspondantes dans la table FACTURE.
Pour appeler cette :
SELECT CalculerMontantTotalReglements('411DUB');
Cela vous retournera le montant total des règlements effectués par le client avec le code '411DUB'.
EI – BDD Page 10 3IL I1FE
6- Modifier la table ARTICLE en ajoutant un champ StockArt de type integer.
Prévoir un trigger qui vérifiera si la quantité en stock de l’article est suffisant avant d’autoriser une
location.
a. Ecrire la requête de modification de la structure de la table ARTICLE
alter table article
add column stockart integer ;
b. Compléter le tableau de description du trigger
sur quelle table ?
Table fact_art
Le déclenchement : sur quel(s)
événement(s) ?
Insertion / update
Déclenchement : à quel
moment par rapport à l’action ?
before
Que doit faire la procédure
appelée par le trigger ?
Comparaison des valeurs de StockArt et quantiteart
En insertion quantiteart doit être < StockArt
Si Ok maj de StockArt = StockArt - quantiteart dans la table
article.
Sinon erreur
A prévoir aussi le cas d’une modification de la quantité d’une location existante.
En update la différence entre nouveau et ancien quantiteart si >0 doit être < StockArt
c. Ecrire du trigger
CREATE OR REPLACE FUNCTION VerifierQuantiteAvantVente()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.QuantiteArt >= (SELECT StockArt
FROM ARTICLE
WHERE RefArt = NEW.RefArt)
THEN
RAISE EXCEPTION 'La quantité demandée est supérieure à la quantité en stock';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER verif_quantite_avant_vente
BEFORE INSERT ON FACT_ART
FOR EACH ROW
EXECUTE FUNCTION VerifierQuantiteAvantVente();
Comment vérifier le bon fonctionnement du trigger ?
Ajouter une ligne dans la table fact_art.
INSERT INTO FACT_ART(RefArt, CodeFact, QuantiteArt) VALUES ('003', 'F460', 5);
Afficher le contenu de la table pour vérifier sir l’insertion a fonctionné.
1 / 3 100%
La catégorie de ce document est-elle correcte?
Merci pour votre participation!

Faire une suggestion

Avez-vous trouvé des erreurs dans linterface ou les textes ? Ou savez-vous comment améliorer linterface utilisateur de StudyLib ? Nhésitez pas à envoyer vos suggestions. Cest très important pour nous !