CREATE TABLE ville( numville INT PRIMARY KEY, nomville CHAR(20), codepostal CHAR(5)); CREATE TABLE train( numtrain INT PRIMARY KEY, villedepart INT, villearrivee INT, heuredepart INT, heurearrivee INT, jour DATE, FOREIGN KEY (villedepart) REFERENCES ville(numville), FOREIGN KEY (villearrivee) REFERENCES ville(numville) ); CREATE TABLE passager( numpassager INT PRIMARY KEY, nompass CHAR(20), age INT, reftrain INT, FOREIGN KEY (reftrain) REFERENCES train(numtrain) ); INSERT INTO ville VALUES ('0', 'v0', '00000'); INSERT INTO ville VALUES ('1', 'v1', '11111'); INSERT INTO ville VALUES ('2', 'v2', '22222'); INSERT INTO ville VALUES ('3', 'v3', '33333'); INSERT INTO ville VALUES ('4', 'v4', '44444'); INSERT INTO ville VALUES ('5', 'v5', '55555'); INSERT INTO ville VALUES ('6', 'v6', '66666'); INSERT INTO ville VALUES ('7', 'v7', '77777'); INSERT INTO ville VALUES ('8', 'v8', '88888'); INSERT INTO ville VALUES ('9', 'v9', '99999'); INSERT INTO train VALUES ('01', '0', '1', '010000', '020000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('02', '0', '2', '020000', '030000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('03', '1', '3', '030000', '040000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('04', '1', '4', '040000', '050000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('05', '2', '5', '050000', '060000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('06', '2', '6', '060000', '070000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('07', '3', '7', '070000', '080000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('08', '3', '8', '080000', '090000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('09', '4', '9', '090000', '100000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('10', '5', '0', '100000', '110000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('11', '6', '1', '110000', '120000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('12', '7', '2', '120000', '130000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('13', '8', '3', '130000', '140000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('14', '9', '4', '140000', '150000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO train VALUES ('15', '9', '5', '150000', '160000', TO_DATE('01/01/2001', 'dd/mm/yyyy')); INSERT INTO passager VALUES ('00', 'p01', '18', '01'); INSERT INTO passager VALUES ('01', 'p02', '23', '02'); INSERT INTO passager VALUES ('02', 'p03', '42', '03'); INSERT INTO passager VALUES ('03', 'p04', '51', '04'); INSERT INTO passager VALUES ('04', 'p05', '65', '05'); INSERT INTO passager VALUES ('05', 'p06', '26', '06'); INSERT INTO passager VALUES ('06', 'p07', '25', '07'); INSERT INTO passager VALUES ('07', 'p08', '83', '08'); INSERT INTO passager VALUES ('08', 'p09', '23', '09'); INSERT INTO passager VALUES ('09', 'p10', '46', '10'); INSERT INTO passager VALUES ('10', 'p11', '23', '11'); INSERT INTO passager VALUES ('11', 'p12', '87', '12'); INSERT INTO passager VALUES ('12', 'p13', '69', '13'); INSERT INTO passager VALUES ('13', 'p14', '27', '14'); INSERT INTO passager VALUES ('14', 'p01', '18', '15'); INSERT INTO passager VALUES ('15', 'p02', '23', '01'); INSERT INTO passager VALUES ('16', 'p03', '42', '02'); INSERT INTO passager VALUES ('17', 'p04', '51', '03'); INSERT INTO passager VALUES ('18', 'p05', '65', '04'); INSERT INTO passager VALUES ('19', 'p06', '26', '05'); INSERT INTO passager VALUES ('20', 'p07', '25', '06'); INSERT INTO passager VALUES ('21', 'p08', '83', '07'); INSERT INTO passager VALUES ('22', 'p09', '23', '08'); INSERT INTO passager VALUES ('23', 'p10', '46', '09'); INSERT INTO passager VALUES ('24', 'p11', '23', '10'); INSERT INTO passager VALUES ('25', 'p12', '87', '11'); INSERT INTO passager VALUES ('26', 'p13', '69', '12'); INSERT INTO passager VALUES ('27', 'p14', '27', '13'); INSERT INTO passager VALUES ('28', 'p07', '25', '14'); INSERT INTO passager VALUES ('29', 'p12', '87', '15'); DELETE FROM passager; DELETE FROM train; DELETE FROM ville; \i data01.sql ALTER TABLE passager ADD categorie INT; UPDATE passager SET categorie = 3; UPDATE passager SET categorie = 2 WHERE age < 18 OR age > 60; UPDATE passager SET categorie = 1 WHERE nompass LIKE 'R%' OR nompass LIKE 'C%'; CREATE TABLE tarif( reftrain INT, categorie INT, prix INT, PRIMARY KEY (reftrain, categorie), FOREIGN KEY (reftrain) REFERENCES train(numtrain) ); CREATE TABLE distance( ville1 INT NOT NULL, ville2 INT NOT NULL, nbkm INT, PRIMARY KEY (ville1, ville2), FOREIGN KEY (ville1) REFERENCES ville(numville), FOREIGN KEY (ville2) REFERENCES ville(numville) ); INSERT INTO tarif VALUES (5, 2, 42); INSERT INTO tarif VALUES (4, 'plop', 42); INSERT INTO tarif VALUES (-7, 8, 42); DELETE FROM tarif; \i data02.sql CREATE TABLE categorie( numcat INT PRIMARY KEY, intitule VARCHAR(50) ); INSERT INTO categorie VALUES (1, 'Privilege du nom'); INSERT INTO categorie VALUES (2, 'Privilege Jeunes et Seniors'); INSERT INTO categorie VALUES (3, 'Aucun privilege'); ALTER TABLE passager ADD CONSTRAINT fk_passCat FOREIGN KEY (categorie) REFERENCES categorie(numcat); ALTER TABLE tarif ADD CONSTRAINT fk_tarifCat FOREIGN KEY (categorie) REFERENCES categorie(numcat);