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)

Angela Taylor
 
Christine Meier

team (columns: name, age_group, trainer_id)

ZSC Lions Frauen, Aktiv, 1

block (Columns: description, team_id)

1. Linie, 1
 
2. Linie, 1

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

spieler_in (columns: name, rueckennummer, spielposition, block_id)

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

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.

First, use SELECT to check how many rows are affected.

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.

M290-LU06

Guido Koch