LU06 - Task: Post delivery

• Working method: 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 city districts and postcodes for the city of Zurich in our database.

Your tables: poestler_in, postgebiet, letterbox.

poestler_in (Column: name)

Fabienne Huber
 
Marco Steiner

postal_area (Columns: name, postcode_area, 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

Postbox (Columns: location, collection_time, area_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);

Try to create a postcode area without a name . Read the error message.

1. The collection for “Central” will now take place half an hour later – change the collection time to 17:00:00.
2. 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.

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

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

Guido Koch