Aufgabe 15 - SQL Joins (E-Sports Edition) - Experts

Aufgabe 15 - SQL Joins (E-Sports Edition) - Experts

Worum geht es?

In dieser Übung steigt der Schwierigkeitsgrad gegenüber Aufgabe 14: Es werden mehr als zwei Tabellen verknüpft, Join-Typen in einer Kette kombiniert und Anti- sowie Semi-Joins genutzt, um Datenlücken aufzuspüren (siehe Kapitel 8 - Joins). Im Expertenteil entsteht ein vollständiger Report, der keine Zeile verliert.

Was Sie dafür brauchen

• Kapitel 8 - Joins, Abschnitte Mehrtabellen-Joins, Outer Joins, Semi-/Anti-/Self-Join.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie verketten mehrere Joins und wählen je Stelle den richtigen Join-Typ.
• Sie spüren verwaiste Datensätze mit Anti-Joins auf.
• Sie kombinieren Joins mit Aggregation, ohne Zeilen zu verlieren.
• Sie lösen ein Self-Join-Problem über mehrere Tabellen hinweg.

Pädagogische Einordnung

• Reproduktion: eine Multi-Join-Kette aufbauen (Teil A).
• Reorganisation und Transfer: Outer Joins und Orphans behandeln (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: einen vollständigen Report konstruieren 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)
);
CREATE TABLE arenas (
    arena_id   INT PRIMARY KEY,
    arena_name VARCHAR(50),
    city       VARCHAR(50),
    capacity   INT
);
CREATE TABLE matches (
    match_id   INT PRIMARY KEY,
    team_id    INT,
    arena_id   INT,
    game       VARCHAR(50),
    kills      INTEGER
);
CREATE TABLE sponsorships (
    sponsor_id   INT PRIMARY KEY,
    sponsor_name VARCHAR(50),
    team_id      INT
);

INSERT INTO teams VALUES (1, 'Cyber Vipers', 'Europe'), (2, 'Neon Samurais', 'Asia'), (3, 'Arctic Foxes', 'North America'), (4, 'Desert Rats', 'Africa');
INSERT INTO arenas VALUES (10, 'Alpha Dome', 'Berlin', 5000), (20, 'Cyber Stadium', 'Tokyo', 15000), (30, 'Empty Hall', 'Unknown', 500);
INSERT INTO matches VALUES (101, 1, 10, 'Valorant', 45), (102, 1, 20, 'League of Legends', 30), (103, 2, 20, 'Counter-Strike', 12), (104, 99, 10, 'Dota 2', 55);
INSERT INTO sponsorships VALUES (501, 'Red Bull', 1), (502, 'Logitech', 1), (503, 'Intel', 3), (504, 'Nvidia', NULL);

Teil A - Multi-Join-Kette

1. Full-Report (4 Tabellen): team_name, game, arena_name und sponsor_name mit INNER JOINs. Notieren, was mit Teams ohne Sponsor passiert.
2. Regionaler Check: alle Teams der Region 'Europe', die in einer Arena in 'Tokyo' gespielt haben (Teamname, Arena, Stadt).

Teil B - Outer Joins und Orphans

1. Verlorene Sponsoren: Sponsoren, die aktuell kein Team unterstützen (Anti-Join).
2. Arenen-Auslastung: alle Arenen (auch ohne Match) mit den Namen der dort spielenden Teams. Genau überlegen, welcher Join-Typ an welcher Stelle stehen muss, um keine Arena zu verlieren.
3. Audit: Teams und Sponsoren gegenüberstellen, sodass auch Teams ohne Sponsor und Sponsoren ohne Team sichtbar sind.

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

1. Sponsor-Validierung: alle Matches, deren Team mindestens einen Sponsor 'Red Bull' oder 'Logitech' hat (WHERE EXISTS).
2. Geister-Matches: alle Matches, deren Team in teams fehlt oder deren Arena in arenas nicht existiert.
3. Self-Join: Teams, die in derselben Stadt wie ein anderes Team gespielt haben, aber aus einer anderen Region stammen (über matches und arenas).

Teil D - Expertenteil: Vollständiger Arena-Report ohne Zeilenverlust

1. Bauen Sie einen Report, der für jede Arena folgende Kennzahlen zeigt, auch für Arenen ohne jedes Match:
   • Anzahl der dort gespielten Matches,
   • Anzahl der verschiedenen Teams, die dort gespielt haben,
   • Gesamtsumme der Kills (bei keiner Aktivität 0). Nutzen Sie LEFT JOIN von arenas auf matches, Aggregatfunktionen, COUNT(DISTINCT ...), COALESCE(SUM(...), 0) und GROUP BY. Sortieren Sie nach der Team-Anzahl absteigend.
2. Erklären Sie in zwei bis drei Sätzen, warum die Reihenfolge der Joins und die Wahl von LEFT JOIN hier entscheidend sind, damit die Arena „Empty Hall” mit 0 erscheint und nicht verschwindet.
3. Zeigen Sie die Falle: Ersetzen Sie den LEFT JOIN durch einen INNER JOIN und beschreiben Sie, welche Zeile dadurch verloren geht und warum.

Wissenscheck

1. Bei A LEFT JOIN B LEFT JOIN C: Was passiert mit den Werten aus C, wenn B keinen Treffer hat?
2. Warum ist eine Kette aus INNER JOINs riskant, wenn ein Bericht über alle Teams gewünscht ist?
3. Worin unterscheidet sich ein CROSS JOIN mit WHERE von einem INNER JOIN mit ON?
4. Warum braucht der Arena-Report COALESCE und COUNT(DISTINCT ...)?
5. Welche Zeile geht verloren, wenn man im Arena-Report LEFT durch INNER ersetzt?

Abgabe

• Die Datei aufgabe15_joins.sql mit allen Abfragen, fehlerfrei ausführbar, mit Aliasen, inklusive des Arena-Reports und der Begründung aus Teil D als Kommentar.

---

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