====== LU06 - Assignment: Animal Shelter on the Ron ======

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

===== Background =====
We would like to help animals at the **Ron Animal Shelter** in Root (LU) that are up for adoption ([[https://tierschutz-luzern.ch/tierheim-an-der-ron/|tierschutz-luzern.ch]]) in our database.

Your tables: **gehege**, **tierart**, **tier**.

===== Task =====

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

**tierart** (columns: name, herkunft)
<WRAP center box 80% round><code sql>
Hund, Weltweit domestiziert

Katze, Weltweit domestiziert

Kaninchen, Europa
</code></WRAP>

**gehege** (columns: name, standort, flaeche)
<WRAP center box 80% round><code sql>
Hundeaussenbereich 1, Garten, 45.50

Kleintierraum, Innenbereich, 20.00

Katzenzimmer, Innenbereich, 15.00
</code></WRAP>

**tier** (Columns: name, geburtsdatum, geschlecht, gehege_id, art_id)
<WRAP center box 80% round><code sql>
('Zuki', '2024-04-01', 'w', 2, 3),
('Sokka', '2024-04-01', 'm', 2, 3),
('Mila', '2019-09-03', 'w', 3, 2),
('Bäry', '2022-01-20', 'm', 1, 1),
('Luigi', '2025-09-01', 'm', 1, 1),
('Fred', '2025-11-01', 'm', 1, 1);
</code></WRAP>

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

==== 3. Modifying data ====
  - An input error has been detected for Mila: she is actually housed in a different enclosure – change her enclosure_id to **2**.
  - Luigi and Fred are being moved together to a new enclosure **and** their species is being corrected, as they are actually rabbits rather than dogs – change the ‘gehege_id’ for both to 2 **and** `art_id` to 3. To do this, use a `WHERE` clause that matches both at the same time.

<WRAP center round tip 80%>
Filter by name, e.g. ''WHERE name = 'Mila' ''. For both animals at the same time, you could use, for example: ''WHERE name = 'Luigi' OR name = 'Fred' ''. Use SELECT beforehand to check how many rows are affected.
</WRAP>

==== 4. Deleting data ====
  - Bäry has been successfully rehomed – delete his record specifically by name.
  - Zuki and Sokka are being rehomed together – delete both records using a WHERE condition that matches both at the same time.

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