CSEP 544 - Homework 1: SQL Queries

DUE DATE
Tuesday, April 14, 6:30pm
WHAT TO TURN IN:
Your SQL queries and the number of rows returned by each. For queries 7 and 8, also include the answers you obtained.
WHERE TO TURN IN:
You can either turn in a hardcopy in class to Bhushan (we prefer that) or submit it online using  Catalyst.
WHAT YOU WILL LEARN:
Advanced SQL.
LENGTH of ASSIGNMENT
Estimated time: 8 hours (with a high variance). Could be as little as 120 lines of SQL code.
TOOLS
SQL Sever, on iprojsrv.cs.washington.edu
MAY USE OTHER TOOL ?
yes (see below) but you are on your own

This assignment requires you to formulate SQL queries over a relational movie database populated with data from the IMDB website. The database is named IMDB and loaded on the SQL Server running on iprojsrv.cs.washington.edu. You have all been given accounts on this database server with the account id being the same as your UW id and the passwords as given in class. Please change your password immediately. See intructions under Software on the course Webpage on how to access the SQL Server on iprojsrv.cs.washington.edu.

(You may use a different database management system for this assignment, if you like to go off the beaten track. For example, you can use either a different installation of SQL server (e.g. on your machine), or some other DBMS (postgres, Oracle, DB2, whatever). In that case you can either import the data from iprojsrv, or you can get it from here http://www.cs.washington.edu/education/courses/csep544/CurrentQtr/hw/imdb-tar.tar.gz)

The database schema is shown below:

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

 The 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
GENRE.mid refers to MOVIE.id

Write SQL queries for the following. Unless otherwise specified, when asked to return all actors, directors, or 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 at least 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.

  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.

  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. There are lots of hints for this homework in the lecture notes, and more may be given in class.