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

SELECT COUNT(*) FROM actress;

Ergebnis: 3479
#### b)

SELECT DISTINCT name FROM producer 
EXCEPT 
SELECT name FROM producer INNER JOIN movie 
ON producer.movie_id = movie.mid;

Ergebnis:
Scorsese, Martin
Hofmann, Trish
Ruiz Sandoval, Humberto
Horan, Ralph
Kousakis, John Peter
Briskin, Frederick
Potamkin, Buzz
Winter, Donna (II)
Matouk, Antonio
#### c)

WITH movie_actor AS(
    SELECT actor.name AS aName, movie.title AS mTitle 
    FROM actor JOIN movie 
    ON actor.movie_id = movie.mid
    )
SELECT m1.mTitle, m2.mTitle 
FROM movie_actor AS m1 JOIN movie_actor AS m2 
ON m1.aName = m2.aName AND m1.mTitle < m2.mTitle
ORDER BY m2.mTitle ASC;

Ergebnis:
Amiche del cuore, Le | Amore imperfetto, L'
Air-Tight | Back Stage
Andy Clyde Gets Spring Chicken | Boobs in Arms
Andy Clyde Gets Spring Chicken | Boobs in Arms
Boobs in Arms | Booty and the Beast
Big Idea, The | Booty and the Beast
Boobs in Arms | Booty and the Beast
Boobs in Arms | Booty and the Beast
Air-Tight | Bromo and Juliet
Beer | Cry for Help: The Tracey Thurman Story, A
4 aventures de Reinette et Mirabelle
...
#### d)

 SELECT name 
 FROM actor WHERE 
 movie_id = (SELECT mid FROM movie
     WHERE title = 'Edge of Night, The')
 UNION
 SELECT name 
 FROM producer WHERE
 movie_id = (SELECT mid FROM movie
     WHERE title = 'Edge of Night, The');

Ergebnis:
Rogers, Brooks
Grant, Bernie
Fowkes, Conard
Gallery, James
Theoharous, Theodore
Willey, Walt
Marino, Dennis
Kramer, Harry
Goodsite, Niles (II)
Weary, A.C.
Tammi, Tom
Urich, Tom
Studer, Hal
Sterling, Philip
Serbagi, Richard
Dunn, Ralph
Holder, Christopher
Noble, James (I)
...

#### e)

SELECT maa.title, COUNT(maa.aName) as count FROM
    (SELECT movie.title AS title, actor.name AS aName 
    FROM movie JOIN actor 
    ON movie.mid = actor.movie_id 
    UNION 
    SELECT movie.title AS title, actress.name AS aName 
    FROM movie JOIN actress 
    ON movie.mid = actress.movie_id) AS maa
GROUP BY maa.title 
ORDER BY count DESC FETCH FIRST 10 ROWS ONLY;

Ergebnis:
title | count
-------------------------------+-------
General Hospital | 1089
Edge of Night, The | 465
Meister Eder und sein Pumuckl | 92
Bobby Jones, Stroke of Genius | 85
I Was a Communist for the FBI | 74
Do Not Disturb | 64
Ned Kelly | 62
They Won't Believe Me | 57
Real Bullets | 56
Gigli | 54

#### f)

SELECT aam.name, COUNT(aam.title) as count FROM
    (SELECT actor.name as name, movie.title as title FROM actor JOIN movie ON     actor.movie_id = movie.mid
    UNION
    SELECT actress.name as name, movie.title as title FROM actress JOIN movie     ON actress.movie_id = movie.mid) AS aam
GROUP BY aam.name
ORDER BY count DESC FETCH FIRST 10 ROWS ONLY;

Ergebnis:
name | count
----------------------+-------
Bayrhammer, Gustl | 5
Richardson, Jack (I) | 5
Gorss, Sol | 4
Roy, Nirupa | 4
Brauer, Charles | 3
Chandler, Eddy | 3
Daniels, Mickey | 3
Alexander, Georg (I) | 3
Blanch, Anita | 3
Bardoux, Rebecca | 3