---
# System prepended metadata

title: 'Übung 3 Aufgabe 2 - Simon Stadlinger, Julian Baumann'

---

# Übung 3 Aufgabe 2 - Simon Stadlinger, Julian Baumann
##### a)
``` sql
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)
``` sql
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)
```sql
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)
```sql
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


