====== LU06 - Assignment: Swiss Regions ======

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

===== 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**.

<WRAP center round tip 80%>
**Reminder:** The canton has no AUTO_INCREMENT ID – ‘kantons_kennzeichen’ is the primary key and must always be specified manually during an INSERT.
</WRAP>

===== Task: =====

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

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

KVA Dietikon (Limeco), Dietikon

KVA Hinwil, Hinwil

KVA Horgen, Horgen

KVA Winterthur, Winterthur
</code></WRAP>

**gemeinde** (Columns: name, einwohnerzahl, plz, kantons_kennzeichen, anlage_id)
<WRAP center box 80% round><code sql>
('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);
</code></WRAP>

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 ====
  - Uster has grown – change the population to **36,800**.
  - 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.

<WRAP center round tip 80%>
Filter by the name of the municipality, e.g. ''WHERE name = 'Uster' ''. Use SELECT beforehand to check how many rows are affected.
</WRAP>

==== 4. Delete data ====
  - Horgen is listed twice by mistake – delete the specific record using the name.
  - 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.

----
{{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