-- Exercice 1

CREATE VIEW QUANTITE_LIVREE_PAR_PRODUIT AS
SELECT numprod, SUM(qte) AS QUANTITE_LIVREE
FROM DETAILLIVRAISON
GROUP BY numprod;

-- Exercice 2

SELECT MAX(QUANTITE_LIVREE)
FROM QUANTITE_LIVREE_PAR_PRODUIT;

-- Exercice 3

CREATE VIEW FACTURE_PAR_LIVRAISON AS
SELECT D.numfou, D.numli, SUM(qte * prix) AS MONTANT_FACTURE
FROM PROPOSER PR, DETAILLIVRAiSON D
wHERE PR.numfou = D.numfou 
AND D.numprod = PR.numprod
GROUP BY D.numfou, D.numli;

-- Exercice 4

CREATE VIEW FACTURE_PAR_FOURNISSEUR AS
SELECT numfou, SUM(MONTANT_FACTURE) AS TOTAL_FACTURE
FROM FACTURE_PAR_LIVRAISON
GROUP BY numfou;

-- Exercice 5

SELECT MIN(TOTAL_FACTURE)
FROM FACTURE_PAR_FOURNISSEUR;

-- Exercice 6

CREATE VIEW NB_PROD_DIST_LIVRES_PAR_FOU AS
SELECT DISTINCT numfou, 
	COUNT(DISTINCT numprod) AS NB_PRODUITS_DISTINCTS_LIVRES
FROM DETAILLIVRAISON
GROUP BY numfou;

SELECT MAX(NB_PRODUITS_DISTINCTS_LIVRES)
FROM NB_PROD_DIST_LIVRES_PAR_FOU;
