TP2

***********************
vues materialisees
***********************
DROP TABLE Fait_Vente;
DROP TABLE Magasin;
DROP TABLE Produit;
DROP TABLE DateVente;
DROP MATERIALIZED VIEW MV1;


SET TIMING ON  -- pour afficher le tps d'execution


CREATE TABLE Produit(
  id_prod NUMBER,
  marque  VARCHAR2(10),
  modele  VARCHAR2(10),
  type    VARCHAR2(10),
  PRIMARY KEY (id_prod )
);


CREATE TABLE Magasin(
  id_mag        NUMBER,
  raison_sociale VARCHAR2(20),
  ville          VARCHAR2(10),
  PRIMARY KEY (id_mag )
);


CREATE TABLE DateVente(
  id_date    NUMBER,
  date_vente DATE,
  PRIMARY KEY (id_date )
);


CREATE TABLE Fait_vente(
  id_prod NUMBER REFERENCES Produit(id_prod),
  id_mag  NUMBER REFERENCES Magasin(id_mag),
  id_date NUMBER REFERENCES DateVente(id_date),
  prix NUMBER(6)--,
  --PRIMARY KEY (id_prod, id_mag, id_date)
);



--Insertion dans Produit
INSERT INTO PRODUIT VALUES (1,'renault' ,'clio'    ,'petite');
INSERT INTO PRODUIT VALUES (2,'renault' ,'R19'      ,'familiale');
INSERT INTO PRODUIT VALUES (3,'peugeot' ,'206'      ,'petite');
INSERT INTO PRODUIT VALUES (4,'mercedes','classe E' ,'berline');

--Insertion dans Magasin
INSERT INTO MAGASIN VALUES (100,'AAA','Paris');
INSERT INTO MAGASIN VALUES (101,'BBB','Paris');
INSERT INTO MAGASIN VALUES (102,'CCC','Marseille');

--Insertion dans DateVente
INSERT INTO DateVente VALUES (997,'29/03/05');
INSERT INTO DateVente VALUES (998,'06/12/05');
INSERT INTO DateVente VALUES (999,'06/01/06');

--Insertion dans Fait_Vente
INSERT INTO Fait_vente VALUES (1,100,997,12000);
INSERT INTO Fait_vente VALUES (1,100,998,11000);
INSERT INTO Fait_vente VALUES (2,100,997,8500);
INSERT INTO Fait_vente VALUES (3,101,999,10000);
INSERT INTO Fait_vente VALUES (1,101,999,15000);
INSERT INTO Fait_vente VALUES (4,102,999,30000);
INSERT INTO Fait_vente VALUES (4,102,998,25000);
INSERT INTO Fait_vente VALUES (4,102,999,31000);
INSERT INTO Fait_vente VALUES (1,100,998,25000);


***************************************
***************************************

/*nbre de ventes par produit*/
SELECT COUNT(P.id_prod) AS Nb, P.marque, P.modele
FROM  Fait_Vente F, Produit P
WHERE  F.id_prod=P.id_prod
GROUP BY (P.marque, P.modele);

/*nbre de ventes par ville*/
SELECT COUNT(M.id_mag) AS Nb, M.ville
FROM  Fait_Vente F, Magasin M
WHERE  F.id_mag=M.id_mag
GROUP BY (M.ville);


/*nbre de ventes par mois*/
  /*ajout d'une colonne mois*/
  ALTER TABLE DateVente ADD
  (
    mois VARCHAR2(10)
  ) ;
  /*on recupere le mois a partir de la date deja passee*/
  update DateVente
    set mois=to_char(date_vente,'MM');

SELECT COUNT(D.id_date) AS Nb, D.mois
FROM  Fait_Vente F, DateVente D
WHERE  F.id_date=D.id_date
GROUP BY (D.mois);




***************************************
***************************************
/*prix moyen par produit*/
SELECT P.marque, P.modele, AVG(F.prix) as prix_moyen
FROM  Fait_vente F, Produit P
WHERE  F.id_prod=P.id_prod
GROUP BY (P.marque, P.modele);

/*prix moyen par ville*/
SELECT M.ville, AVG(F.prix) as prix_moyen
FROM  Fait_Vente F, Magasin M
WHERE  F.id_mag=M.id_mag
GROUP BY (M.ville);

/*prix moyen par ville*/
SELECT D.mois, AVG(F.prix) as prix_moyen
FROM  Fait_Vente F, DateVente D
WHERE  F.id_date=D.id_date
GROUP BY (D.mois);



***************************************
***************************************
BREAK ON MARQUE ON MODELE ON VILLE

SELECT P.marque, P.modele, M.ville, D.mois, COUNT(P.id_prod) AS nb_vente,
      AVG(F.prix) AS prix_moyen
FROM  Fait_vente F, Produit P, Magasin M, DateVente D
WHERE  F.id_prod=P.id_prod AND
      F.id_mag=M.id_mag  AND
      F.id_date=D.id_date
GROUP BY (P.marque, P.modele,M.ville,D.mois);





***************************************
***************************************

CREATE MATERIALIZED VIEW MV1 AS
 SELECT P.marque, P.modele, M.ville, D.mois, COUNT(P.id_prod) AS nb_vente,
        AVG(F.prix) AS prix_moyen
 FROM  Fait_vente F, Produit P, Magasin M, DateVente D
 WHERE  F.id_prod=P.id_prod AND
        F.id_mag=M.id_mag  AND
        F.id_date=D.id_date
 GROUP BY (P.marque, P.modele,M.ville,D.mois);


SELECT * FROM MV1;