LU06 – Assignment: Public Transport

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: linie, haltestelle, fahrt.

linie (columns: name, betreiber)

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

haltestelle (columns: name, ort)

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

fahrt (Columns: datum, abfahrtszeit, preis, linien_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. Read the error message.

The price for all journeys on line 4 increases to 4.80 – update using WHERE linien_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 linien_id, as this may affect several journeys at once.

A single journey is cancelled – delete it specifically (e.g. by combining date and departure time).
All journeys on route 11 will be rescheduled – delete all journeys with ‘linien_id’ 2 in one go.

Remember to carry out a dry run using SELECT before every DELETE.

M290-LU06

Guido Koch