# Übung 3 Aufgabe 2 - Simon Stadlinger, Julian Baumann
##### a)

SELECT DISTINCT actor.name 
FROM actor JOIN 
(SELECT movie.mid as mid FROM genre JOIN movie ON genre.movie_id = movie.mid WHERE genre.genre = 'Action') AS movieGenre
ON movieGenre.mid = actor.movie_id
WHERE actor.name LIKE 'T%';

Ergebnis:
Thomas, Dean Milor
Thompson, Brian (I)
Tucker, Marcellus
Thunderwolf
Tokmakchiev, Toncho
Thompson, James Brewster
Thring, Frank (I)
Tertzarian, Krikor
Tilley, Colin (I)
Thome, Keath
##### b)

SELECT DISTINCT actor.name 
FROM actor JOIN 
(SELECT movie.mid as mid FROM genre JOIN movie ON genre.movie_id = movie.mid WHERE genre.genre = 'Action') AS movieGenre
ON movieGenre.mid = actor.movie_id
WHERE actor.name LIKE 'T%' 
EXCEPT
SELECT DISTINCT actor.name 
FROM actor JOIN 
(SELECT movie.mid as mid FROM genre JOIN movie ON genre.movie_id = movie.mid WHERE genre.genre != 'Action') AS movieGenre
ON movieGenre.mid = actor.movie_id
WHERE actor.name LIKE 'T%';

Ergebnis:
Thomas, Dean Milor
Thompson, Brian (I)
Tucker, Marcellus
Thunderwolf
Thompson, James Brewster
##### c)

SELECT producer.name 
FROM producer JOIN movie ON producer.movie_id=movie.mid 
WHERE movie.mid IN 
    (SELECT movie.mid 
    FROM movie JOIN genre ON movie.mid = genre.movie_id 
    WHERE genre.genre IN 
        (SELECT genre FROM genre 
        GROUP BY genre 
        HAVING COUNT(movie_id) >= 200))
AND movie.year = '2001';

Ergebis:
Betsworth, Marc
Circelli, Nick
Delest, Nathalie
Greenberg, Marc L.
Habig, Jeremy
Kanehl, Oliver
Kapinos, Matthias
Nichols, Deborah
Peters, Maria
Pos, Hans
Schram, Dave
...
##### d)

SELECT year, COUNT(mid) 
FROM movie GROUP BY year 
HAVING year=(
    SELECT MAX(year) FROM movie) 
UNION
SELECT year, COUNT(mid) 
FROM movie GROUP BY year 
HAVING year=(
    SELECT year FROM movie GROUP BY year
    HAVING COUNT(mid)=(
        SELECT COUNT(mid) 
        FROM movie GROUP BY year 
        ORDER BY COUNT(mid) DESC 
        FETCH FIRST ROW ONLY)
    FETCH FIRST ROW ONLY);

Ergebnis:
1997 | 36
2008 | 1