DtSzznRz

1--
2-- Base de données :  `bdAffair`
3--
4DROP DATABASE IF EXISTS `bdAffair`;
5CREATE DATABASE IF NOT EXISTS `bdAffair`;
6USE `bdAffair`;
7-- --------------------------------------------------------
8
9--
10-- Structure de la table `CLIENT`
11--
12
13CREATE TABLE IF NOT EXISTS `CLIENT` (
14  `NOCLI` int(10) NOT NULL,
15  `NOMCLI` char(20) NOT NULL,
16  `RUECLI` varchar(50) NOT NULL,
17  `CPCLI` int(5) NOT NULL,
18  `VILLECLI` varchar(50) NOT NULL,
19  `CACLI` int(10) NOT NULL,
20  PRIMARY KEY (`NOCLI`)
21);
22--
23-- Structure de la table `AFFAIRE`
24--
25
26CREATE TABLE IF NOT EXISTS `AFFAIRE` (
27  `NOAFF` int(10) NOT NULL,
28  `NOCLI` int(10) NOT NULL,
29  `NOMAT` int(10) NOT NULL,
30  `DATAFF` datetime NOT NULL,
31  `NBMAT` int(10) NOT NULL,
32  `NBLIV` int(10) NOT NULL,
33  PRIMARY KEY (`NOAFF`),
34  FOREIGN KEY (`NOCLI`) REFERENCES CLIENT(`NOCLI`),
35  FOREIGN KEY (`NOMAT`) REFERENCES MATERIEL(`NOMAT`)
36);
37--
38-- Structure de la table `LIVRAISON`
39--
40
41CREATE TABLE IF NOT EXISTS `LIVRAISON` (
42  `NOBL` int(10) NOT NULL,
43  `NOAFF` int(10) NOT NULL,
44  `DATEXP` datetime NOT NULL,
45  `DATLIV` datetime NOT NULL,
46  `QTELIV` int(10) NOT NULL,
47  PRIMARY KEY (`NOBL`),
48  FOREIGN KEY (`NOAFF`) REFERENCES AFFAIRE(`NOAFF`)
49);
50--
51-- Structure de la table `MATERIEL`
52--
53
54CREATE TABLE IF NOT EXISTS `MATERIEL` (
55  `NOMAT` INT(10) NOT NULL,
56  `LIBMAT` VARCHAR(255) NOT NULL,
57  `QTEMAT` int(10) NOT NULL,
58  `PVMAT` NUMERIC(5) NOT NULL,
59  PRIMARY KEY (`NOMAT`)
60);
61
62INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (1,1,8,'2011-09-03',50,3);
63INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (2,5,5,'2011-10-04',9,5);
64INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (3,8,5,'2011-11-05',50,9);
65INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (4,7,6,'2011-11-10',26,8);
66INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (5,1,4,'2011-12-11',40,4);
67INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (6,1,10,'2011-12-14',30,3);
68INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (7,2,7,'2012-01-06',10,1);
69INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (8,2,8,'2012-01-07',50,2);
70INSERT INTO `affaire`(`NOAFF`, `NOCLI`, `NOMAT`, `DATAFF`, `NBMAT`, `NBLIV`) VALUES (9,1,8,'2012-01-13',30,3);
71
72INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (1,'Dupont Jacques','8 Rue Des Cordeliers',13090,'Aix En Provence',56000);
73INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (2,'Dupuis Jean','Rue Du Bois Nouveau',13100,'Aix En Provence',985000);
74INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (3,'Serra Albert','5 Rue Léon Blum',13090,'Aix En Provence',553223);
75INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (4,'Danna Sidonie','Quartier Saint Jean',84130,'Le Pontet',812654);
76INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (5,'Sartous Robert','Rue De La Molle',13100,'Aix En Provence',456258);
77INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (6,'Texedene Jean','Rue De La République',13120,'Puyricard',32000);
78INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (7,'Loiseau Gerard','Rue De La Grande Place',13240,'Meyreuil',12000);
79INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (8,'Josserand Melodie','Avenue De L Europe',13090,'Aix En Provence',75000);
80INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (9,'Roubaud Aurore','Rue Max Weber',13700,'Lambesc',125000);
81INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (10,'Tamburini Franck','Boulevard De La Liberté',13740,'Saint Canna',453000);
82INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (11,'Poitrier Laurent','Chemin De Valcros',13800,'Aix Les Milles',70000);
83INSERT INTO `client`(`NOCLI`, `NOMCLI`, `RUECLI`, `CPCLI`, `VILLECLI`, `CACLI`) VALUES (12,'Parlos Jose','Chemin De La Bigotte',13006,'Marseille',200000);
84
85INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (1,'Perforateur A',25,3654);
86INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (2,'Electro Portatif',15,8975);
87INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (3,'Compresseur B',30,4658);
88INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (4,'Surpresseur',45,6000);
89INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (5,'Electro Pompe',60,2985);
90INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (6,'Groupe Electrogène',28,15685);
91INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (7,'Pompe Type H',14,4657);
92INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (8,'Marteau Piqueur',145,6189);
93INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (9,'Compresseur H',25,8000);
94INSERT INTO `materiel`(`NOMAT`, `LIBMAT`, `QTEMAT`, `PVMAT`) VALUES (10,'Perforateur TTX',42,3589);
95
96INSERT INTO `livraison`(`NOBL`, `NOAFF`, `DATEXP`, `DATLIV`, `QTELIV`) VALUES (1,3,'2011-11-16','2011-11-23',10);
97INSERT INTO `livraison`(`NOBL`, `NOAFF`, `DATEXP`, `DATLIV`, `QTELIV`) VALUES (2,3,'2011-11-23','2011-12-10',3);
98INSERT INTO `livraison`(`NOBL`, `NOAFF`, `DATEXP`, `DATLIV`, `QTELIV`) VALUES (3,4,'2011-11-24','2011-12-01',2);
99INSERT INTO `livraison`(`NOBL`, `NOAFF`, `DATEXP`, `DATLIV`, `QTELIV`) VALUES (4,5,'2011-12-19','2011-12-25',5);
100INSERT INTO `livraison`(`NOBL`, `NOAFF`, `DATEXP`, `DATLIV`, `QTELIV`) VALUES (5,7,'2012-01-13','2012-01-16',10);