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);