57 lines
1.7 KiB
Plaintext
57 lines
1.7 KiB
Plaintext
|
1|-------------------------------
|
||
|
|
|
||
|
|
update ligne_commande l1
|
||
|
|
set l1.prix_total =
|
||
|
|
(select p.prix_unitaire * l2.quantite
|
||
|
|
from produit p, ligne_commande l2
|
||
|
|
where p.id = l2.produit_id and l2.id = l1.id);
|
||
|
|
|
||
|
|
(verfification)
|
||
|
|
select p.prix_unitaire * l.quantite, l.prix_total
|
||
|
|
from ligne_commande l, produit p
|
||
|
|
where l.produit_id = p.id;
|
||
|
|
|
||
|
|
3|--------------------------------
|
||
|
|
|
||
|
|
select c.id, c.nom, c.prenom, sum(l.prix_total)
|
||
|
|
from client c, commande co, ligne_commande l
|
||
|
|
where c.id = co.client_id and co.id = l.commande_id
|
||
|
|
group by c.id, c.nom, c.prenom;
|
||
|
|
|
||
|
|
4|--------------------------
|
||
|
|
|
||
|
|
select c.id, c.nom, c.prenom, sum(l.prix_total)
|
||
|
|
from client c, commande co, ligne_commande l
|
||
|
|
where c.id = co.client_id and co.id = l.commande_id
|
||
|
|
group by c.id, c.nom, c.prenom
|
||
|
|
having sum(l.prix_total) > 2000;
|
||
|
|
|
||
|
|
5|-----------------------------
|
||
|
|
|
||
|
|
select to_char(co.date_achat, 'yy'), to_char(co.date_achat, 'mm'), sum(l.prix_total)
|
||
|
|
from commande co, ligne_commande l
|
||
|
|
where co.id = l.commande_id
|
||
|
|
group by to_char(co.date_achat, 'yy'), to_char(co.date_achat, 'mm');
|
||
|
|
|
||
|
|
(verification)
|
||
|
|
la somme est egale a:
|
||
|
|
select sum(prix_total) from ligne_commande;
|
||
|
|
|
||
|
|
6|------------------------------
|
||
|
|
|
||
|
|
create sequence seq_tp
|
||
|
|
start with 121;
|
||
|
|
|
||
|
|
select seq_tp.currval from dual;
|
||
|
|
select seq_tp.nextval from dual;
|
||
|
|
|
||
|
|
7|------------------------------
|
||
|
|
|
||
|
|
create or replace procedure sp_insert_ligne_commande(p_commande_id int, p_produit_id int, p_quantite int) is
|
||
|
|
p_prix_total ligne_commande.prix_total%type;
|
||
|
|
p_id ligne_commande.id%type;
|
||
|
|
begin
|
||
|
|
select seq_tp.nextval into p_id from dual;
|
||
|
|
select p.prix_unitaire * l.quantite into p_prix_total from produit p, ligne_commande l where p.id = p_commande_id;
|
||
|
|
insert into ligne_commande values (p_id,p_commande_id,p_produit_id,p_quantite,p_prix_total);
|
||
|
|
end sp_insert_ligne_commande;
|