\documentclass{bschlangaul-aufgabe}
\bLadePakete{syntax,rmodell}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 6},
  Thematik = {Fußballweltmeisterschaft},
  Referenz = 66116-2021-F.T1-TA2-A6,
  RelativerPfad = Examen/66116/2021/03/Thema-1/Teilaufgabe-2/Aufgabe-6.tex,
  ZitatSchluessel = examen:66116:2021:03,
  BearbeitungsStand = mit Lösung,
  Korrektheit = unbekannt,
  Ueberprueft = {unbekannt},
  Stichwoerter = {SQL, SQL mit Übungsdatenbank, Top-N-Query},
  EinzelpruefungsNr = 66116,
  Jahr = 2021,
  Monat = 03,
  ThemaNr = 1,
  TeilaufgabeNr = 2,
  AufgabeNr = 6,
}

\begin{liRelationenSchemaFormat}
Nation (Land*, Kapitän[Spieler_ID], Trainer)
Spiel (Spiel_ID*, Ort, Datum, Team1[Nation], Team2[Nation], ToreTeam1, ToreTeam2)
Spieler (Spieler_ID*, Name, Vorname, Wohnort, Land[Nation])
Platzverweise (Platzverweis_ID*, Spiel_ID[Spiel], Spieler_ID[Spieler], Spielminute)
\end{liRelationenSchemaFormat}

Gegeben ist folgendes Relationenschema zur Verwaltung von Daten aus der
Fußballweltmeisterschaft:
\index{SQL}
\footcite{examen:66116:2021:03}

Die Tabelle Match wurde in Spiel umgenannt, da es sonst zu Konfliken mit
der SQL-Syntax kommt, da match ein SQL Schlüsselwort ist.

Nation (Land, Kapitän, Trainer)
Kapitän ist Fremdschlüssel zu Spieler\_ID in Spieler.

Spiel (Spiel\_ID, Ort, Datum, Team1, Team2, ToreTeam1, ToreTeam2)
Team1 ist Fremdschlüssel zu Land in Nation.
Team2 ist Fremdschlüssel zu Land in Nation.

Spieler (Spieler\_ID, Name, Vorname, Wohnort, Land)
Land ist Fremdschlüssel zu Land in Nation.

Platzverweise (Platzverweis\_ID,Spiel\_ID, Spieler\_ID, Spielminute)
Spiel\_ID ist Fremdschlüsssel zu Spiel\_ID in Spiel.
Spieler\_ID ist Fremdschlüssel zu Spieler\_ID in Spieler.

Die Primärschlüssel der Relationen sind wie üblich durch Unterstreichen
gekennzeichnet. Pro Ort und Datum findet jeweils nur ein Spiel statt.

Formulieren Sie folgende Abfragen in SQL. Vermeiden Sie nach Möglichkeit
übermäßige Nutzung von Joins und Views.

\begin{bAdditum}[Übungsdatenbank]
% Datenbankname: weltmeisterschaft
\begin{minted}{sql}
CREATE TABLE Spieler (
  Spieler_ID INTEGER PRIMARY KEY,
  Name VARCHAR(20),
  Vorname VARCHAR(20),
  Wohnort VARCHAR(50),
  Land VARCHAR(50)
);

CREATE TABLE Nation (
  Land VARCHAR(50) PRIMARY KEY,
  Kapitän INTEGER REFERENCES Spieler(Spieler_ID),
  Trainer VARCHAR(50)
);

CREATE TABLE Spiel (
  Spiel_ID INTEGER PRIMARY KEY,
  Ort VARCHAR(50),
  Datum DATE,
  Team1 VARCHAR(50) REFERENCES Nation(Land),
  Team2 VARCHAR(50) REFERENCES Nation(Land),
  ToreTeam1 INTEGER,
  ToreTeam2 INTEGER
);

CREATE TABLE Platzverweise (
  Platzverweis_ID INTEGER PRIMARY KEY,
  Spiel_ID INTEGER REFERENCES Spiel(Spiel_ID),
  Spieler_ID INTEGER REFERENCES Spieler(Spieler_ID),
  Spielminute VARCHAR(50)
);

INSERT INTO Spieler
  (Spieler_ID, Name, Vorname, Wohnort, Land)
VALUES
  (1, 'Matthäus', 'Lothar', 'Herzogenaurach', 'Deutschland'),
  (2, 'Rizzitelli', 'Luca', 'Genua', 'Italien'),
  (3, 'Beckham', 'David', 'London', 'England'),
  (4, 'Babel', 'Markus', 'München', 'Deutschland'),
  (5, 'Bodden', 'Olaf', 'Kalkar', 'Deutschland'),
  (6, 'Häßler', 'Thomas', 'Berlin', 'Deutschland');

INSERT INTO Nation
  (Land, Kapitän, Trainer)
