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

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

Worum geht es?

In dieser Übung steigern Sie den Schwierigkeitsgrad aus Aufgabe 14: Sie verknüpfen mehr als zwei Tabellen, kombinieren verschiedene Join-Typen in einer Kette und nutzen Anti- und Semi-Joins, um Datenlücken aufzuspüren (siehe Kapitel 7 - Joins).

In dieser Übung üben Sie:

• Verknüpfen: mehr als zwei Tabellen in einer Abfrage zusammenführen.
• Kombinieren: verschiedene Join-Typen (LEFT, RIGHT, INNER) in einer Kette einsetzen.
• Anwenden: Anti-Joins und Semi-Joins zur Identifikation von Datenlücken nutzen.

Szenario: Global E-Sports Infrastructure

Das Turnier-Management wurde erweitert. Neben Teams und Matches gibt es nun Sponsoren und Arenen. Die Datenstruktur ist komplexer, da Sponsoren Verträge mit Teams haben und Matches in bestimmten Arenen stattfinden.

Erweitertes Schema & Testdaten

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

CREATE TABLE matches (
    match_id   INT PRIMARY KEY,
    team_id    INT,
    arena_id   INT,
    game       VARCHAR(50),
    kills      INTEGER
);

CREATE TABLE arenas (
    arena_id   INT PRIMARY KEY,
    arena_name VARCHAR(50),
    city       VARCHAR(50),
    capacity   INT
);

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);

---

Arbeitsaufträge (SQL-Abfragen)

Teil A: Die Multi-Join Kette

1. Der Full-Report (4 Tabellen): Erstellen Sie eine Liste, die den team_name, das game, den arena_name und den sponsor_name anzeigt.

   • Nutzen Sie INNER JOINS.
   • Beachten Sie: Ein Team kann mehrere Sponsoren haben, was zu mehrfachen Zeilen pro Match führt.
   • Herausforderung: Was passiert mit Teams, die keinen Sponsor haben?

2. Regionaler Location-Check: Finden Sie alle Teams aus der Region ‘Europe’, die in einer Arena in ‘Tokyo’ gespielt haben. Geben Sie Teamname, Arena und Stadt aus.

Teil B: Komplexe Outer Joins & “Orphans”

3. Die “Verlorenen” Sponsoren: Identifizieren Sie Sponsoren, die aktuell kein Team unterstützen. Geben Sie die Namen dieser Sponsoren aus.

4. Arenen-Auslastung: Listen Sie alle Arenen auf (auch die, in denen noch nie ein Match stattfand) und zeigen Sie dazu die Namen der Teams an, die dort gespielt haben.

   • Hinweis: Hier müssen arenas und matches (und dann teams) verknüpft werden. Überlegen Sie genau, welcher Join-Typ an welcher Stelle stehen muss, um keine Arena zu verlieren.

5. Exklusive Matches: Erstellen Sie einen Audit, der alle Teams und alle Sponsoren gegenüberstellt. Es sollen auch Teams ohne Sponsoren und Sponsoren ohne Teams sichtbar sein.

Teil C: Advanced Filtering (Semi- & Anti-Joins)

6. Sponsor-Validierung: Zeigen Sie alle Details der Tabelle matches an, aber nur für jene Matches, deren teilnehmendes Team mindestens einen Sponsor der Marke ‘Red Bull’ oder ‘Logitech’ hat. Nutzen Sie WHERE EXISTS.

7. Die “Geister-Matches”: Finden Sie alle Matches (Details aus matches), für die entweder das Team in der teams-Tabelle fehlt oder die Arena in der arenas-Tabelle nicht existiert.

8. Self-Join Deluxe: Finden Sie Teams, die in der gleichen Stadt wie ein anderes Team gespielt haben, aber selbst aus einer anderen Region stammen. (Hinweis: Sie müssen über matches und arenas gehen und die Tabelle arenas oder teams mit sich selbst verknüpfen).

---

Abgabe & Kriterien

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

---

Wissens-Check

• Frage 1: Wenn Sie drei Tabellen mit LEFT JOIN verknüpfen (A ➡️ B ➡️ C) und in der Mitte (B) kein passender Datensatz gefunden wird, was passiert mit den Werten aus Tabelle C?

• Frage 2: Warum kann eine Kette von INNER JOINS gefährlich sein, wenn man einen vollständigen Bericht über alle Teams erstellen möchte?

• Frage 3: Erklären Sie den Unterschied in der Ergebnismenge zwischen einem CROSS JOIN mit einer WHERE-Bedingung und einem INNER JOIN mit einer ON-Bedingung.

---

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