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
| CREATE TABLE J (ID_J CHAR(4) NOT NULL,
JNAME char(20),
CITY char(10),
constraint pk_J PRIMARY KEY (ID_J));
CREATE TABLE P (ID_P CHAR(4) NOT NULL,
PNAME char(20),
COLOR char(8),
WEIGHT integer,
CITY char(10),
constraint pk_P PRIMARY KEY (ID_P));
CREATE TABLE S (ID_S CHAR(4) NOT NULL,
SNAME char(20),
STATUS CHAR(2),
CITY char(10),
constraint pk_S PRIMARY KEY (ID_S));
CREATE TABLE SPJ (ID_S CHAR(4) NOT NULL,
ID_P CHAR(4) NOT NULL,
ID_J CHAR(4) NOT NULL,
QTY integer,
DATE_DERNIERE_LIVRAISON date,
constraint pk_SPJ PRIMARY KEY (ID_S, ID_P, ID_J),
constraint pk_SPJ_S FOREIGN KEY (ID_S) REFERENCES S(ID_S),
constraint pk_SPJ_P FOREIGN KEY (ID_P) REFERENCES P(ID_P),
constraint pk_SPJ_J FOREIGN KEY (ID_J) REFERENCES J(ID_J));
/* chargement de la table S */
insert into S values('S1','Smith','20','London');
insert into S values('S2','Jones','10','Paris');
insert into S values('S3','Blake','30','Paris');
insert into S values('S4','Clark','20','London');
insert into S values('S5','Adams','30','Athens');
/* chargement de la table P */
insert into P values('P1','Nut','Red',12,'London');
insert into P values('P2','Bolt','Green',17,'Paris');
insert into P values('P3','Screw','Blue',17,'Rome');
insert into P values('P4','Screw','Red',14,'London');
insert into P values('P5','Cam','Blue',12,'Paris');
insert into P values('P6','Cog','Red',19,'London');
/* chargement de la table J */
insert into J values('J1','Sorter','Paris');
insert into J values('J2','Display','Rome');
insert into J values('J3','OCR','Athens');
insert into J values('J4','Console','Athens');
insert into J values('J5','RAID','London');
insert into J values('J6','EDS','Oslo');
insert into J values('J7','Tape','London');
/* chargement de la table SPJ */
insert into SPJ values('S1','P1','J1',200,'05-10-2001');
insert into SPJ values('S1','P1','J4',700,'10-05-2001');
insert into SPJ values('S2','P3','J1',400,'20-05-2001');
insert into SPJ values('S2','P3','J2',200,'30-07-2000');
insert into SPJ values('S2','P3','J3',200,'10-05-2001');
insert into SPJ values('S2','P3','J4',500,'03-10-2001');
insert into SPJ values('S2','P3','J5',600,'20-09-2001');
insert into SPJ values('S2','P3','J6',400,'12-05-2000');
insert into SPJ values('S2','P3','J7',800,'23-08-2001');
insert into SPJ values('S2','P5','J2',100,'23-06-2000');
insert into SPJ values('S3','P3','J1',200,'07-07-2001');
insert into SPJ values('S3','P4','J2',500,'18-05-2001');
insert into SPJ values('S4','P6','J3',300,'10-05-2001');
insert into SPJ values('S4','P6','J7',300,'16-09-2001');
insert into SPJ values('S5','P2','J2',200,'10-11-2001');
insert into SPJ values('S5','P2','J4',100,'17-04-2001');
insert into SPJ values('S5','P5','J5',500,'08-02-2001');
insert into SPJ values('S5','P5','J7',100,'25-06-2001');
insert into SPJ values('S5','P6','J2',200,'09-02-2001');
insert into SPJ values('S5','P1','J4',100,'18-03-2000');
insert into SPJ values('S5','P3','J4',200,'19-05-2001');
insert into SPJ values('S5','P4','J4',800,'10-05-2001');
insert into SPJ values('S5','P5','J4',400,'16-12-2001');
insert into SPJ values('S5','P6','J4',500,'10-10-2001');
commit; |
