Révisions des bases de données en vue de l'implémentation de data warehouses sous Oracle

select p.num, p.designation, sum(lf.qte) as nb

from produit p, ligne_fact lf

where lf.produit=p.num

group by p.num, p.designation

order by nb desc;

select p.num, p.designation, sum(lf.qte) as nb
from produit p, ligne_fact lf
where lf.produit=p.num
group by p.num, p.designation
order by nb desc;

select c.num, c.nom, c.prenom, sum(p.prix*lf.qte)

from client c, produit p, facture f, ligne_fact lf

where p.num=lf.produit

and lf.facture=f.num

and f.client=c.num

group by c.num, c.nom, c.prenom

order by sum(p.prix*lf.qte) DESC;

select c.num, c.nom, c.prenom, sum(p.prix*lf.qte)
from client c, produit p, facture f, ligne_fact lf
where p.num=lf.produit
and lf.facture=f.num
and f.client=c.num
group by c.num, c.nom, c.prenom
order by sum(p.prix*lf.qte) DESC;

select client, avg(montant) moy

from (

  select c.num as client, f.num, sum(lf.qte*p.prix) as montant

  from facture f, ligne_fact lf, client c, produit p

  where lf.facture=f.num and lf.produit=p.num and f.client=c.num

  group by c.num, f.num

)

group by client

order by moy;

select client, avg(montant) moy
from (
  select c.num as client, f.num, sum(lf.qte*p.prix) as montant
  from facture f, ligne_fact lf, client c, produit p
  where lf.facture=f.num and lf.produit=p.num and f.client=c.num
  group by c.num, f.num
)
group by client
order by moy;

select distinct designation "PRIX MAX"

from produit

where prix in (select max(prix) from produit);

select distinct designation "PRIX MAX"
from produit
where prix in (select max(prix) from produit);

select distinct designation "PRIX MIN"

from produit

where prix in (select min(prix) from produit);

select distinct designation "PRIX MIN"
from produit
where prix in (select min(prix) from produit);