-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathCamions.sql
171 lines (156 loc) · 10.2 KB
/
Camions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
drop table LESCONTRATS;
drop table LESCHAUFFEURS;
drop table LESSECRETAIRES;
drop table LESCLIENTS;
drop table LESPERSONNES;
drop table LESCAMIONS;
drop table LESDISTANCES;
create table LESDISTANCES
(
VDEP VARCHAR2(20) not null,
VARR VARCHAR2(20) not null,
NBK NUMBER(6)
constraint DIST_CK2
check (nbK > 0),
constraint DIST_PK
primary key (VDEP, VARR),
constraint DIST_CK1
check (vDep <> vArr)
);
create table LESCAMIONS
(
IMMAT VARCHAR2(20) not null
constraint CAM_PK
primary key,
MARQUE VARCHAR2(20),
DATEACHAT DATE
);
create table LESPERSONNES
(
NOP NUMBER(4) not null
constraint PERS_PK
primary key,
NOM VARCHAR2(20),
PRENOM VARCHAR2(20),
SALAIRE NUMBER(6,2)
);
create table LESCLIENTS
(
NOC NUMBER(4) not null
constraint CLIE_PK
primary key,
NOM VARCHAR2(20),
ADRESSE VARCHAR2(20)
);
create table LESSECRETAIRES
(
NOP NUMBER(4) not null
constraint SECR_FK1
references LESPERSONNES,
NOC NUMBER(4) not null
constraint SECR_FK2
references LESCLIENTS,
constraint SECR_PK
primary key (NOP, NOC)
);
create table LESCHAUFFEURS
(
NOP NUMBER(4) not null
constraint CHAU_PK
primary key
constraint CHAU_FK
references LESPERSONNES,
NBK NUMBER(6)
constraint CHAU_CK
check (nbK >= 0)
);
create table LESCONTRATS
(
NOTR NUMBER(4) not null
constraint CONT_PK
primary key,
DATEDEP DATE,
DATEARR DATE,
VDEP VARCHAR2(20),
VARR VARCHAR2(20),
NOC NUMBER(4)
constraint CONT_FK2
references LESCLIENTS,
IMMAT VARCHAR2(20),
NOP NUMBER(4)
constraint CONT_FK1
references LESCHAUFFEURS,
constraint CONT_CK
check (dateDep <= dateArr)
);
grant select on LESDISTANCES to public;
grant select on LESCAMIONS to public;
grant select on LESPERSONNES to public;
grant select on LESCLIENTS to public;
grant select on LESSECRETAIRES to public;
grant select on LESCHAUFFEURS to public;
grant select on LESCONTRATS to public;
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Lyon', 'Marseille', 314);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Lyon', 'Paris', 466);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Lyon', 'Grenoble', 112);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Marseille', 'Grenoble', 306);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Marseille', 'Lyon', 314);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Marseille', 'Paris', 774);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Grenoble', 'Marseille', 306);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Grenoble', 'Paris', 574);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Grenoble', 'Lyon', 112);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Paris', 'Marseille', 774);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Paris', 'Lyon', 466);
INSERT INTO LESDISTANCES (VDEP, VARR, NBK) VALUES ('Paris', 'Grenoble', 574);
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('18 CHX 38', 'renault', TO_DATE('2017-07-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('19 CHX 38', 'renault', TO_DATE('2017-07-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('20 CHX 38', 'renault', TO_DATE('2017-07-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('1675 WZ 38', 'renault', TO_DATE('1994-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('1 A 38', 'renault', TO_DATE('1955-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESCAMIONS (IMMAT, MARQUE, DATEACHAT) VALUES ('181 CHX 38', 'daf', TO_DATE('2017-07-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (15, 'azeae', 'cccccc', 1.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (1, 'Peyrin', 'Jean-Pierre', 68.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (9, 'Bouchenak', 'Sara', 140.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (8, 'Fauvet', 'Marie-Christine', 140.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (10, 'Frehse', 'Goran', 130.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (2, 'Fernandez', 'Jean-Claude', 160.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (3, 'Maltese', 'Corto', 160.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (11, 'L''enchanteur', 'Merlin', 160.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (12, 'Tata', 'Catherine', 160.00);
INSERT INTO LESPERSONNES (NOP, NOM, PRENOM, SALAIRE) VALUES (13, 'Tonton', 'Michael', 160.00);
INSERT INTO LESCLIENTS (NOC, NOM, ADRESSE) VALUES (1, 'Mickey Mouse', 'Echirolles');
INSERT INTO LESCLIENTS (NOC, NOM, ADRESSE) VALUES (2, 'Tintin', 'Bruxelles');
INSERT INTO LESCLIENTS (NOC, NOM, ADRESSE) VALUES (3, 'Rantanplan', 'Washington');
INSERT INTO LESCLIENTS (NOC, NOM, ADRESSE) VALUES (4, 'Mafalda', 'Gap');
--New
INSERT INTO LESCLIENTS (NOC, NOM, ADRESSE) VALUES (5, 'Pepe', 'Grenoble');
INSERT INTO LESSECRETAIRES (NOP, NOC) VALUES (8, 1);
INSERT INTO LESSECRETAIRES (NOP, NOC) VALUES (8, 2);
INSERT INTO LESSECRETAIRES (NOP, NOC) VALUES (13, 3);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (15, 0);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (9, 3326);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (1, 154999);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (10, 1706);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (11, 3262);
INSERT INTO LESCHAUFFEURS (NOP, NBK) VALUES (12, 0);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (100, TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-02-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Marseille', 1, '19 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (101, TO_DATE('2017-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-04-3 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Grenoble', 'Marseille', 2, '1675 WZ 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (102, TO_DATE('2017-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-02-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Paris', 'Marseille', 2, '1675 WZ 38', 10);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (103, TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-01-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 3, '1675 WZ 38', 10);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (104, TO_DATE('2016-03-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-03-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 1, '1675 WZ 38', 10);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (105, TO_DATE('2016-04-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-04-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 2, '1675 WZ 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (106, TO_DATE('2016-09-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-09-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '18 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (107, TO_DATE('2016-09-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '19 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (108, TO_DATE('2016-10-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '20 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (109, TO_DATE('2016-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '18 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (110, TO_DATE('2018-12-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '181 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (111, TO_DATE('2018-12-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 4, '1 A 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (112, TO_DATE('2016-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-06-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Grenoble', 2, '18 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (113, TO_DATE('2016-06-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-06-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 1, '19 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (114, TO_DATE('2016-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-07-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Grenoble', 'Paris', 2, '20 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (115, TO_DATE('2016-11-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-11-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Paris', 3, '18 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (116, TO_DATE('2018-11-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Marseille', 3, '181 CHX 38', 9);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (117, TO_DATE('2018-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-12-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Marseille', 'Paris', 1, '1 A 38', 9);
--New
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (118, TO_DATE('2017-07-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-07-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Marseille', 1, '18 CHX 38', 11);
INSERT INTO LESCONTRATS (NOTR, DATEDEP, DATEARR, VDEP, VARR, NOC, IMMAT, NOP) VALUES (119, TO_DATE('2017-03-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2017-03-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Lyon', 'Marseille', 5, '1675 WZ 38', 10);