LU06 – Assignment: Public Transport

Dies ist eine alte Version des Dokuments!

Method of work: Group work (same group as in LU05)
Resources: Your own database from LU05.
Time: approx. 30 minutes

We would like to enter, amend and delete VBZ tram routes from Zurich in our database.

Your tables: line, stop, journey.

line (columns: name, operator)

Linie 4, VBZ
 
Linie 11, VBZ
 
Linie 8, VBZ

bus stop (columns: name, town)

Bellevue, Zürich
 
Rehalp, Zürich
 
Tiefenbrunnen, Zürich
 
Klusplatz, Zürich
 
Zoo, Zürich

journey (Columns: date, departure_time, price, route_id)

('2026-09-22', '07:15:00', 4.60, 1),
('2026-09-22', '07:45:00', 4.60, 1),
('2026-09-22', '08:05:00', 4.60, 2),
('2026-09-22', '08:20:00', 4.60, 3),
('2026-09-22', '08:40:00', 4.60, 2);

Try to add a route without a name (name is NOT NULL). Read the error message.

The price for all journeys on line 4 increases to 4.80 – update using WHERE line_id.
One journey on line 8 is rescheduled: date and departure time are changing – select a specific journey (e.g. by combining date and departure time) and change both columns in a single query.

First, use SELECT to check how many rows are affected by your WHERE condition – this is particularly important if you’re filtering by line_id, as this may affect several journeys at once.

==== 4. Deleting data ==== - A single journey is cancelled – delete it specifically (e.g. by combining date and departure time).