LU06 – Assignment: Online clothing shop

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

We would like to analyse items from the streetwear label ZRCL (wearezrcl.com) in our database.

Your tables: marke, kategorie, kleidungstueck.

marke (columns: name, herkunftsland)

ZRCL, Schweiz

kategorie (column: name)

Hoodies
 
T-Shirts
 
Jeans

kleidungsstueck (Columns: name, preis, groess, zielgruppe, marken_id, kategorie_id)

('ZRCL Hoodie Classic', 149.00, 'M', 'Damen', 1, 1),
('ZRCL Hoodie Classic', 149.00, 'L', 'Herren', 1, 1),
('ZRCL Basic Tee', 59.00, 'S', 'Damen', 1, 2),
('ZRCL Basic Tee', 59.00, 'M', 'Herren', 1, 2),
('ZRCL Organic Denim', 179.00, 'M', 'Herren', 1, 3);

Try to find an item of clothing without a name. Read the error message.

1. The ‘ZRCL Organic Denim’ is on sale – change the price to 139.00.
2. The “ZRCL Basic Tee” in size S is now also listed as a unisex product and the price has fallen slightly – change the target group to “Unisex” and the price to 54.00 in a single command. Make sure to further filter by size, as there are two entries for “ZRCL Basic Tee”.

1. The “ZRCL Hoodie Classic” in size L is sold out – delete this specific record (using name and size).
2. All items in the “T-shirts” category are being removed from the range – delete all garments with category_id 2 in one go.

Remember to carry out a dry run with SELECT before every DELETE.

Guido Koch