LU06 - Assignment: Swiss Regions

Prerequisites

Background

We would like to record, amend and delete municipalities and waste incineration plants (KVA) in the canton of Zurich in our database.

Your tables: kanton, kva, gemeinde.

Reminder: The canton has no AUTO_INCREMENT ID – ‘kantons_kennzeichen’ is the primary key and must always be specified manually during an INSERT.

Task:

1. Insert records

kva (columns: name, ort) 

KVA Hagenholz, Zürich
 
KVA Dietikon (Limeco), Dietikon
 
KVA Hinwil, Hinwil
 
KVA Horgen, Horgen
 
KVA Winterthur, Winterthur

gemeinde (Columns: name, einwohnerzahl, plz, kantons_kennzeichen, anlage_id) 

('Uster', 36000, '8610', 'ZH', 3),
('Dietikon', 28000, '8953', 'ZH', 2),
('Horgen', 22000, '8810', 'ZH', 4),
('Winterthur', 114000, '8400', 'ZH', 5),
('Wetzikon', 25000, '8620', 'ZH', 3);

Adjust the ‘kantons_kennzeichen’ to match the value you actually created for ‘kanton’ (e.g. ‘ZH’). Adjust the ‘anlage_id’ to match the order in which you inserted the KVA records.

2. Deliberately violate the constraint

Try entering the same canton a second time (`kantons_kennzeichen` is the primary key). Read the error message.

3. Modify data

  1. Uster has grown – change the population to 36,800.
  2. Wetzikon is being assigned a new postcode and an updated population figure – change the postcode to “8623” and population to 25,400 in a single command.

Filter by the name of the municipality, e.g. ''WHERE name = 'Uster' ''. Use SELECT beforehand to check how many rows are affected.

4. Delete data

  1. Horgen is listed twice by mistake – delete the specific record using the name.
  2. All municipalities assigned to the Hinwil waste-to-energy plant are being re-entered – delete all municipalities with an anlage_id of 3 in one go.

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

Guido Koch