\documentclass{bschlangaul-aufgabe}
\bLadePakete{syntax,rmodell,spalten}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 3},
  Thematik = {Mitfahrgelegenheiten},
  Referenz = 46116-2014-F.T2-TA2-A3,
  RelativerPfad = Examen/46116/2014/03/Thema-2/Teilaufgabe-2/Aufgabe-3.tex,
  ZitatSchluessel = examen:46116:2014:03,
  BearbeitungsStand = mit Lösung,
  Korrektheit = unbekannt,
  Ueberprueft = {unbekannt},
  Stichwoerter = {SQL, SQL mit Übungsdatenbank, GROUP BY, HAVING},
  EinzelpruefungsNr = 46116,
  Jahr = 2014,
  Monat = 03,
  ThemaNr = 2,
  TeilaufgabeNr = 2,
  AufgabeNr = 3,
}

{
\footnotesize
\begin{multicols}{2}
„Kunde":\index{SQL}\footcite{examen:46116:2014:03}

\begin{tabular}{|l|l|l|l|}
\hline
\bPrimaer{KID} & Name & Vorname & \bFremd{Stadt}\\\hline\hline
K1 & Meier & Stefan & S3\\\hline
K2 & Müller & Peta & S3\\\hline
K3 & Schmidt & Christine & S2\\\hline
K4 & Schulz & Michael & S4\\\hline
\end{tabular}

„Stadt"

\begin{tabular}{|l|l|l|}
\hline
\bPrimaer{SID} & SName & Bundesland\\\hline\hline
S1 & Berlin & Berlin\\\hline
S2 & Nürnberg & Bayern\\\hline
S3 & Köln & Nordrhein-Wesffalen\\\hline
S4 & Stuttgart & Baden-Württemberg\\\hline
S5 & München & Bayern\\\hline
\end{tabular}
\end{multicols}

\begin{multicols}{2}
„Angebot":

\begin{tabular}{|l|l|l|l|l|}
\hline
\bPrimaer{KID} & \bFremd{Start} & \bFremd{Ziel} & \bPrimaer{Datum} & Plätze\\\hline\hline
K4 & S4 & S5 & 08.07.2011 & 3\\\hline
K4 & S5 & S4 & 10.07.2011 & 3\\\hline
K1 & S1 & S5 & 08.07.2011 & 3\\\hline
K3 & S2 & S3 & 15.07.2011 & 1\\\hline
K4 & S4 & S1 & 15.07.2011 & 3\\\hline
K1 & S5 & S5 & 09.07.2011 & 2\\\hline
\end{tabular}

„Anfrage":

\begin{tabular}{|l|l|l|l|}
\hline
\bPrimaer{KID} & \bFremd{Start} & \bFremd{Ziel} & \bPrimaer{Datum}\\\hline\hline
K2 & S4 & S5 & 08.07.2011\\\hline
K2 & S5 & S4 & 10.07.2011\\\hline
K3 & S2 & S3 & 08.07.2011\\\hline
K3 & S3 & S2 & 10.07.2011\\\hline
K2 & S4 & S5 & 05.07.2011\\\hline
K2 & S5 & S4 & 17.07.2011\\\hline
\end{tabular}
\end{multicols}
}

% Datenbankname: mitfahrgelegenheit
\begin{minted}{sql}
CREATE TABLE Stadt (
  SID VARCHAR(100) NOT NULL PRIMARY KEY,
  SName VARCHAR(100) NOT NULL,
  Bundesland VARCHAR(100) NOT NULL
);

CREATE TABLE Anfrage (
  KID VARCHAR(100) NOT NULL,
  Start VARCHAR(100) DEFAULT NULL REFERENCES Stadt (SID),
  Ziel VARCHAR(100) DEFAULT NULL REFERENCES Stadt (SID),
  Datum date NOT NULL,
  PRIMARY KEY (KID, Start, Ziel, Datum)
);

CREATE TABLE Angebot (
  KID VARCHAR(100) NOT NULL,
  Start VARCHAR(100) DEFAULT NULL REFERENCES Stadt (SID),
  Ziel VARCHAR(100) DEFAULT NULL REFERENCES Stadt (SID),
  Datum date NOT NULL,
  Plätze integer DEFAULT NULL,
  PRIMARY KEY (Datum, KID)
);

CREATE TABLE Kunde (
  KID VARCHAR(100) NOT NULL PRIMARY KEY,
  Name VARCHAR(100) DEFAULT NULL,
  Vorname VARCHAR(100) DEFAULT NULL,
  Stadt VARCHAR(100) DEFAULT NULL REFERENCES Stadt (SID)
);

