LU06 - Assignment: Ice Hockey Club

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

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

Your tables: trainer, team, block, player.

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.

player (columns: name, shirt_number, playing_position, 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);

Try to add a player without a name (`name` is NOT NULL). Read the error message.

1. Kristi Shashkina has been appointed captain – change her shirt number to 3.
2. 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.

1. Fanny Rask is changing clubs – delete her record specifically by name.
2. 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.

Guido Koch