Rules
1. Submission format: For each question, type your answer into a text file named qN.sql, where N is the
question number. Then, create a ZIP file, containing all those files. We will run your queries (mysql -D imdb
2006).
Question 10 – Audience favorites [5 points] Return the number of movies where the audience rating is strictly
higher than the critics’ rating. Do not forget to scale the two ratings to the same range (i.e., either multiply
audience ratings by two, or divide critic ratings by two).
Question 11 – Contentious directors [5 points] Return the directors’ name that have made movies which were
rated higher by audiences than critics, as well as movies which were rated higher by critics than audiences. Again,
do not forget to scale ratings to the same range. Sort the results in ascending order of name.
Question 12 – Cruise collaborators [5 points] Return the actors’ name who have collaborated with ‘Tom Cruise’ in
the largest number of movies. If there is a tie, sort by name in ascending order.
Question 13 – Most popular actors [5 points] Find the top-10 active actors (as defined in Question 9) whose
movies receive the most audience ratings in total. For example, if ‘Tom Hanks’ had played in three movies,
whose audience numratings are 30, 20, and 10 respectively, then the total number of ratings of his movies
would be 30+20+10=60.
Question 14 – Contentious directors revisited [10 points] Similar to Question 11, but each director should have
at least 5 movies rated higher by audiences and at least 5 rated higher by critics. Output the directors’ name,
numhigherbyaudience, numhigherbycritics and sort the results in descending order of numhigherbyaudience +
numhigherbycritics, breaking ties in ascending order of name.
Question 15 – Modern directors’ favorites [10 points] Find all actors who have acted in four or more movies by
the same director since 2005. Output actorname, directorname, nummoviestogether, ordering results first by
number of movies in descending order and breaking ties by actor’s name.
Question 16 – Dual favorite movies [10 points] One analyst wants to find a set of all-time best movies. He considers
two metrics at the same time: its audiencerating and the number of ratings audiencenumratings. The analyst uses
the following rule to determine whether one movie is better than another: For two movies textttm1 and m2, we
define that m1 dominates m2 if and only if m1 has a higher average rating and m1 receives more ratings.
Find a set of movies’ title, audiencenumratings, audiencerating, so that each movie in this set is not dominated
by any other movie in the database. Return the output in ascending order of title. [This type of query is also
known as a skyline query.]