---
# System prepended metadata

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

---

# Übung 3 Aufgabe 1 - Simon Stadlinger, Julian Baumann
#### a)
``` sql
SELECT COUNT(*) FROM actress;
```
Ergebnis: 3479
#### b)
``` sql
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)
``` sql
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)
 ``` sql
 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)
```sql
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)
 ```sql
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
