182 lines
4.1 KiB
Plaintext
182 lines
4.1 KiB
Plaintext
1. Mettre à jour la colonne Prix_total de la table ligne_commande
|
||
Requête pour mettre à jour la colonne prix_total en fonction des quantite et prix_unitaire :
|
||
|
||
|
||
UPDATE ligne_commande
|
||
SET prix_total = quantite * (
|
||
SELECT prix_unitaire
|
||
FROM produit
|
||
WHERE produit.id = ligne_commande.produit_id
|
||
);
|
||
|
||
|
||
|
||
|
||
2. Implémenter les requêtes du TD
|
||
|
||
|
||
select *
|
||
from ligne_commande lc
|
||
join commande c
|
||
on lc.commande_id = c.id
|
||
where client_id = 16;
|
||
|
||
|
||
|
||
|
||
3. Montant total dépensé par chaque client, classé par ordre décroissant
|
||
|
||
|
||
SELECT
|
||
client.prenom,
|
||
client.nom,
|
||
SUM(ligne_commande.prix_total) AS montant_total
|
||
FROM
|
||
client
|
||
JOIN
|
||
commande ON client.id = commande.client_id
|
||
JOIN
|
||
ligne_commande ON commande.id = ligne_commande.commande_id
|
||
GROUP BY
|
||
client.prenom, client.nom
|
||
ORDER BY
|
||
montant_total DESC;
|
||
|
||
|
||
|
||
|
||
4. Clients ayant dépensé plus de 2000 euros
|
||
|
||
|
||
SELECT
|
||
client.prenom,
|
||
client.nom
|
||
FROM
|
||
client
|
||
JOIN
|
||
commande ON client.id = commande.client_id
|
||
JOIN
|
||
ligne_commande ON commande.id = ligne_commande.commande_id
|
||
GROUP BY
|
||
client.prenom, client.nom
|
||
HAVING
|
||
SUM(ligne_commande.prix_total) > 2000;
|
||
|
||
|
||
|
||
|
||
5. Chiffre d’affaires par mois
|
||
|
||
|
||
SELECT
|
||
TO_CHAR(commande.date_achat, 'YYYY-MM') AS mois_annee,
|
||
SUM(ligne_commande.prix_total) AS chiffre_affaires
|
||
FROM
|
||
commande
|
||
JOIN
|
||
ligne_commande ON commande.id = ligne_commande.commande_id
|
||
GROUP BY
|
||
TO_CHAR(commande.date_achat, 'YYYY-MM')
|
||
ORDER BY
|
||
TO_CHAR(commande.date_achat, 'YYYY-MM');
|
||
|
||
|
||
|
||
|
||
6. Création de la séquence seq_tp
|
||
|
||
|
||
CREATE SEQUENCE seq_tp
|
||
START WITH 121
|
||
INCREMENT BY 1;
|
||
|
||
-- Afficher la valeur actuelle et suivante
|
||
SELECT seq_tp.CURRVAL, seq_tp.NEXTVAL FROM dual;
|
||
|
||
|
||
|
||
|
||
7. Procédure sp_insert_ligne_commande
|
||
Procédure pour insérer une ligne de commande en vérifiant l'existence de la commande et du produit :
|
||
|
||
|
||
CREATE OR REPLACE PROCEDURE sp_insert_ligne_commande(
|
||
p_commande_id INT,
|
||
p_produit_id INT,
|
||
p_quantite INT
|
||
) AS
|
||
v_prix_unitaire NUMBER;
|
||
BEGIN
|
||
-- Vérifier l'existence de la commande et du produit
|
||
IF NOT EXISTS (SELECT 1 FROM commande WHERE id = p_commande_id) THEN
|
||
RAISE_APPLICATION_ERROR(-20001, 'Commande inexistante.');
|
||
END IF;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM produit WHERE id = p_produit_id) THEN
|
||
RAISE_APPLICATION_ERROR(-20002, 'Produit inexistant.');
|
||
END IF;
|
||
|
||
-- Récupérer le prix unitaire
|
||
SELECT prix_unitaire
|
||
INTO v_prix_unitaire
|
||
FROM produit
|
||
WHERE id = p_produit_id;
|
||
|
||
-- Insérer la ligne de commande
|
||
INSERT INTO ligne_commande (id, commande_id, produit_id, quantite, prix_total)
|
||
VALUES (seq_tp.NEXTVAL, p_commande_id, p_produit_id, p_quantite, p_quantite * v_prix_unitaire);
|
||
END;
|
||
/
|
||
|
||
|
||
|
||
|
||
8. Procédure sp_insert_commande
|
||
Procédure pour insérer une commande avec vérification de l'existence du client :
|
||
|
||
|
||
CREATE OR REPLACE PROCEDURE sp_insert_commande(
|
||
p_client_id INT,
|
||
p_reference VARCHAR2
|
||
) AS
|
||
BEGIN
|
||
-- Vérifier l'existence du client
|
||
IF NOT EXISTS (SELECT 1 FROM client WHERE id = p_client_id) THEN
|
||
RAISE_APPLICATION_ERROR(-20003, 'Client inexistant.');
|
||
END IF;
|
||
|
||
-- Insérer la commande
|
||
INSERT INTO commande (id, client_id, date_achat, reference)
|
||
VALUES (seq_tp.NEXTVAL, p_client_id, SYSDATE, p_reference);
|
||
END;
|
||
/
|
||
|
||
|
||
|
||
|
||
9. Procédure sp_frais_livraison
|
||
Procédure pour calculer les frais de livraison selon le montant total d'une commande :
|
||
|
||
|
||
CREATE OR REPLACE PROCEDURE sp_frais_livraison(
|
||
p_commande_id INT,
|
||
p_frais_livraison OUT NUMBER
|
||
) AS
|
||
v_total_commande NUMBER;
|
||
BEGIN
|
||
-- Calculer le montant total de la commande
|
||
SELECT SUM(prix_total)
|
||
INTO v_total_commande
|
||
FROM ligne_commande
|
||
WHERE commande_id = p_commande_id;
|
||
|
||
-- Calculer les frais de livraison
|
||
IF v_total_commande >= 100 THEN
|
||
p_frais_livraison := 0;
|
||
ELSIF v_total_commande >= 50 THEN
|
||
p_frais_livraison := 4;
|
||
ELSE
|
||
p_frais_livraison := 10;
|
||
END IF;
|
||
END;
|
||
/ |