-- les caisses auxquelles ``Lise'' a travaille une seule fois.
CREATE VIEW Magasin.reponse_1_5 AS
SELECT DISTINCT NumeroCaisse
FROM            Magasin.Caisses
WHERE           Caissiere = 'Lise'
GROUP BY        NumeroCaisse
HAVING          count(*)=1;
-- les caissieres ayant travaille au moins une fois a chacune des caisses.
-- c'est une division
CREATE VIEW Magasin.reponse_1_6 AS
SELECT DISTINCT Caissiere
FROM   Magasin.Caisses AS R1
WHERE  NOT EXISTS
       (SELECT *
         FROM   Magasin.Caisses AS R2
         WHERE  NOT EXISTS
                (SELECT NumeroCaisse
                 FROM   Magasin.Caisses AS R11
                 WHERE  R11.Caissiere  = R1.Caissiere
                 AND    R11.NumeroCaisse  = R2.NumeroCaisse));
-- les caissieres ayant vendu au moins un article dont le prix unitaire est superieur a 300
CREATE VIEW Magasin.reponse_1_7 AS
SELECT DISTINCT Caissiere
FROM            Magasin.Caisses 
  NATURAL JOIN  Magasin.Factures 
  NATURAL JOIN  Magasin.LignesFacture
WHERE           DateHeure >= DateHeureDebut
  AND           DateHeure <= DateHeureFin
  AND           PrixUnitaire > 300;
-- pour chaque caissiere la difference entre :
--   le total des sommes qu'elles ont encaissees (SommmeFin -SommeDebut) 
--   et la somme des factures qu'elles ont emises.
CREATE VIEW Magasin.reponse_1_8 AS
SELECT DISTINCT Caissiere, SommeDeclaree - SommeRecue AS Difference
FROM
  (SELECT DISTINCT Caissiere, SUM(SommeFin - SommeDebut) AS SommeDeclaree
   FROM            Magasin.Caisses 
   GROUP BY        Caissiere
  ) AS Declaration
  NATURAL JOIN
  (SELECT DISTINCT Caissiere, SUM(PrixUnitaire * Quantite) AS SommeRecue
   FROM            Magasin.Caisses
     NATURAL JOIN  Magasin.Factures
     NATURAL JOIN  Magasin.LignesFacture
   WHERE           DateHeure >= DateHeureDebut
     AND           DateHeure <= DateHeureFin
   GROUP BY Caissiere
  ) AS Recue
ORDER BY        Difference DESC;