VALUES
  ('Deutschland', 1, 'Joachim Löw'),
  ('Italien', 2, 'Giovanni Trapatoni'),
  ('England', 3, 'Boris Johnson');

INSERT INTO Spiel
  (Spiel_ID, Ort, Datum, Team1, Team2, ToreTeam1, ToreTeam2)
VALUES
  (1, 'München', '2021-08-01', 'Deutschland', 'Italien', 1, 1),
  (2, 'Nürnberg', '2021-08-02', 'England', 'Deutschland', 1, 5),
  (3, 'Fürth', '2021-08-03', 'Italien', 'England', 3, 1),
  (4, 'Leverkusen', '2021-08-05', 'Deutschland', 'England', 3, 1);

INSERT INTO Platzverweise
  (Platzverweis_ID, Spiel_ID, Spieler_ID, Spielminute)
VALUES
  (1, 1, 1, 37),
  (2, 1, 2, 36),
  (3, 1, 3, 35),
  (4, 1, 4, 34),
  (5, 1, 5, 33),
  (6, 1, 6, 32),
  (7, 2, 1, 31);
\end{minted}
\index{SQL mit Übungsdatenbank}

\end{bAdditum}
\begin{enumerate}

%%
% a)
%%

\item Ermitteln Sie die Anzahl der Platzverweise pro Spieler und geben
Sie jeweils Name und Vorname des Spielers mit aus. Die Ausgabe soll nach
der Anzahl der Platzverweise absteigend erfolgen.

\begin{bAntwort}
\begin{minted}{sql}
SELECT COUNT(*) AS Anzahl, s.Name, s.Vorname
FROM Platzverweise p, Spieler s
WHERE p.Spieler_ID = s.Spieler_ID
GROUP BY s.Name, s.Vorname
ORDER BY Anzahl DESC;
\end{minted}

\begin{minted}{md}
 anzahl |    name    | vorname
--------+------------+---------
      2 | Matthäus   | Lothar
      1 | Bodden     | Olaf
      1 | Beckham    | David
      1 | Rizzitelli | Luca
      1 | Babel      | Markus
      1 | Häßler     | Thomas
(6 rows)
\end{minted}
\end{bAntwort}

%%
% b)
%%

\item Welches ist die maximale Anzahl an Toren, die eine Mannschaft
insgesamt im Turnier erzielt hat? (Sie dürfen der Einfachheit halber
annehmen, dass jede Mannschaft jeweils mindestens einmal als Team1 und
Team2 angetreten ist.)

\begin{bAntwort}
\begin{minted}{sql}
SELECT MAX(tmp2.Summe) FROM (
  SELECT Team, SUM(Summe) as Summe FROM (
    SELECT Team1 AS Team, SUM(ToreTeam1) AS Summe
      FROM Spiel
      GROUP BY Team1, ToreTeam1
    UNION
    SELECT Team2 AS Team, SUM(ToreTeam2) AS Summe
      FROM Spiel
      GROUP BY Team2, ToreTeam2
  ) AS tmp
  GROUP BY Team
) as tmp2;
\end{minted}

\begin{minted}{md}
 max
-----
   9
(1 row)
\end{minted}
\end{bAntwort}

%%
% c)
%%

\item Wie viele Tore sind im Turnier insgesamt gefallen?

\begin{bAntwort}
\begin{minted}{sql}
SELECT SUM(ToreTeam1 + ToreTeam2) AS GesamtanzahlTore
FROM Spiel;
\end{minted}
\end{bAntwort}

%%
% d)
%%

\item Ermitteln Sie die Namen und Länder der fünf Spieler, die nach der
kürzesten Spielzeit einen Platzverweis erhielten. Die Ausgabe soll
nummeriert erfolgen (beginnend bei 1 für die kürzeste Spielzeit).
\index{Top-N-Query}

\begin{bAntwort}
\begin{minted}{sql}
SELECT s.Name, s.Land, COUNT(*) AS Rang
FROM Spieler s, Platzverweise p1, Platzverweise p2
WHERE
  s.Spieler_ID = p2.Spieler_ID AND
  p1.Spielminute <= p2.Spielminute
GROUP BY s.Name, s.Land, p2.Spieler_ID
HAVING COUNT(*) < 6
ORDER BY Rang;
\end{minted}

Der Erstplatzierte kommt durch die WHERE-Bedingungen nur einmal in der
Relation vor, weil sein Eintrag genau einmal mit sich selbst vorkommt.
Alle anderen Einträge, bei denen die \texttt{p2.Spieler\_ID}, der
Spieler mit der „geringsten Minute“ ist, werden ja eliminiert, da ja nur
die Einträge behalten werden, die der Bedingung \texttt{p1.Spielminute
<= p2.Spielminute} entsprechen.
\end{bAntwort}

\end{enumerate}
\end{document}