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)

Fabienne Huber
 
Marco Steiner

postgebiet (Columns: name, plz_bereich, poestler_id)

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

briefkasten (Columns: standort, leerungszeit, gebiet_id)

('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);

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.

Filter by location, e.g. ''WHERE standort = 'Central' ''. Use SELECT beforehand to check how many rows are affected.

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.

M290-LU06

Guido Koch