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

Worum geht es?

In dieser Übung lernen Sie, Daten aus mehreren Tabellen mit den verschiedenen Join-Typen zu verknüpfen (siehe Kapitel 7 - Joins). An einem absichtlich “unsauberen” Datenbestand machen Sie fehlende Zuordnungen (“Orphans”) sichtbar.

In dieser Übung üben Sie:

Anwenden: alle Join-Typen (INNER, LEFT, RIGHT, FULL, CROSS) sicher einsetzen.
Filtern: verknüpfte Daten filtern und verwaiste Datensätze (“Orphans”) identifizieren.
Einsetzen: Semi-Joins mit EXISTS zur effizienten Filterung verwenden.
Verknüpfen: eine Tabelle mit sich selbst (Self-Join) verbinden.

Szenario: E-Sports Tournament Management

Um die Performance der Teams zu analysieren, müssen Daten aus der Team-Verwaltung und der Match-Datenbank kombiniert werden. Da das System historisch gewachsen ist, gibt es Inkonsistenzen (Teams ohne Spiele und Spiele ohne gültige Team-Zuordnung), die durch Joins sichtbar gemacht werden sollen.

Tabellen-Schema & 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, -- Beabsichtigt ohne Foreign Key Constraint!
    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);

Arbeitsaufträge (SQL-Abfragen)

Teil A: Standard Joins (Einfach bis Mittel)

Ergebnisliste (INNER JOIN): Erstellen Sie eine Liste mit team_name, game und kills. Es sollen nur Datensätze erscheinen, bei denen ein Team tatsächlich ein Match absolviert hat.
Vollständige Team-Statistik (LEFT JOIN): Listen Sie alle Teams (team_name) auf, inklusive ihrer Spiele (game). Teams ohne Spiele (wie ‘Arctic Foxes’) müssen ebenfalls in der Liste erscheinen.
Daten-Fehler finden (RIGHT JOIN): Identifizieren Sie Match-Einträge, die keinem existierenden Team zugeordnet sind. Geben Sie game, kills und die (nicht existierende) team_id aus.
Totaler Daten-Audit (FULL OUTER JOIN): Erstellen Sie eine Liste, die alle Teams und alle Matches kombiniert, unabhängig davon, ob eine Zuordnung existiert.

Teil B: Spezielle Join-Szenarien (Mittel)

Sponsoring-Simulation (CROSS JOIN): Verknüpfen Sie alle Teams (team_name) mit einer fiktiven Liste von zwei Sponsoren: 'Red Bull' und 'Logitech'. Jeder Teamname soll mit jedem Sponsor kombiniert werden.
Inaktive Teams identifizieren: Finden Sie mittels LEFT JOIN und einer WHERE ... IS NULL Bedingung alle Teams, die bisher noch kein einziges Match bestritten haben.
Gezielte Filterung: Zeigen Sie team_name und kills für alle Spiele in der Region ‘Europe’ an, bei denen mehr als 30 Kills erzielt wurden. Nutzen Sie dafür Tabellen-Aliase (z. B. t für teams, m für matches).

Teil C: Fortgeschrittene Techniken (Schwer)

Existenz-Check (Semi-Join): Listen Sie alle Details (*) der Tabelle teams auf, aber nur für jene Teams, für die mindestens ein Eintrag in der Tabelle matches existiert. Nutzen Sie hierfür den EXISTS-Operator.
Regionaler Vergleich (Self-Join): Finden Sie Paare von Teams, die in der gleichen Region spielen, aber einen unterschiedlichen Rang haben. (Hinweis: Verknüpfen Sie teams mit sich selbst und vermeiden Sie doppelte Paare wie A-B und B-A).
Matching-Check: Geben Sie eine Liste aus, die anzeigt, ob ein Team “Aktiv” oder “Inaktiv” ist, basierend darauf, ob es Einträge in matches hat (Verwenden Sie einen Join in Kombination mit DISTINCT).

Abgabe & Kriterien

Abgabe der Datei aufgabe14_joins.sql.
Alle Abfragen müssen auf einer PostgreSQL-Datenbank ausführbar sein.
Achten Sie auf saubere Formatierung und die Nutzung von Aliasen zur Lesbarkeit.

Wissens-Check

Frage 1: Was passiert bei einem INNER JOIN, wenn die team_id in der Tabelle matches auf NULL steht?
Frage 2: Warum ist ein CROSS JOIN ohne Filterung bei sehr großen Tabellen (z. B. 1 Mio. Zeilen) gefährlich?
Frage 3: Welchen Join-Typ verwenden Sie, um alle Teams - auch solche ohne Matches - aufzulisten?

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