2tnpfPwT

1drop database if exists METRO;
2create database if not exists METRO;
3use METRO;
4
5CREATE TABLE operator (
6    id SMALLINT NOT NULL PRIMARY KEY,
7    nazwa VARCHAR(32) NOT NULL
8);
9   
10CREATE TABLE linia_metra (
11    id SMALLINT NOT NULL PRIMARY KEY,
12    nazwa VARCHAR(32) NOT NULL,
13    operatorId SMALLINT NOT NULL
14);
15   
16CREATE TABLE stacja (
17    id SMALLINT NOT NULL PRIMARY KEY,
18    nazwa VARCHAR(32) NOT NULL,
19    liniaMetraId SMALLINT NOT NULL,
20    isActive CHAR(1) NOT NULL DEFAULT '1',
21    passThrough CHAR(1) NOT NULL DEFAULT '0'
22);
23   
24CREATE TABLE stacje_relacje (
25    id SMALLINT NOT NULL PRIMARY KEY,
26    aktStacjaId SMALLINT NOT NULL,
27    docelStacjaId SMALLINT NOT NULL,
28    czasPrzejazdu SMALLINT NOT NULL,
29    dystans INTEGER NOT NULL
30);
31
32alter table stacje_relacje add column kosztPrzejazdu
33smallint not null default 0;
34
35 
36alter table linia_metra add constraint
37foreign key fk_operatorId(operatorId)
38references operator(id)
39on update restrict on delete restrict;
40 
41alter table stacja add constraint
42foreign key fk_liniaMetraId(liniaMetraId)
43references linia_metra(id)
44on update restrict on delete restrict;
45 
46alter table stacje_relacje add constraint
47foreign key fk_aktStacjaId(aktStacjaId)
48references stacja(id)
49on update restrict on delete restrict;
50 
51alter table stacje_relacje add constraint
52foreign key fk_docelStacjaId(docelStacjaId)
53references stacja(id)
54on update restrict on delete restrict;
55 
56insert into operator values (1, upper('operator 1'));
57 
58insert into linia_metra (id, nazwa, operatorId)
59values(1 ,upper('linia a') , 1);
60insert into linia_metra (id, nazwa, operatorId)
61values(2 ,upper('linia b') , 1);
62 
63insert into stacja (id, nazwa, liniaMetraId, isActive, passThrough)
64values
65(1,upper('a1'), 1, '1', '0'),
66(2,upper('a2'), 1, '1', '0'),
67(3,upper('a3'), 1, '1', '0'),
68(4,upper('a4'), 1, '1', '0'),
69(5,upper('b1'), 2, '1', '0'),
70(6,upper('b2'), 2, '1', '0'),
71(7,upper('b3'), 2, '1', '0');
72 
73insert into stacje_relacje (id, aktStacjaId, docelStacjaId, czasPrzejazdu, dystans)
74values
75(1, 1, 2, 7, 7000),
76(2, 2, 1, 7, 7000),
77(3, 2, 3, 7, 7000),
78(4, 2, 5, 0, 0000),
79(5, 3, 2, 7, 7000),
80(6, 3, 4, 7, 7000),
81(7, 4, 3, 7, 7000),
82(8, 5, 2, 0, 7000),
83(9, 5, 6, 7, 7000),
84(10, 6, 5, 7, 7000),
85(11, 6, 7, 7, 7000),
86(12, 7, 6, 7, 7000);
87 
88select s.*,lm.*,o.* from stacja as s
89inner join linia_metra as lm on lm.id = s.liniaMetraId
90inner join operator as o on o.id = lm.operatorId
91order by s.id;
92 
93select o.nazwa as operatorNazwa, lm.nazwa as liniaMetraNazwa,
94s.nazwa as stacjaNazwa, s.isActive, s.passThrough,
95lm.operatorId,s.liniaMetraId,s.id as stacjaId
96from stacja as s
97inner join linia_metra as lm on lm.id = s.liniaMetraId
98inner join operator as o on o.id = lm.operatorId
99order by o.nazwa asc,lm.nazwa asc, s.id asc;
100 
101select sr.* from stacje_relacje as sr order by sr.id asc;
102update stacje_relacje set kosztPrzejazdu = 20
103where czasPrzejazdu <> 0 and dystans <> 0;
104
105	select s.* from stacja as s;
106	update stacja set isActive = '0' where id = 6;
107	update stacja set isActive = case when isActive = '1' then '0' else '1' end where id = 6;