-- vin.sql

--set echo on

-- suppression des tables si des fois elles ont ete creees auparavant

DROP TABLE achat;
DROP TABLE client;
DROP TABLE article;
DROP TABLE vin;
DROP TABLE region;

-- creation des tables

CREATE TABLE region (
idregion INT,
nom VARCHAR(25),
CONSTRAINT pk_region PRIMARY KEY (idregion));

CREATE TABLE vin (
idvin INT,
nom VARCHAR(25),
appellation VARCHAR(25),
type VARCHAR(10),
provenance INT,
CONSTRAINT pk_vin PRIMARY KEY (idvin),
CONSTRAINT fk_vin_provenance FOREIGN KEY (provenance) REFERENCES region (idregion));

CREATE TABLE article (
idarticle INT,
millesime DATE,
contenance INT,
prixht INT,
vin INT,
CONSTRAINT pk_article PRIMARY KEY (idarticle),
CONSTRAINT fk_article_vin FOREIGN KEY (vin) REFERENCES vin (idvin));

CREATE TABLE client (
idclient INT,
nom VARCHAR(25),
age INT,
habite INT,
CONSTRAINT pk_client PRIMARY KEY (idclient),
CONSTRAINT fk_client_habite FOREIGN KEY (habite) REFERENCES region (idregion));

CREATE TABLE achat (
idachat INT,
idarticle INT,
idclient INT,
dateachat DATE,
quantite INT,
ristourne INT,
CONSTRAINT pk_achat PRIMARY KEY (idachat),
CONSTRAINT fk_achat_idarticle FOREIGN KEY (idarticle) REFERENCES article (idarticle),
CONSTRAINT fk_achat_idclient FOREIGN KEY (idclient) REFERENCES client (idclient));


-- insertion de donnees dans ces tables

INSERT INTO region VALUES ( 1, 'Alsace');
INSERT INTO region VALUES ( 2, 'Provence');
INSERT INTO region VALUES ( 3, 'Bourgogne');
INSERT INTO region VALUES ( 4, 'Beaujolais');
INSERT INTO region VALUES ( 5, 'Bordeaux');
INSERT INTO region VALUES ( 6, 'Côtes du Rhone');

INSERT INTO vin VALUES ( 1,'Klevener','AOC','blanc',1);
INSERT INTO vin VALUES ( 2,'Sylvaner','grand cru','blanc',1);
INSERT INTO vin VALUES ( 3,'Riesling','cru classé','blanc',1);
INSERT INTO vin VALUES ( 4,'Kaefferkopf','cru classé','blanc',1);
INSERT INTO vin VALUES ( 5,'Gewurtzraminer','AOC','blanc',1);
INSERT INTO vin VALUES ( 6,'Côtes de Provence','AOC','rosé',2);
INSERT INTO vin VALUES ( 7,'Bandol','grand cru','rosé',2);
INSERT INTO vin VALUES ( 8,'Saint Emilion','cru classé','rouge',3);
INSERT INTO vin VALUES ( 9,'Moulin à vent','AOC','rouge',4);
INSERT INTO vin VALUES (10,'Saint Estephe','grand cru','rouge',5);
INSERT INTO vin VALUES (11,'Pétrus','cru classé','rouge',5);
INSERT INTO vin VALUES (12,'Côtes de Provence','AOC','rouge',2);

INSERT INTO article VALUES ( 1,TO_DATE('1991','YYYY'),75,50,1);
INSERT INTO article VALUES ( 2,TO_DATE('1995','YYYY'),75,40,1);
INSERT INTO article VALUES ( 3,TO_DATE('1996','YYYY'),75,35,1);
INSERT INTO article VALUES ( 4,TO_DATE('1997','YYYY'),75,30,1);
INSERT INTO article VALUES ( 5,TO_DATE('1998','YYYY'),75,20,1);
INSERT INTO article VALUES ( 6,TO_DATE('1994','YYYY'),75,50,2);
INSERT INTO article VALUES ( 7,TO_DATE('1995','YYYY'),75,40,2);
INSERT INTO article VALUES ( 8,TO_DATE('1996','YYYY'),75,35,2);
INSERT INTO article VALUES ( 9,TO_DATE('1997','YYYY'),75,30,2);
INSERT INTO article VALUES (10,TO_DATE('1998','YYYY'),75,20,2);
INSERT INTO article VALUES (11,TO_DATE('1994','YYYY'),75,50,10);
INSERT INTO article VALUES (12,TO_DATE('1995','YYYY'),75,50,10);
INSERT INTO article VALUES (13,TO_DATE('1996','YYYY'),75,45,10);
INSERT INTO article VALUES (14,TO_DATE('1997','YYYY'),75,40,10);
INSERT INTO article VALUES (15,TO_DATE('1998','YYYY'),75,40,10);
INSERT INTO article VALUES (16,TO_DATE('1990','YYYY'),75,280,11);
INSERT INTO article VALUES (17,TO_DATE('1995','YYYY'),75,160,11);
INSERT INTO article VALUES (18,TO_DATE('1996','YYYY'),75,140,11);
INSERT INTO article VALUES (19,TO_DATE('1997','YYYY'),75,135,11);
INSERT INTO article VALUES (20,TO_DATE('1998','YYYY'),75,130,11);
INSERT INTO article VALUES (21,TO_DATE('1992','YYYY'),150,90,1);
INSERT INTO article VALUES (22,TO_DATE('1997','YYYY'),150,50,1);
INSERT INTO article VALUES (23,TO_DATE('1998','YYYY'),150,40,1);
INSERT INTO article VALUES (24,TO_DATE('1995','YYYY'),150,70,2);
INSERT INTO article VALUES (25,TO_DATE('1996','YYYY'),150,65,2);
INSERT INTO article VALUES (26,TO_DATE('1998','YYYY'),150,30,2);
INSERT INTO article VALUES (27,TO_DATE('1996','YYYY'),150,85,10);
INSERT INTO article VALUES (28,TO_DATE('1997','YYYY'),150,70,10);
INSERT INTO article VALUES (29,TO_DATE('1998','YYYY'),150,80,10);
INSERT INTO article VALUES (30,TO_DATE('1994','YYYY'),150,600,11);
INSERT INTO article VALUES (31,TO_DATE('1998','YYYY'),150,730,11);
INSERT INTO article VALUES (32,TO_DATE('1989','YYYY'),150,990,11);

INSERT INTO client VALUES ( 1,'Arnaud',30,1);
INSERT INTO client VALUES ( 2,'Sylvain',35,1);
INSERT INTO client VALUES ( 3,'Pierre',39,1);
INSERT INTO client VALUES ( 4,'Anne',50,2);
INSERT INTO client VALUES ( 5,'Paul',20,3);
INSERT INTO client VALUES ( 6,'Jeanne',32,3);
INSERT INTO client VALUES ( 7,'Nicolas',58,4);
INSERT INTO client VALUES ( 8,'André',55,4);
INSERT INTO client VALUES ( 9,'Richard',38,5);
INSERT INTO client VALUES (10,'Louis',37,6);

INSERT INTO achat VALUES (1,1,4,'16-05-2006',12,10);

