====== LU06 - Assignment: Ice Hockey Club  ======

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

===== Starting point =====
We would like to select players from the **ZSC Lions Women’s team** in the database.

Your tables: **trainer**, **team**, **block**, **player**.

===== Task =====

==== 1. Insert data records ====
First, insert the coaching duo and the team:

**trainer** (column: name)
<WRAP center box 80% round><code sql>
Angela Taylor

Christine Meier
</code></WRAP>

**team** (columns: name, age_group, trainer_id)
<WRAP center box 80% round><code sql>
ZSC Lions Frauen, Aktiv, 1
</code></WRAP>

**block** (Columns: description, team_id)
<WRAP center box 80% round><code sql>
1. Linie, 1

2. Linie, 1
</code></WRAP>

Now add **five female players** – ideally using a single MULTIPLE INSERT.

**spieler_in** (columns: name, rueckennummer, spielposition, block_id)
<WRAP center box 80% round><code sql>
('Kira Juodikis', 22, 'Stürmerin', 1),
('Kristi Shashkina', 5, 'Verteidigerin', 1),
('Anja Trummer', 14, 'Stürmerin', 2),
('Isabel Waidacher', 9, 'Stürmerin', 2),
('Fanny Rask', 27, 'Stürmerin', 1);
</code></WRAP>

==== 2. Deliberately violate the constraint ====
Try to add a player **without a name**. Read the error message.

==== 3. Modify data ====
  - Kristi Shashkina has been appointed captain – change her shirt number to **3**.
  - Isabel Waidacher is moving from the second line to the first line **and** her playing position is now listed as "Centre" – update both columns in a single command.

<WRAP center round tip 80%>
First, use SELECT to check how many rows are affected.
</WRAP>

==== 4. Deleting data ====
  - Fanny Rask is changing clubs – delete her record specifically by name.
  - All players in the second line are being reassigned – delete all players with block_id 2 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