· 9 years ago · Oct 19, 2016, 02:20 PM
1DROP TABLE if exists Lab4_Lee_Trains;
2CREATE Table Lab4_Lee_Trains(
3 TrainID varchar(200) not null,
4 Departure timestamp,
5 DeptStation varchar(200) not null,
6 Arrival timestamp not null,
7 ArrStation varchar(200) not null,
8 Duration time not null,
9 Train varchar(200) not null,
10 Comments varchar(200) not null,
11 Primary Key(TrainID)
12);
13Insert into Lab4_Lee_Trains
14 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
15 values
16 ('T13', '12/10/16 08:50', 'Frankfurt Hbf', '12/10/16 12:37', 'Munich Hbf', '03:37:00', 'ICE 275', 'Connection in Mannhein Hbf with ICE 513');
17Insert into Lab4_Lee_Trains
18 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
19 values
20 ('T14', '12/10/16 08:54', 'Frankfurt Hbf', '12/10/16 12:04', 'Munich Hbf', '03:10:00', 'ICE 1127', 'No Comments');
21Insert into Lab4_Lee_Trains
22 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
23 values
24 ('T15', '12/10/16 10:04', 'Frankfurt Hbf', '12/10/16 13:38', 'Munich Hbf', '03:34:00', 'ICE 1521', 'No Comments');
25Insert into Lab4_Lee_Trains
26 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
27 values
28 ('T155', '12/10/16 11:54', 'Frankfurt Hbf', '12/10/16 16:06', 'Munich Hbf', '04:12:00', 'ICE 623', 'Connection in Wurzburg Hbf with ICE 787');
29Insert into Lab4_Lee_Trains
30 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
31 values
32 ('T157', '12/10/16 18:50', 'Frankfurt Hbf', '12/10/16 22:36', 'Munich Hbf', '03:36:00', 'ICE 375', 'Connection in Mannhein Hbf with ICE 513');
33Insert into Lab4_Lee_Trains
34 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
35 values
36 ('T156', '12/10/16 20:54', 'Frankfurt Hbf', '13/10/16 00:44', 'Munich Hbf', '03:50:00', 'ICE 821', 'Connection in Wurzburg Hbf with ICE 635 then Nuremburg Hbf with ICE 1517');
37Insert into Lab4_Lee_Trains
38 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
39 values
40 ('X03', '12/10/16 16:48', 'Cologne Hbf', '12/10/16 21:06', 'Berlin Hbf', '04:18:00', 'ICE 653', 'No comments');
41Insert into Lab4_Lee_Trains
42 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
43 values
44 ('X73', '12/10/16 19:48', 'Cologne Hbf', '12/10/16 00:19', 'Berlin Hbf', '04:31:00', 'ICE 955', 'No comments');
45Insert into Lab4_Lee_Trains
46 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
47 values
48 ('L33', '13/10/16 08:15', 'Berlin Hbf', '13/10/16 11:26', 'Cologne Hbf', '04:18:00', 'ICE 876', 'No comments');
49Insert into Lab4_Lee_Trains
50 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
51 values
52 ('L34', '14/10/16 11:54', 'Frankfurt Hbf', '14/10/16 16:06', 'Munich Hbf', '04:12:00', 'ICE 623', 'Connection in Wurzburg Hbf with ICE 787');
53Insert into Lab4_Lee_Trains
54 (TrainID, Departure, DeptStation, Arrival, ArrStation, Duration, Train, Comments)
55 values
56 ('L35', '16/10/16 11:54', 'Frankfurt Hbf', '16/10/16 16:06', 'Munich Hbf', '04:12:00', 'ICE 623', 'Connection in Wurzburg Hbf with ICE 787');
57
58
59
60Select * from Lab4_Lee_Trains where (departure >= '2016-10-13 00:00:00' and departure < '2016-10-14 00:00:00') or (arrival >= '2016-10-13 00:00:00' and arrival < '2016-10-14 00:00:00');
61
62--Select * from Lab4_Lee_Trains where (duration >= '03:30:00') and (duration < '04:00:00')--