LU06 - Assignment: Animal Shelter on the Ron

Prerequisites

Background

We would like to help animals at the Ron Animal Shelter in Root (LU) that are up for adoption (tierschutz-luzern.ch) in our database.

Your tables: gehege, tierart, tier.

Task

1. Insert data records

tierart (columns: name, herkunft) 

Hund, Weltweit domestiziert
 
Katze, Weltweit domestiziert
 
Kaninchen, Europa

gehege (columns: name, standort, flaeche) 

Hundeaussenbereich 1, Garten, 45.50
 
Kleintierraum, Innenbereich, 20.00
 
Katzenzimmer, Innenbereich, 15.00

tier (Columns: name, geburtsdatum, geschlecht, gehege_id, art_id) 

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

2. Deliberately violating a constraint

Try to create an animal without a name. Read the error message.

3. Modifying data

  1. An input error has been detected for Mila: she is actually housed in a different enclosure – change her enclosure_id to 2.
  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.

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.

4. Deleting data

  1. Bäry has been successfully rehomed – delete his record specifically by name.
  2. 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.

Guido Koch