\documentclass[11pt]{article}

\usepackage{ngerman,a4}
\usepackage{amstext}
\usepackage{amssymb}
\usepackage{amsmath}
\usepackage{stmaryrd}
\usepackage{enumerate}
\usepackage{alltt}
\usepackage[latin1]{inputenc}
\usepackage{graphicx}

\thispagestyle{empty}

\title{Informationssysteme - Übungsblatt 5}
\author{Jan Hendrik Dithmar - Matrikelnummer 2031259 \\
		Mi, 14-16 Uhr, Andreas Kaster
	}
\date{}

\begin{document}

\newcommand{\AND}{\ensuremath{\wedge}}
\newcommand{\OR}{\ensuremath{\vee}}
\newcommand{\SetR}{\ensuremath{\mathbb{R}}}
\newcommand{\SetN}{\ensuremath{\mathbb{N}}}
\newcommand{\SetC}{\ensuremath{\mathbb{C}}}
\newcommand{\SetZ}{\ensuremath{\mathbb{Z}}}
\newcommand{\SetQ}{\ensuremath{\mathbb{Q}}}
\newcommand{\SetB}{\ensuremath{\mathbb{B}}}
\newcommand{\QED}{\ensuremath{\hspace*{\fill}\square}}
\newcommand{\FLASH}{\ensuremath{\hspace*{\fill}\lightning}}
\newcommand{\OnotO}{\ensuremath{\mathcal{O}}}
\newcommand{\bild}[4]{
	\begin{figure}[htpb]
		\centering
			\includegraphics[width=#1cm]{#2}\\[0.2cm]
			\caption{#3}
			\label{#4}
	\end{figure}
	}

\maketitle

\newpage

\section*{Aufgabe 5.1}
\subsubsection*{(a)}
\begin{verbatim}
SELECT Hausmeister
FROM Gebäude, Professor
WHERE P_Name = 'Weikum'
AND Professor.Gebäude = Gebäude.Gebäude
\end{verbatim}

\subsubsection*{(b)}
\begin{verbatim}
SELECT DISTINCT Student.Matrikel_Nr
FROM Prüfung, Fachrichtung, Student
WHERE Prüfer = Studiendekan
AND Student.Matrikel_Nr = Prüfung.Matrikel_Nr
AND Student.Fachrichtung_Nr = Fachrichtung.Fachrichtung_Nr
\end{verbatim}

\subsubsection*{(c)}
\begin{verbatim}
SELECT Gebäude, Raum
FROM Prüfung, Student, Professor
WHERE S_Name = 'Hugo Meier'
AND Student.Matrikel_Nr = Prüfung.Matrikel_Nr
AND Prüfer = P_Name
AND Fach = 'Betriebssysteme'
\end{verbatim}

\subsubsection*{(d)}
\begin{verbatim}
SELECT Matrikel_Nr
FROM Student
WHERE Semester > 3
MINUS
SELECT DISTINCT Matrikel_Nr
FROM Prüfung
\end{verbatim}

\subsubsection*{(e)}
\begin{verbatim}
SELECT DISTINCT Matrikel_Nr
FROM Student, Prüfung pr1
WHERE Student.Matrikel_Nr = pr1.Matrikel_Nr
AND NOT EXISTS
  (SELECT *
   FROM Prüfung pr2, Professor
   WHERE pr2.Matrikel_Nr = Student.Matrikel_Nr
   AND Prüfer = P_Name
   AND Professor.Fachrichtung_Nr != Student.Fachrichtung_Nr)
\end{verbatim}

\subsubsection*{(f)}
\begin{verbatim}
SELECT DISTINCT Matrikel_Nr
FROM Prüfung
MINUS
SELECT DISTINCT Matrikel_Nr
FROM Prüfung
WHERE Note > 1.0
\end{verbatim}

\subsubsection*{(g)}
\begin{verbatim}
SELECT Matrikel_Nr, AVG(Note)
FROM Prüfung
GROUP BY Matrikel_Nr
\end{verbatim}

\subsubsection*{(h)}
\begin{verbatim}
SELECT DISTINCT Matrikel_Nr
FROM Prüfung pr1
WHERE Note > (SELECT AVG(Note) FROM Prüfung pr2 WHERE pr1.Fach = pr2.Fach)
\end{verbatim}

\subsubsection*{(i)}
\begin{verbatim}
SELECT Fach
FROM Prüfung
GROUP BY Fach
HAVING AVG(Note) >= ALL (SELECT AVG(Note) FROM Prüfung GROUP BY Fach)
\end{verbatim}

\subsubsection*{(j)}
\begin{verbatim}
SELECT DISTINCT S_Name
FROM Student s
WHERE NOT EXISTS
  (SELECT *
   FROM Prüfung pr1
   WHERE s.Matrikel_Nr = pr1.Matrikel_Nr
   AND EXISTS (SELECT *
               FROM Prüfung pr2
               WHERE pr1.Fach = pr2.Fach
               AND pr1.Note < pr2.Note))
\end{verbatim}

\newpage
\section*{Aufgabe 2}

\subsubsection*{(a)}
\begin{verbatim}
SELECT DISTINCT m.titel
FROM Musikstück m, Person p, Autor a
WHERE m.DiskID = a.DiskID
AND m.StückID = a.StückID
AND a.Funktion = 'Komponist'
AND p.PID = a.PID
AND p.Name LIKE '%Chopin%'
\end{verbatim}

\subsubsection*{(b)}
\begin{verbatim}
SELECT DiskTitel, Preis
FROM Disk disk1
WHERE NOT EXISTS (SELECT *
                  FROM Disk disk2
                  WHERE disk2.Preis > disk1.Preis)
\end{verbatim}

\subsubsection*{(c)}
\begin{verbatim}
SELECT disk1.DiskTitel
FROM Disk disk1, Musikstück m1
WHERE disk1.DiskID = m1.DiskID
AND disk1.Preis <= 20
AND NOT EXISTS (SELECT *
                FROM Disk disk2, Musikstück m2
                WHERE disk2.DiskID = m2.DiskID
                AND disk2.Preis <= 20
                AND m1.Länge < m2.Länge)
\end{verbatim}

\subsubsection*{(d)}
\begin{verbatim}
SELECT DISTINCT d.DiskTitel
FROM Disk d, Person p, Musikstück m1, Autor a1
WHERE d.DiskID = a1.DiskID
AND a1.DiskID = m1.DiskID
AND m1.StückID = a1.StückID
AND a1.PID = p.PID
AND a1.Funktion = 'Komponist'
AND p.Name LIKE '%Chopin%'
AND NOT EXISTS (SELECT *
                FROM Musikstück m2, Autor a2
                WHERE m2.DiskID = m1.DiskID
                AND a2.DiskID = m2.DiskID
                AND a2.Funktion = 'Komponist'
                AND a2.PID != p.PID)
\end{verbatim}

\subsubsection*{(e)}
\begin{verbatim}
SELECT DISTINCT d.DiskID, d.DiskTitel
FROM Musikstück m, Disk d
WHERE d.DiskID = m.DiskID
GROUP BY d.DiskID, d.DiskTitel
HAVING MAX(Länge) <= 60
\end{verbatim}

\subsubsection*{(f)}
\begin{verbatim}
SELECT AVG(Preis)
FROM Disk d, Interpret i, Person p
WHERE d.DiskID = i.DiskID
AND p.PID = i.PID
GROUP BY i.DiskID
HAVING COUNT(DISTINCT Nationalität) > 3
\end{verbatim}

\subsubsection*{(g)}
\begin{verbatim}
SELECT d1.DiskID, d1.DiskTitel, SUM(m1.Länge), COUNT(*)
FROM Disk d1, Musikstück m1
WHERE d1.DiskID = m1.DiskID
AND 3 > (SELECT COUNT(*)
         FROM Disk d2, Musikstück m2
         WHERE d2.DiskID = d1.DiskID
         AND m2.DiskID = m1.DiskID
         AND m2.Länge < m1.Länge)
GROUP BY d1.DiskID, d1.DiskTitel
\end{verbatim}


\end{document}
