Aufgabe 13 - SQL Mengenoperationen (E-Sports Edition)

Aufgabe 13 - SQL Mengenoperationen (E-Sports Edition)

Worum geht es?

In dieser Übung werden Ergebnismengen mehrerer Abfragen mit den Mengenoperatoren UNION, INTERSECT und EXCEPT kombiniert, ganz ohne komplexe Joins (siehe Kapitel 5 - SQL-Grundlagen). Im Expertenteil werden Mengenoperationen für eine echte Konsistenzanalyse eingesetzt.

Was Sie dafür brauchen

• Kapitel 5 - SQL-Grundlagen, Abschnitt Mengenoperationen.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie führen Ergebnismengen mit UNION und UNION ALL zusammen.
• Sie bilden Schnittmengen mit INTERSECT und Differenzen mit EXCEPT.
• Sie kennen die Voraussetzungen (gleiche Spaltenzahl, passende Typen).
• Sie kombinieren Mengenoperationen zu einer symmetrischen Differenz und finden Duplikate.

Pädagogische Einordnung

• Reproduktion: Mengen mit UNION vereinigen (Teil A).
• Reorganisation und Transfer: Schnittmengen und Differenzen bilden (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: Operationen zu einer Konsistenzanalyse kombinieren (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,
    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);

Tipp: Eine fiktive einzeilige Menge erzeugt man mit SELECT 'Oceania' AS region.

Teil A - Vereinigung (UNION)

1. Alle Regionen aus teams auflisten und per Mengenoperation die fiktiven Regionen 'South America' und 'Oceania' ergänzen.
2. Eine Liste aller vorkommenden IDs erstellen (team_id aus teams und aus matches kombinieren).
3. Die Abfrage aus 2 einmal mit UNION und einmal mit UNION ALL ausführen und den Unterschied erklären (besonders bei Team-ID 1 und 2).

Teil B - Schnittmenge (INTERSECT)

1. Alle team_ids finden, die sowohl in teams als auch in matches existieren.
2. „Top-Regionen” sind Regionen mit Teams vom rank < 10. Diese Liste bilden und nur jene Regionen anzeigen, die auch in der fiktiven Liste 'Europe', 'Asia', 'Antarctica' enthalten sind.

Teil C - Differenz (EXCEPT)

1. team_ids der Teams finden, die in teams stehen, aber keinen Eintrag in matches haben.
2. Alle Regionen aus teams außer der Region von „Arctic Foxes” auflisten.
3. „Daten-Leichen” finden: team_ids aus matches, die nicht in teams geführt werden.

Teil D - Expertenteil: Konsistenzanalyse mit kombinierten Operationen

1. Symmetrische Differenz: In einer einzigen Abfrage alle team_ids finden, die entweder nur in teams oder nur in matches vorkommen (also in genau einer der beiden Tabellen). Kombinieren Sie dazu zwei EXCEPT-Abfragen mit UNION:

   (SELECT team_id FROM teams   EXCEPT SELECT team_id FROM matches)
   UNION
   (SELECT team_id FROM matches EXCEPT SELECT team_id FROM teams);

   Erklären Sie in zwei Sätzen, welche Datenprobleme diese Liste sichtbar macht.

2. Sortierte Gesamtmenge: Die kombinierte ID-Liste aus Teil A so ausgeben, dass sie aufsteigend sortiert ist. Zeigen, dass ORDER BY nur einmal am Ende der gesamten Mengenoperation stehen darf.

3. Duplikate aufspüren: Mit UNION ALL und anschließender Gruppierung ermitteln, welche team_id in der Vereinigung aus teams.team_id und matches.team_id mehr als einmal vorkommt.

   SELECT team_id, count(*) AS cnt
   FROM (
     SELECT team_id FROM teams
     UNION ALL
     SELECT team_id FROM matches
   ) combined
   GROUP BY team_id
   HAVING count(*) > 1
   ORDER BY cnt DESC;

   Erklären Sie, warum diese Auswertung mit UNION (statt UNION ALL) nicht funktionieren würde.

Wissenscheck

1. Warum schlägt SELECT team_name FROM teams UNION SELECT kills FROM matches fehl?
2. Welcher Mengenoperator entspricht einer „UND”-Verknüpfung zweier Abfrageergebnisse?
3. Worin unterscheiden sich UNION und UNION ALL in Ergebnis und Performance?
4. Wie bildet man mit Mengenoperationen eine symmetrische Differenz?
5. Warum braucht das Aufspüren von Duplikaten UNION ALL statt UNION?

Abgabe

• Die Datei aufgabe13_mengen.sql mit allen Abfragen, fehlerfrei ausführbar, SQL-Schlüsselwörter konsistent groß.

---

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