Erstelle entsprechend der gegebenen Relationen die nötigen Tabellen in einer Datenbank. Verwende dafür den Befehlt CREATE TABLE.

  • movie(mid, title, year)
  • actor(id, name, movie_id, role, order)
  • actress(id, name, movie_id, role, order)
  • producer(id, name, movie_id, role)
  • genre(id, movie_id, genre)

Die Datentypen sind wie folgt zu wählen:

  • Die Attribute id, year und order sind vom Typ INTEGER
  • Alle anderen Attribute sind vom Typ VARCHAR(127)

Definiere auch die Primärschlüssel für die einzelnen Tabellen.

Hinweise zu dem verwendeten IMDb-Datensatz:

  • Die Tabellen actor, actress, producer und genre enthalten eine Spalte mit dem Namen "id”. Hierbei handelt es sich um Zeilennummern und nicht um Werte, die etwa einen Film oder eine Person eindeutig identifizieren. Die Tabellen actor und actress speichern daher eher Rollen von Schauspielern in verschiedenen Filmen als Schauspieler selbst.
  • Nimm an, dass Schauspieler, Schauspielerinnen und Produzenten eindeutig über ihre Namen identifiziert werden können.
  • Nimm an, dass die "movie_id”-Attribute jeweils das "mid”-Attribut referenzieren. Eine Fremdschlüssel-Eigenschaft gilt allerdings nicht automatisch in dem zur Verfügung gestellten IMDb-Datensample. Es gibt also z.B. Schauspieler und Genres, deren zugehörige Filme NICHT existieren!

Nenne für jede der folgenden natürlichsprachlichen Fragen eine geeignete SQL-Anfrage und führe sie auf den Daten der IMDb aus. Gib auf deiner Abgabe die Anfrage und deren Ergebnis an.

  1. Wie viele Schauspielerinnen gibt es? (2P)

  2. Gib alle Producer aus, die keine zugehörigen Einträge in der Filmtabelle haben (nach Producernamen sortiert)! Jeder Producer soll dabei nur einmal ausgegeben werden. (3P)

  3. Gib die Titel aller Filmpaare aus, in denen mindestens ein gemeinsamer Schauspieler mitspielt! Sortiere das Ergebnis nach dem Titel des zweiten Films. (3P)

  4. Gib die Namen der Personen (Schauspieler und Produzenten) an, die an der Serie "Edge of Night, The" beteiligt waren und zwar einmal nach Mengen- und ein weiteres Mal nach Multimengensemantik. Hinweis: UNION benutzen. (4P)

  5. Erstelle eine Top-3 Liste der Filme mit den meisten Schauspielern und Schauspielerinnen! Sortiere entsprechend. Hinweis: Recherchiere hierzu die FETCH FIRST Klausel. (4P)

  6. Erstelle eine Top-3 Liste der Schauspieler und Schauspielerinnen mit den meisten Filmen! Sortiere entsprechend. (4P)

Solution

  • 1

    select count(distinct name) as "Number of Actresses"
from actresses;
    Row Number of Actresses
    1 3331

    2

    select *
from producers
where movie_id not in (
    select mid as movie_id from movies
)
order by name;
    Row id name movie_id role
    1 34205 Bovaira, Fernando Vanilla Sky (2001) executive producer
    2 38488 Briskin, Frederick Adventures of Rin Tin Tin, The producer
    3 147431 Hofmann, Trish Running with Scissors (2002) executive producer
    4 149175 Horan, Ralph Michael (1996) executive producer
    5 177843 Kousakis, John Peter Feeling Called Glory, A (1999) co-executive producer
    6 211751 Matouk, Antonio Pablo y Carolina (1957) producer
    7 258028 Potamkin, Buzz Flintstones Christmas Carol, A (1994) (TV) executive producer
    8 279764 Ruiz Sandoval, Humberto Dempsey en México (1925) producer
    9 292571 Scorsese, Martin Kicked in the Head (1997) executive producer
    10 355663 Winter, Donna (II) Girl Talk (2001) producer

    Anmerkung: Diese Anfrage gibt Produzenten aus, die mindestens einen Film gedreht haben, der nicht in 'movie' enthalten ist. 'Bovaira, Fernando' hat zwar 'Vanilla Sky (2001)' gedreht, und dieser Film kommt nicht in 'movie' vor. Jedoch hat 'Bovaira, Fernando' noch einen weiteres Film gedreht (nämlich 'Caja 507, La (2002)'), der sehr wohl in 'movie' vorkommt. [-1 Punkt]

    3

    select distinct movie1.title as "Title 1", movie2.title as "Title 2"
