-- DBS2.1.2.a::
-- Suchen Sie alle Informationen aus der Tabelle MOVIE für Kinofilme aus dem Jahr 1989.
-- Kinofilme sind Einträge in der MOVIE-Tabelle mit Type=C.
-- Erwartete Spalten: id, title, year
SELECT id, title, year FROM moviedb.MOVIE WHERE Year=1989 AND Type='C';

-- DBS2.1.2.b::
-- Finden Sie die Titel (TITLE) aller Kinofilme (d.h. Type=C), sowie die darin
-- auftretenden Charaktere (CHARACTER) und Namen (NAME) des Schauspielers
-- der Schauspielerin des Charakters.
-- Geben Sie nur die Informationen für Filme aus, die entweder vor dem Jahr 1930
-- oder nach dem Jahr 2020 gedreht wurden.
-- Verwenden Sie UNION/UNION ALL, um die beiden Teilmengen (vor 1930 bzw. nach 2020)
-- zusammenzusetzen.
-- Duplikate im Ergebnis sind erlaubt.
-- Erwartete Spalten: title, character, name
SELECT m.TITLE, p.CHARACTER, a.NAME
FROM moviedb.MOVIE m
JOIN moviedb.PLAYS p ON (p.MOVIE = m.ID)
JOIN moviedb.PERSON a ON (a.ID = p.PLAYER)
WHERE Type='C' AND m.YEAR < 1930

UNION ALL

SELECT m.TITLE, p.CHARACTER, a.NAME
FROM moviedb.MOVIE m
JOIN moviedb.PLAYS p ON (p.MOVIE = m.ID)
JOIN moviedb.PERSON a ON (a.ID = p.PLAYER)
WHERE Type='C' AND m.YEAR > 2020;

-- DBS2.1.2.c::
-- Finden Sie die Titel (TITLE) aller Kinofilme (d.h. Type=C), sowie die darin
-- auftretenden Charaktere (CHARACTER) und Namen (NAME) des Schauspielers
-- der Schauspielerin des Charakters.
-- Geben Sie nur die Informationen für Filme aus, die entweder vor dem Jahr 1930
-- oder nach dem Jahr 2020 gedreht wurden.
-- Verwenden Sie diesmal kein UNION/UNION ALL, um die beiden Teilmengen
-- (vor 1930 bzw. nach 2020) zusammenzusetzen.
-- Duplikate im Ergebnis sind erlaubt.
-- Erwartete Spalten: title, character, name
SELECT m.TITLE, p.CHARACTER, a.NAME
FROM moviedb.MOVIE m
JOIN moviedb.PLAYS p ON (p.MOVIE = m.ID)
JOIN moviedb.PERSON a ON (a.ID = p.PLAYER)
WHERE Type='C' AND (m.YEAR < 1930 OR m.YEAR > 2020);
-- DBS2.1.2.d::
-- Finden Sie die Titel (TITLE) aller Kinofilme (Type=C), für die es keinen Regisseur in der Datenbank gibt.
-- Formulieren Sie die Anfrage dabei ohne die Verwendung von EXISTS.
-- Es sollen keine Duplikate im Ergebnis enthalten sein.
-- Erwartete Spalten: title

SELECT DISTINCT m.TITLE
FROM moviedb.MOVIE m
LEFT JOIN moviedb.DIRECTS d ON (m.ID = d.MOVIE)
WHERE m.TYPE='C' AND d.MOVIE IS NULL;

-- DBS2.1.2.e::
-- Finden Sie die Titel (TITLE) aller Kinofilme (Type=C), für die es keinen Regisseur in der Datenbank gibt.
-- Formulieren Sie die Anfrage diesmal mit EXISTS. Es sollen keine Duplikate im Ergebnis enthalten sein.
-- Falls sie EXISTS nicht kennen, recherchieren Sie ggf. die Verwendung dieser Klausel.
-- Erwartete Spalten: title
SELECT DISTINCT m.TITLE
FROM moviedb.MOVIE m
WHERE m.TYPE = 'C' AND NOT EXISTS (SELECT 1 FROM moviedb.DIRECTS d WHERE d.MOVIE = m.id);

-- DBS2.1.2.f::
-- Geben sie alle Kinofilme des Jahres 1960 (Type=C; Spalten MID für MOVIE.ID und TITLE) mit ihren Regisseuren
-- (PID für PERSON.ID und NAME) und Ratings (RATING) aus. Sortieren Sie das Ergebnis nach Regisseur
-- und pro Regisseur nach Rating absteigend, bei gleichem Regisseur und Rating nach Movie-ID aufsteigend.
-- Erwartete Spalten: mid, title, pid, name, rating

SELECT m.ID AS MID, m.TITLE, p.ID AS PID, p.NAME, r.RATING
FROM moviedb.MOVIE m
JOIN moviedb.RATING r ON (m.ID = r.MOVIE)
JOIN moviedb.DIRECTS d ON (m.ID = d.MOVIE)
JOIN moviedb.PERSON p ON (d.DIRECTOR = p.ID)
WHERE m.TYPE = 'C' AND YEAR=1960
ORDER BY p.NAME, r.RATING DESC, m.ID ASC;