Project 1: SQL Queries

In this project you are asked to write 12 SQL queries on a relational movie database. The data in this database is from the IMDB website. The database exists already on IISQLSRV (see below for instructions on how to connect). The database consists of five tables:

ACTOR (id, fname, lname, gender)
MOVIE (id, name, year, rank)
DIRECTORS (id, fname, lname)
CASTS (pid, mid, role)
MOVIE_DIRECTORS (did, mid)
GENRE (genre, mid)

id column in ACTOR, MOVIE & DIRECTOR tables is a key for the respective table.
CASTS.pid refers to ACTOR.id,
CASTS.mid refers to MOVIE.id
MOVIE_DIRECTORS.did refers to DIRECTORS.id
MOVIE_DIRECTORS.mid refers to MOVIE.id

Note: Unless otherwise specified, in every question below that asks you to return all actors, or all directors, or all movies, you should return their names (first and last respectively).

  1. List all the directors who directed a 'Film-Noir' movie in a leap year. (You need to check that the genre is 'Film-Noir' and year is divisible by 4.) Your query should return director name, the movie name, and the year.

  2. List the first and last names of all the actors who played in the movie 'Officer 444 (1926)'.

  3. List all the actors who acted in a film before 1900 and also in a film after 2000. (That is: < 1900 and > 2000.)

    How can this be ? Actors can't live more than 100 years, right ? You are asked to find the explanation. For that you need to investigate a bit, perhaps run 1-2 additional queries. Once you identify one logical explanation why some actors appear in movies more than 100 years apart, please write it in your turnin, as a comment to the SQL query: keep you answer below 1-2 sentences.

  4. List all directors who directed 150 movies or more, in descending order of the number of movies they directed. Return the directors' names and the number of movies each of them directed.

  5. We want to find actors that played two or more roles in the same movie. Notice that Casts may have occasional dupliates, but we are not interested in these: we want actors that had two ore more distinct roles in the same movie.

    1. Write a query that returns the actors' names, the movie name, and the number of distinct roles that they played in that movie (which will be >= 2).
    2. Write a query that returns the actors' names, the movie name, and all the distinct roles (two ore more) that the actor played in that movie.

    Note: these two queries take several minutes to run on postgres; they take only a few seconds on sql server.

  6. (a) For each year, count the number of movies in that year that had only female actors. (b) Now make a small change: for each year, report the percentage of movies with only female actors made that year, and also the total number of movies made that year. For example, one answer will be:

    1990 28.76 6098
    meaning that in 1990 there were 6098 movies, and 28.76% had only female actors.

  7. Find the film(s) with the largest cast. Return the movie title and the size of the cast. By "cast size" we mean the number of distinct actors that played in that movie: if an actor played multiple roles, or if it simply occurs multiple times in casts we still count her/him only once.

    Note: this query runs very slowly on postgres (several minutes), but it's just a few seconds on sql server.

  8. A decade is a sequence of 10 consecutive years. For example 1965, 1966, ..., 1974 is a decade, and so is 1967, 1968, ..., 1976. Find the decade with the largest number of films.

    Hint: this query combines several subtleties. One thing you may want to check is that the number of movies in that decade doesn't exceed the total number of movies in the database.

  9. The Bacon number of an actor is the length of the shortest path between the actor and Kevin Bacon in the "co-acting" graph. That is, Kevin Bacon has Bacon number 0; all actors who acted in the same film as KB have Bacon number 1; all actors who acted in the same film as some actor with Bacon number 1 have Bacon number 2, etc. Return all actors whose Bacon number is 2.

    To ponder: how can you compute the Bacon number of each actor in the database ?

These 9 queries are of increasing degree of complexity. Some of them are quite advanced SQL queries. If you write them correctly, none should take more than 10 seconds on SQL Server, and none should return more than 100-200 tuples. There are lots of hints for this homework in the lecture notes, and more may be given in class.

WHAT TO TURN IN: a file with all SQL queries and for each query the number of answers that your received.

WHAT YOU WILL LEARN: advanced SQL.

ESTIMATED TIME: 8 hours, but with a high variance. (The entire answer consists of as little as 120 lines of SQL code.)