· 6 years ago · Nov 20, 2019, 05:32 PM
1drop database if exists commande;
2create database commande;
3use commande;
4create table CLIENT (
5 NCLI char(10) not null,
6 NOM char(32) not null,
7 ADRESSE char(60) not null,
8 LOCALITE char(30) not null,
9 CAT char(2),
10 COMPTE decimal(9,2)not null,
11 primary key (NCLI))
12ENGINE = INNODB;
13
14create table PRODUIT (
15 NPRO char(15) not null,
16 LIBELLE char(60) not null,
17 PRIX decimal(6) not null,
18 QSTOCK decimal(8) not null,
19 qMin int not null,
20 qMax int not null,
21
22
23 primary key (NPRO))
24ENGINE = INNODB;
25
26create table TAXE (
27 codeTx varchar(6) not null,
28 TPS decimal(5,4) not null,
29 TVQ decimal(5,4) not null,
30 primary key (codeTx))
31ENGINE = INNODB;
32
33
34create table COMMANDE (
35 NCOM char(12) not null,
36 NCLI char(10) not null,
37 DATECOM date not null,
38 totalSansTx decimal(10,2) not null,
39 payee boolean not null default 0,
40 livree boolean not null default 0,
41 codeTx varchar(6) not null,
42 primary key (NCOM),
43 foreign key (NCLI) references CLIENT(NCLI),
44 foreign key (codeTx) references TAXE(codeTx))
45ENGINE = INNODB;
46
47
48
49
50create table DETAIL (
51 NCOM char(12) not null,
52 NPRO char(15) not null,
53 QCOM decimal(8) not null,
54 primary key (NCOM,NPRO),
55 foreign key (NCOM) references COMMANDE(NCOM),
56 foreign key (NPRO) references PRODUIT(NPRO))
57
58 ENGINE = INNODB;
59
60
61insert into CLIENT values
62 ('B112','HANSENNE','23, r. Dumont','Poitiers','C1',1250);
63insert into CLIENT values
64 ('C123','MERCIER','25, r. Lemaitre','Namur','C1',-2300);
65insert into CLIENT values
66 ('B332','MONTI','112, r. Neuve','Geneve','B2',0);
67insert into CLIENT values
68 ('F010','TOUSSAINT','5, r. Godefroid','Poitiers','C1',0);
69insert into CLIENT values
70 ('K111','VANBIST','180, r. Florimont','Lille','B1',720);
71insert into CLIENT values
72 ('S127','VANDERKA','3, av. des Roses','Namur','C1',-4580);
73insert into CLIENT values
74 ('B512','GILLET','14, r. de l''Ete','Toulouse','B1',-8700);
75insert into CLIENT values
76 ('B062','GOFFIN','72, r. de la Gare','Namur','B2',-3200);
77insert into CLIENT values
78 ('C400','FERARD','65, r. du Tertre','Poitiers','B2',350);
79insert into CLIENT values
80 ('C003','AVRON','8, ch. de la Cure','Toulouse','B1',-1700);
81insert into CLIENT values
82 ('K729','NEUMAN','40, r. Bransart','Toulouse',null,0);
83insert into CLIENT values
84 ('F011','PONCELET','17, Cles des Erables','Toulouse','B2',0);
85insert into CLIENT values
86 ('L422','FRANCK','60, r. de Wepion','Namur','C1',0);
87insert into CLIENT values
88 ('S712','GUILLAUME','14a, ch. des Roses','Paris','B1',0);
89insert into CLIENT values
90 ('D063','MERCIER','201, bvd du Nord','Toulouse',null,-2250);
91insert into CLIENT values
92 ('F400','JACOB','78, ch. du Moulin','Bruxelles','C2',0);
93
94
95
96insert into TAXE values ('TX2019',0.05,0.09975);
97insert into PRODUIT values ('CS262','CHEV. SAPIN 200x6x2',75,45,2,10);
98insert into PRODUIT values ('CS264','CHEV. SAPIN 200x6x4',120,2690,2,10);
99insert into PRODUIT values ('CS464','CHEV. SAPIN 400x6x4',220,450,2,10);
100insert into PRODUIT values ('PA45' ,'POINTE ACIER 45 (1K)',105,580,2,10);
101insert into PRODUIT values ('PA60' ,'POINTE ACIER 60 (1K)',95,134,2,10);
102insert into PRODUIT values ('PH222','PL. HETRE 200x20x2',230,782,2,10);
103insert into PRODUIT values ('PS222','PL. SAPIN 200x20x2',185,1220,2,10);
104insert into COMMANDE values ('30178','K111','2015-12-21',0,0,0,'TX2019');
105insert into COMMANDE values ('30179','C400','2015-12-22',0,0,0,'TX2019');
106insert into COMMANDE values ('30182','S127','2015-12-23',0,0,0,'TX2019');
107insert into COMMANDE values ('30184','C400','2015-12-23',0,0,0,'TX2019');
108insert into COMMANDE values ('30185','F011','2016-01-02',0,0,0,'TX2019');
109insert into COMMANDE values ('30186','C400','2016-01-02',0,0,0,'TX2019');
110insert into COMMANDE values ('30188','B512','2016-01-03',0,0,0,'TX2019');
111insert into DETAIL values ('30178','CS464',25);
112insert into DETAIL values ('30179','PA60',20);
113insert into DETAIL values ('30179','CS262',60);
114insert into DETAIL values ('30182','PA60',30);
115insert into DETAIL values ('30184','CS464',120);
116insert into DETAIL values ('30184','PA45',20);
117insert into DETAIL values ('30185','PA60',15);
118insert into DETAIL values ('30185','PS222',600);
119insert into DETAIL values ('30185','CS464',260);
120insert into DETAIL values ('30186','PA45',3);
121insert into DETAIL values ('30188','PA60',70);
122insert into DETAIL values ('30188','PH222',92);
123insert into DETAIL values ('30188','CS464',180);
124insert into DETAIL values ('30188','PA45',22);