INSERT INTO Stadt (SID, SName, Bundesland) VALUES
  ('S1', 'Berlin', 'Berlin'),
  ('S2', 'Nürnberg', 'Bayern'),
  ('S3', 'Köln', 'NRW'),
  ('S4', 'Stuttgart', 'BW'),
  ('S5', 'München', 'Bayern');

INSERT INTO Anfrage (KID, Start, Ziel, Datum) VALUES
  ('K2', 'S4', 'S5', '2011-07-05'),
  ('K2', 'S4', 'S5', '2011-07-08'),
  ('K3', 'S2', 'S3', '2011-07-08'),
  ('K2', 'S5', 'S4', '2011-07-10'),
  ('K3', 'S3', 'S2', '2011-07-10'),
  ('K2', 'S5', 'S4', '2011-07-17');

INSERT INTO Kunde (KID, Name, Vorname, Stadt) VALUES
  ('K1', 'Meier', 'Stefan', 'S3'),
  ('K2', 'Müller', 'Petra', 'S3'),
  ('K3', 'Schmidt', 'Christine', 'S2'),
  ('K4', 'Schulz', 'Michael', 'S4');

INSERT INTO Angebot (KID, Start, Ziel, Datum, Plätze) VALUES
  ('K1', 'S1', 'S5', '2011-07-08', 3),
  ('K4', 'S4', 'S5', '2011-07-08', 3),
  ('K1', 'S5', 'S4', '2011-07-09', 2),
  ('K4', 'S5', 'S4', '2011-07-10', 3),
  ('K3', 'S2', 'S3', '2011-07-15', 1),
  ('K4', 'S4', 'S1', '2011-07-15', 3);
\end{minted}
\index{SQL mit Übungsdatenbank}

\begin{enumerate}
\item Formulieren Sie die folgenden Anfragen in SQL:\footcite{db:pu:wh}

\begin{enumerate}

%%
%
%%

\item Geben Sie alle Attribute aller Anfragen aus, für die passende
Angebote existieren! Ein Angebot ist passend zu einer Anfrage, wenn
Start, Ziel und Datum identisch sind!

\begin{bAntwort}
\begin{minted}{sql}
SELECT Anfrage.KID, Anfrage.Start, Anfrage.Ziel, Anfrage.Datum
FROM Anfrage, Angebot
WHERE
  Anfrage.Start = Angebot.Start AND
  Anfrage.Ziel = Angebot.Ziel AND
  Anfrage.Datum = Angebot.Datum;
\end{minted}
\end{bAntwort}

%%
%
%%

\item Finden Sie Nachnamen und Vornamen aller Kunden, für die kein
Angebot existiert!

\begin{bAntwort}
\begin{minted}{sql}
SELECT k.Name, k.Vorname
FROM Kunde k
WHERE NOT EXISTS ( SELECT * FROM Angebot a WHERE a.KID = k.KID )
\end{minted}

oder:

\begin{minted}{sql}
SELECT k.Name, k.Vorname
FROM Kunde k
WHERE k.KID NOT IN ( SELECT KID FROM Angebot );
\end{minted}
\end{bAntwort}

%%
%
%%

\item Geben Sie das Datum aller angebotenen Fahrten von München nach
Stuttgart aus und sortieren Sie das Ergebnis aufsteigend!

\begin{minted}{sql}
SELECT Datum
FROM Angebot, Stadt
WHERE
  (SID = Start OR
  SID = Ziel)
  AND
  (SName = 'München' OR SName = 'Stuttgart')
\end{minted}

%%
%
%%

\item Geben Sie für jeden Startort einer Anfrage den Namen der Stadt und
die Anzahl der Anfragen aus.

\begin{minted}{sql}
SELECT SName, COUNT(*)
FROM Anfrage, Stadt
WHERE SID = Start
GROUP BY SID;
\end{minted}

\end{enumerate}

\item Wie sieht die Ergebnisrelation zu folgenden Anfragen auf den
Beispieldaten aus?\index{GROUP BY}\index{HAVING}

\begin{minted}{sql}
SELECT *
FROM
Stadt
WHERE
NOT EXISTS ( SELECT *
FROM Anfrage
WHERE Start = SID OR Ziel = SID ) ;
\end{minted}

\begin{bAntwort}
S1 Berlin Berlin
\end{bAntwort}

\begin{minted}{sql}
SELECT KID, SUM (Plätze)
FROM Angebot
WHERE Plätze > 2
GROUP BY KID
HAVING SUM (Plätze) > 4;
\end{minted}

\end{enumerate}
\end{document}