/* Création de la base de données GestCommande	*/

create table C_CLIENT
(NCLI char(8) not null,
 NOM  char(18) not null,
 ADRESSE char(24) not null,
 LOCALITE char(20) not null,
 CAT char(2),
 COMPTE decimal(8,2) not null,
 primary key (NCLI));

create table C_PRODUIT
(NPRO char(10) not null,
 LIBELLE char(30) not null,
 PRIX decimal(5) not null,
 QSTOCK decimal(6) not null,
 primary key (NPRO));

create table C_COMMANDE
(NCOM char(8) not null,
 NCLI char(8) not null,
 DATECOM date not null,
 primary key (NCOM),
 foreign key (NCLI) references C_CLIENT
         on delete cascade
         on update cascade);

create table C_DETAIL
(NCOM char(8) not null,
 NPRO char(10) not null,
 QCOM decimal(8) not null,
 primary key (NCOM,NPRO),
 foreign key (NCOM) references C_COMMANDE
         on delete cascade
         on update cascade,
 foreign key (NPRO) references C_PRODUIT
         on delete cascade
         on update cascade);

/* Insertion des données dans C_CLIENT  */

INSERT INTO C_CLIENT VALUES ('B112','HANSENNE' ,'23 A. DUMONT' ,'POITIERS' ,'C1',1250.00);
INSERT INTO C_CLIENT VALUES ('C123','MERCIER' ,'25 RUE LEMAITRE' ,'NAMUR' ,'C1',-2300);
INSERT INTO C_CLIENT VALUES
 ('B332','MONTI' ,'112 RUE NEUVE' ,'GENEVE' ,'B2',0);
INSERT INTO C_CLIENT VALUES ('F010','TOUSSAINT' ,'5 RUE GODEFROID' ,'POITIERS' ,'C1',0);
INSERT INTO C_CLIENT VALUES ('K111','VANBIST' ,'180 RUE FLORIMONT' ,'LILLE' ,'B1',720);
INSERT INTO C_CLIENT VALUES ('S127','VANDERKA' ,'3 AV. DES ROSES' ,'NAMUR' ,'C1',-4580);
INSERT INTO C_CLIENT VALUES ('B512','GILLET' ,'14 RUE DE L''ETE' ,'TOULOUSE' ,'B1',-8700);
INSERT INTO C_CLIENT VALUES ('B062','GOFFIN' ,'72 RUE DE LA GARE' ,'NAMUR' ,'B2',-3200);
INSERT INTO C_CLIENT VALUES ('C400','FERARD' ,'65 RUE. DU TERTRE' ,'POITIERS' ,'B2',350);
INSERT INTO C_CLIENT VALUES ('C003','AVRON' ,'8 CHEMIN DE LA CURE' ,'TOULOUSE' ,'B1',-1700);
INSERT INTO C_CLIENT VALUES ('K729','NEUMAN' ,'40 RUE BRANSART' ,'TOULOUSE' ,NULL,0);
INSERT INTO C_CLIENT VALUES ('F011','PONCELET' ,'17 CLOS DES ERABLES','TOULOUSE' ,'B2',0);
INSERT INTO C_CLIENT VALUES ('L422','FRANCK' ,'60 RUE DE WEPION' ,'NAMUR' ,'C1',0);
INSERT INTO C_CLIENT VALUES ('S712','GUILLAUME' ,'14A CHEMIN DES ROSES' ,'PARIS' ,'B1',0);
INSERT INTO C_CLIENT VALUES ('D063','MERCIER' ,'201 BD DU NORD' ,'TOULOUSE' ,NULL,-2250);
INSERT INTO C_CLIENT VALUES ('F400','JACOB' ,'78 CHEMIN DU MOULIN' ,'BRUXELLES','C2',0);

/* Insertion des données dans C_PRODUIT  */

INSERT INTO C_PRODUIT VALUES ('CS262','CHEV. SAPIN 200x6x2',  75,  45);
INSERT INTO C_PRODUIT VALUES ('CS264','CHEV. SAPIN 200x6x4', 120,2690);
INSERT INTO C_PRODUIT VALUES ('CS464','CHEV. SAPIN 400x6x4', 220, 450);
INSERT INTO C_PRODUIT VALUES ('PA45' ,'POINTE ACIER 45',105, 580);
INSERT INTO C_PRODUIT VALUES ('PA60' ,'POINTE ACIER 60', 95, 134);
INSERT INTO C_PRODUIT VALUES ('PH222','PL. HETRE 200x20x2',  230, 782);
INSERT INTO C_PRODUIT VALUES ('PS222','PL. SAPIN 200x20x2',  185,1220);

/* Insertion des données dans C_COMMANDE */

INSERT INTO C_COMMANDE VALUES ('30178','K111','2003-12-21');
INSERT INTO C_COMMANDE VALUES ('30179','C400','2003-12-22');
INSERT INTO C_COMMANDE VALUES ('30182','S127','2003-12-23');
INSERT INTO C_COMMANDE VALUES ('30184','C400','2003-12-23');
INSERT INTO C_COMMANDE VALUES ('30185','F011','2004-01-02');
INSERT INTO C_COMMANDE VALUES ('30186','C400','2004-01-02');
INSERT INTO C_COMMANDE VALUES ('30188','B512','2004-01-03');

/* Insertion des données dans C_DETAIL  */

INSERT INTO C_DETAIL VALUES ('30178','CS464',25);
INSERT INTO C_DETAIL VALUES ('30179','PA60',20);
INSERT INTO C_DETAIL VALUES ('30179','CS262',60);
INSERT INTO C_DETAIL VALUES ('30182','PA60',30);
INSERT INTO C_DETAIL VALUES ('30184','CS464',120);
INSERT INTO C_DETAIL VALUES ('30184','PA45',20);
INSERT INTO C_DETAIL VALUES ('30185','PA60',15);
INSERT INTO C_DETAIL VALUES ('30185','PS222',600);
INSERT INTO C_DETAIL VALUES ('30185','CS464',260);
INSERT INTO C_DETAIL VALUES ('30186','CS464',3);
INSERT INTO C_DETAIL VALUES ('30188','PA60',70);
INSERT INTO C_DETAIL VALUES ('30188','PH222',92);
INSERT INTO C_DETAIL VALUES ('30188','CS464',180);
INSERT INTO C_DETAIL VALUES ('30188','PA45',22);