Questions

Use the imdb database to answer the following questions.

  1. Count the number of movies in each production year.

  2. Find the total number of votes cast for movies in each year since 2000.

  3. What is wrong with this SQL statement, and how can we correct it?

SELECT production_year, sum(votes) 
  FROM movies 
  GROUP BY production_year 
  HAVING votes > 100000
  1. What is wrong with this statement, and how can we correct it?
SELECT production_year, sum(votes) 
  FROM movies 
  GROUP BY votes 
  HAVING votes > 100000

  1. Compute the average rating of movies in 2000 and 2010.

  2. Find the movie id for The Dark Knight.

  3. Using the id you found in the previous question, find all of the cast assignments for The Dark Knight.

  4. Add the names of the people you found in the previous question by joining onto the people table.

  5. Add the names of the roles you found in the previous question by joining onto the role_type table.

  6. How many actors, actresses, and directors where there for The Dark Knight? Find all three numbers using one query.

  7. Identify the five actors who played more than one role in The Dark Knight, and list the roles?

  8. The Godfather trilogy consists of three movies: The Godfather, The Godfather: Part II, and The Godfather: Part III. Find the IDs of all three movies.

  9. List the movie title, person name, and role for all those involved with the original The Godfather movie.

  10. List the movie title, person name, and role for all those involved with the first two Godfather movies.

Hint: One way to do this is with INTERSECT. There are other ways!

  1. List the movie title, person name, and role for all those involved with the all three Godfather movies.

Challenge: Do this without using INTERSECT!

  1. List all the movies that Al Pacino and Robert De Niro have been in together.