====== LU06 - Task: Post delivery ======

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

===== Starting point =====
We would like to **enter, amend and delete city districts and postcodes for the city of Zurich** in our database.

Your tables: **poestler_in**, **postgebiet**, **briefkasten**.

===== Task =====

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

**poestler_in** (Column: name)
<WRAP center box 80% round><code sql>
Fabienne Huber

Marco Steiner
</code></WRAP>

**postgebiet** (Columns: name, plz_bereich, poestler_id)
<WRAP center box 80% round><code sql>
Zürich Kreis 1 - Altstadt, 8001-8001, 1

Zürich Kreis 4 - Aussersihl, 8004-8004, 1

Zürich Kreis 11 - Affoltern/Oerlikon/Seebach, 8046-8052, 2
</code></WRAP>

**briefkasten** (Columns: standort, leerungszeit, gebiet_id)
<WRAP center box 80% round><code sql>
('Bahnhof Stadelhofen', '17:00:00', 1),
('Central', '16:30:00', 1),
('Bahnhof Oerlikon', '17:15:00', 3),
('Escher-Wyss-Platz', '16:45:00', 2),
('Stauffacher', '17:30:00', 2);
</code></WRAP>

==== 2. Deliberately violating the constraint ====
Try to create a postcode area **without a name** . Read the error message.

==== 3. Modifying data ====
  - The collection for “Central” will now take place half an hour later – change the collection time to **17:00:00**.
  - The “Oerlikon railway station” postbox will be moved **and** will now be emptied more frequently – change the location to “Oerlikon North Station” **and** set the collection time to 18:00:00 in a single command.

<WRAP center round tip 80%>
Filter by location, e.g. ''WHERE standort = 'Central' ''. Use SELECT beforehand to check how many rows are affected.
</WRAP>

==== 4. Delete data ====
  - The ‘Escher-Wyss-Platz’ postbox is being dismantled – delete it specifically by location.
  - All postboxes in the “Zurich District 4 – Aussersihl” area are being reorganised – delete all postboxes with gebiet_id 2 in one go.

Remember to carry out a dry run with 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