/* 1- Donnez deux façons de spécifier une requête pour afficher les localité des client qui ont commandé le
produit de numéro ‘CS464’ (avec et sans sous-requête) */

SELECT DISTINCT CLIENT.localite, PRODUIT.nPro
FROM CLIENT,COMMANDE,DETAIL,PRODUIT
WHERE CLIENT.nCli = COMMANDE.nCli AND DETAIL.nPro = PRODUIT.nPro AND COMMANDE.nCom = DETAIL.nCom AND PRODUIT.nPro = "CS464";


SELECT DISTINCT CLIENT.localite, PRODUIT.nPro
FROM CLIENT,COMMANDE,DETAIL,PRODUIT
where PRODUIT.nPro IN
(
    SELECT nPro
    FROM PRODUIT
    WHERE CLIENT.nCli = COMMANDE.nCli AND DETAIL.nPro = PRODUIT.nPro AND COMMANDE.nCom = DETAIL.nCom AND PRODUIT.nPro = "CS464"
);


/*====================================================================================================================================*/

/*2- Donner deux façons pour extraire le nombre de commandes spécifiant un (ou plusieurs) produit(s) en
acier. (avec et sans sous-requête)*/


SELECT COMMANDE.nCom,PRODUIT.libelle,DETAIL.qCom
FROM CLIENT, COMMANDE, DETAIL, PRODUIT
WHERE CLIENT.nCli = COMMANDE.nCli AND COMMANDE.nCom = DETAIL.nCom AND PRODUIT.nPRo = DETAIL.nPro AND PRODUIT.libelle LIKE "%ACIER%";


SELECT COMMANDE.nCom,PRODUIT.libelle,DETAIL.qCom
FROM CLIENT,COMMANDE,DETAIL,PRODUIT
where PRODUIT.libelle IN
(
    SELECT libelle
    FROM PRODUIT
    WHERE CLIENT.nCli = COMMANDE.nCli AND COMMANDE.nCom = DETAIL.nCom AND PRODUIT.nPRo = DETAIL.nPro AND PRODUIT.libelle LIKE "%ACIER%"
);


/*====================================================================================================================================*/

/*3- Afficher le numéro et libellé des produits qui n’ont pas été commandés en 2015.*/

SELECT PRODUIT.nPro,libelle,COMMANDE.dateCom
FROM COMMANDE,DETAIL,PRODUIT
WHERE COMMANDE.nCom = DETAIL.nCom AND DETAIL.nPro = PRODUIT.nPro AND YEAR(dateCom) <> "2015";


/*4- Afficher le numéro et le nom des clients qui n’ont jamais commandé de produit en sapin.*/


select c.nCli, c.nom
from CLIENT c
where c.nCli not in
(
    select c.nCli
    from CLIENT c, COMMANDE cmd, DETAIL d, PRODUIT p
    where c.nCli = cmd.nCli AND cmd.nCom = d.nCom AND d.nPro = p.nPro AND p.libelle like '%SAPIN%'
);




/*5- Afficher le total et la moyenne des comptes des client, ainsi que le nombre de clients, selon :
a. La catégorie
b. La localité
c. La catégorie dans chaque localité
*/

/*A*/  SELECT c.cat as "CATEGORIE",SUM(c.compte) as "SOMME",AVG(c.compte) as "MOYENNE",COUNT(c.nCli) as "NOMBRE DE CLIENTS" FROM CLIENT c GROUP BY cat;
/*B*/ SELECT c.localite as "Localite",SUM(c.compte) as "SOMME",AVG(c.compte) as "MOYENNE",COUNT(c.nCli) as "NOMBRE DE CLIENTS" FROM CLIENT c GROUP BY localite;
/*C*/ SELECT c.cat as "CATEGORIE",c.localite as "Localite",SUM(c.compte) as "SOMME",AVG(c.compte) as "MOYENNE",COUNT(c.nCli) as "NOMBRE DE CLIENTS" FROM CLIENT c GROUP BY localite,cat;