====== LU06 – Assignment: Public Transport ======

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

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

Your tables: **linie**, **haltestelle**, **fahrt**.

===== Task =====

==== 1. Insert data records ====

**linie** (columns: name, betreiber)
<WRAP center box 80% round><code sql>
Linie 4, VBZ

Linie 11, VBZ

Linie 8, VBZ
</code></WRAP>

**haltestelle** (columns: name, ort)
<WRAP center box 80% round><code sql>
Bellevue, Zürich

Rehalp, Zürich

Tiefenbrunnen, Zürich

Klusplatz, Zürich

Zoo, Zürich
</code></WRAP>

**fahrt** (Columns: datum, abfahrtszeit, preis, linien_id)

<WRAP center box 80% round><code sql>
('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);
</code></WRAP>

==== 2. Deliberately breaking the constraint ====

Try to add a route **without a name**. Read the error message.

==== 3. Modify data ====
  - 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.

<WRAP center round tip 80%>
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.
</WRAP>

==== 4. Deleting data ==== 
  - 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.

----
{{tag>M290-LU06}}
[[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Guido Koch