from movies as movie1
inner join actors as actor1 on actor1.movie_id = movie1.mid
inner join actors as actor2 on actor2.name = actor1.name
inner join movies as movie2 on movie2.mid = actor2.movie_id
where movie1.mid < movie2.mid
order by movie2.title;
    Row Title 1 Title 2
    1 Amiche del cuore, Le Amore imperfetto, L'
    2 Air-Tight Back Stage
    3 Andy Clyde Gets Spring Chicken Boobs in Arms
    4 Big Idea, The Booty and the Beast
    5 Boobs in Arms Booty and the Beast
    6 Air-Tight Bromo and Juliet
    7 Beer Cry for Help: The Tracey Thurman Story, A
    8 4 aventures de Reinette et Mirabelle Dame de lieudit, La
    9 Adieu Don Juan
    10 Dernier mot, Le Don Juan
    11 Bare Ass Beach Eclipse
    12 Cafe Fantasy Eclipse
    13 Addicted to Love Eclipse
    14 Bikini Beach Eclipse
    15 Cry for Help: The Tracey Thurman Story, A Edge of Night, The
    … …
    321 Rainha Louca, A Último Vôo do Condor, O

    4

    select name
from actors, movies
where movies.mid = actors.movie_id and title = 'Edge of Night, The'

union

select name
from producers, movies
where movies.mid = producers.movie_id and title = 'Edge of Night, The'
    Row name
    1 Aames, Willie
    2 Addy, Wesley
    3 Albee, Denny
    4 Andrews, Mark (V)
    5 Arnold, Mark (I)
    6 Arnold, Victor (II)
    7 Augustine, Craig
    8 Bain, Conrad
    9 Baio, Joey
    10 Barrett, Leslie (I)
    11 Barrow, Bernard
    12 Beck, Jackson
    13 Bedford Lloyd, John
    14 Bell, Ralph
    15 Berger, Bill
    … …
    284 … 
    select name
from actors, movies
where movies.mid = actors.movie_id and title = 'Edge of Night, The'

union all

select name
from producers, movies
where movies.mid = producers.movie_id and title = 'Edge of Night, The'
    Row name
    1 Aames, Willie
    2 Addy, Wesley
    3 Albee, Denny
    4 Andrews, Mark (V)
    5 Arnold, Victor (II)
    6 Arnold, Mark (I)
    7 Augustine, Craig
    8 Bain, Conrad
    9 Baio, Joey
    10 Barrett, Leslie (I)
    11 Barrow, Bernard
    12 Beck, Jackson
    13 Bedford Lloyd, John
    14 Bell, Ralph
    15 Berger, Bill
    … …
    286 …

    5

    select title as "Movie Title", count(*) as "Number of Actors"
from (
    select * from actors
    union
    select * from actresses
    ) actorMaleFemale
    join movies on movies.mid = actorMaleFemale.movie_id
group by movies.mid
order by "Number of Actors" desc
fetch first 3 rows only;
    Row Movie Title Number of Actors
    1 General Hospital 1089
    2 Edge of Night, The 465
    3 Meister Eder und sein Pumuckl 92

    6

    select name as "Name", count(*) as "Number of Movies"
from (
        select distinct name, actorMaleFemale.movie_id
        from (
            select * from actors
            union
            select * from actresses
        ) actorMaleFemale
    )
group by name
order by "Number of Movies" desc
fetch first 3 rows only
    Row Name Number of Movies
    1 Bayrhammer, Gustl 5
    2 Richardson, Jack (I) 5
    3 Gorss, Sol 4
  • URL:
  • Language: Deutsch
  • Subjects: Databases
  • Type: Name
  • Duration: 45min
  • Credits: 20
  • Difficulty: 0.6
  • Tags: sql query
  • Note:
    HPI, 2015-06-01, Datenbanksysteme 1, Aufgabe 3.1
  • Created By: adius
  • Created At:
    2015-07-26 22:37:34 UTC
  • Last Modified:
    2015-07-26 22:37:34 UTC