Aufgabe 14 - SQL Joins (E-Sports Edition) - Newcomers

Aufgabe 14 - SQL Joins (E-Sports Edition) - Newcomers

Worum geht es?

In dieser Übung werden Daten aus mehreren Tabellen mit den verschiedenen Join-Typen verknüpft (siehe Kapitel 8 - Joins). An einem absichtlich unsauberen Datenbestand werden fehlende Zuordnungen („Orphans”) sichtbar. Im Expertenteil geht es um die häufigste Join-Falle überhaupt.

Was Sie dafür brauchen

• Kapitel 8 - Joins, Abschnitte INNER/LEFT/RIGHT/FULL/CROSS, Semi- und Anti-Join.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie setzen alle Join-Typen sicher ein.
• Sie spüren verwaiste Datensätze mit Outer Joins und IS NULL auf.
• Sie nutzen Semi-Joins mit EXISTS und verbinden eine Tabelle mit sich selbst.
• Sie erkennen und vermeiden die Filter-Falle beim LEFT JOIN.

Pädagogische Einordnung

• Reproduktion: die Standard-Joins anwenden (Teil A).
• Reorganisation und Transfer: spezielle Join-Szenarien lösen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: die LEFT-JOIN-Filterfalle analysieren und begründen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Schema und Testdaten

CREATE TABLE teams (
    team_id    INT PRIMARY KEY,
    team_name  VARCHAR(50),
    region     VARCHAR(30),
    rank       INTEGER
);

CREATE TABLE matches (
    match_id   SERIAL PRIMARY KEY,
    team_id    INTEGER, -- absichtlich ohne Foreign Key
    game       VARCHAR(50),
    kills      INTEGER
);

INSERT INTO teams VALUES
(1, 'Cyber Vipers', 'Europe', 1),
(2, 'Neon Samurais', 'Asia', 5),
(3, 'Arctic Foxes', 'North America', 12),
(4, 'Desert Rats', 'Africa', 20);

INSERT INTO matches (team_id, game, kills) VALUES
(1, 'League of Legends', 25),
(1, 'Valorant', 40),
(2, 'Counter-Strike', 15),
(2, 'League of Legends', 10),
(99, 'Dota 2', 50);

Teil A - Standard-Joins

1. INNER JOIN: team_name, game und kills aller Teams, die tatsächlich ein Match gespielt haben.
2. LEFT JOIN: alle Teams mit ihren Spielen, auch Teams ohne Match (wie „Arctic Foxes”).
3. RIGHT JOIN: Match-Einträge, die keinem existierenden Team zugeordnet sind (game, kills, team_id).
4. FULL OUTER JOIN: alle Teams und alle Matches, unabhängig von der Zuordnung.

Teil B - Spezielle Szenarien

1. CROSS JOIN: alle Teams mit den zwei fiktiven Sponsoren 'Red Bull' und 'Logitech' kombinieren.
2. Inaktive Teams: mit LEFT JOIN und WHERE ... IS NULL alle Teams ohne Match finden.
3. Gezielte Filterung: team_name und kills aller Spiele aus der Region 'Europe' mit mehr als 30 Kills, mit Tabellen-Aliasen.

Teil C - Semi-, Anti- und Self-Join

1. Semi-Join: alle Details der Teams, für die mindestens ein Match existiert (mit EXISTS).
2. Self-Join: Paare von Teams aus derselben Region mit unterschiedlichem Rang, ohne Doppelpaare (A-B und B-A).
3. Matching-Check: Liste, die je Team „Aktiv” oder „Inaktiv” anzeigt, je nachdem, ob es Einträge in matches hat.

Teil D - Expertenteil: Die LEFT-JOIN-Filterfalle

1. Schreiben Sie eine Abfrage, die alle Teams samt ihrer Spiele im Spiel 'Valorant' zeigt und dabei auch Teams behält, die kein Valorant gespielt haben (deren Spiel dann NULL ist). Erster Versuch:

   SELECT t.team_name, m.game
   FROM teams t
   LEFT JOIN matches m ON m.team_id = t.team_id
   WHERE m.game = 'Valorant';

   Führen Sie diese Abfrage aus. Sie liefert nicht das gewünschte Ergebnis. Erklären Sie in zwei bis drei Sätzen, warum die WHERE-Bedingung den LEFT JOIN faktisch in einen INNER JOIN verwandelt.

2. Korrigieren Sie die Abfrage, indem Sie die Bedingung in die ON-Klausel verschieben, und zeigen Sie, dass nun alle Teams erscheinen.

   SELECT t.team_name, m.game
   FROM teams t
   LEFT JOIN matches m ON m.team_id = t.team_id AND m.game = 'Valorant';

3. Formulieren Sie als Merksatz, wann eine Bedingung in die ON-Klausel und wann in die WHERE-Klausel gehört.

4. Zusatz: Ermitteln Sie je Region die Gesamtzahl der Kills (auch Regionen mit Teams ohne Match sollen mit 0 erscheinen). Nutzen Sie LEFT JOIN, COALESCE und GROUP BY.

Wissenscheck

1. Was passiert bei einem INNER JOIN, wenn die team_id in matches auf einen nicht existierenden Wert zeigt?
2. Warum ist ein CROSS JOIN ohne Filter bei großen Tabellen gefährlich?
3. Welchen Join-Typ verwendet man, um alle Teams inklusive der ohne Matches zu listen?
4. Warum verwandelt ein WHERE m.game = 'Valorant' einen LEFT JOIN in einen INNER JOIN?
5. Wann gehört eine Bedingung in die ON- und wann in die WHERE-Klausel?

Abgabe

• Die Datei aufgabe14_joins.sql mit allen Abfragen, fehlerfrei ausführbar, mit Aliasen und sauberer Formatierung, inklusive der beiden Varianten aus Teil D und dem Merksatz als Kommentar.

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at