I’ve put together a mash-up database of movie ratings from (MovieLens) and the Internet Movie Database (IMDB). You will use this database to complete this assignment and submit this homework to Canvas by the due date as a file named netid_cse30246_hw3.sql.
Log in to db8.cse.nd.edu, log in to mysql and use the cse30246 database. The class-lecture tables are in this database as are the movie tables, prefixed by hw3_
These are the tables:
mysql> use cse30246;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_cse30246 |
+--------------------+
| hw3_imdb_crew |
| hw3_imdb_episodes |
| hw3_imdb_people |
| hw3_imdb_principals |
| hw3_imdb_ratings |
| hw3_imdb_titles |
| hw3_links |
| hw3_movies |
| hw3_ratings |
| hw3_tags |
+--------------------+
Here the IMDB data tables are denoted with hw3_imdb_* and the data from MovieLens does not have the IMDB text.
This matters because the primary key of MovieLens is different from IMDB. But I’ve made hw3_links table that can be used to mash these together.
mysql> select * from hw3_movies where title = 'Rudy';
+---------+-------+--------+
| movieId | title | genres |
+---------+-------+--------+
| 524 | Rudy | Drama |
+---------+-------+--------+
1 row in set (0.03 sec)
mysql> select tconst, primaryTitle, startYear, runtimeMinutes from hw3_imdb_titles where primaryTitle = 'Rudy' and startYear = 1993;
+-----------+--------------+-----------+----------------+
| tconst | primaryTitle | startYear | runtimeMinutes |
+-----------+--------------+-----------+----------------+
| tt0108002 | Rudy | 1993 | 114 |
+-----------+--------------+-----------+----------------+
1 row in set (6.30 sec)
For each question, write a single query to return the answer to the following questions.
>>>You may not use sql commands: JOIN, CASE, or WITH.
1. 5pts How many total movies are in the IMDB database?
+-----------------+
| num_imdb_movies |
+-----------------+
| 757966 |
+-----------------+
2. 10pts How many movies are in both IMDB and MovieLens?
+---------+
| in_both |
+---------+
| no hint |
+---------+
3. 15pts Short Titles. I suspect that movies with short titles dont get very many ratings on MovieLens. Is this true? How many MovieLens ratings are there, and what % are for movies with titles shorter than 9 characters.
+---------------+---------------+
| total_ratings | short_ratings |
+---------------+---------------+
| 32000204 | 0.1675 |
+---------------+---------------+
1 row in set (7.14 sec)
4. 20pts Superfan. Steven Spielberg is a movie director of such movies as ET, Jurassic Park, and Jaws. Who rated Steven Spielberg the most? And what was their average Spielberg-movie rating?
+--------+-------------------+----------------------+
| userId | spielberg_ratings | avg_spielberg_rating |
+--------+-------------------+----------------------+
| 173901 | 36 | 4.28 |
+--------+-------------------+----------------------+
1 row in set (12.18 sec)
5. 15pts Best Friends. Steven Spielberg and Tom Hanks often work together. What movies have these legends both in the credits?
+---------------------+-----------+
| primaryTitle | startYear |
+---------------------+-----------+
| no hint | |
+---------------------+-----------+
6. 15pts IMDb disagreement: IMDB rates movies out of 10, MovieLens out of 5. Let’s normalize by taking the IMDB ratings divided by 2. Then, I wonder: Which Christopher Nolan movie has the largest difference between IMDb’s rating (normalized to a 5-point scale) and MovieLens’s average rating?
+-------------------+------------------+--------------+-------------+------------+
| title | mlens_num_ratngs | mlens_rating | imdb_rating | difference |
+-------------------+------------------+--------------+-------------+------------+
| Dark Knight, The | no more hints here
+-------------------+------------------+--------------+-------------+------------+
7. 15pts What movie has the largest cast (not just actors, but all cast/crew/credits)
+-------------------------------------------+-----------+
| title | cast_size |
+-------------------------------------------+-----------+
| No hint | no hint |
+-------------------------------------------+-----------+
8. 20pts There were eight Harry Potter movies, each with their own MovieLens average rating. But, what was the average of the average ratings? How do we do this? There are, in fact, two ways to think about averaging averages: micro-average, and macro-average. Compute both.
+-----------------+-------------------+
| macro Potter Avg | micro Potter Avg |
+-----------------+-------------------+
| 3.776206250 | 3.76602 |
+-----------------+-------------------+
1 row in set (2.92 sec)
9. 20pts Which Wes Anderson movie was most polarizing?
+-----------------------+
| title |
+-----------------------+
| Royal Tenenbaums, The |
+-----------------------+
10. 20pts Come up with your own question; print out the English query with >select 'What film/actor has the....'; so that we can see it as we grade. Then the query needs to complete execution in 30 seconds or less.
This is an individual assignment. You may discuss strategies in groups, but you may not work together when writing the solutions. SQL-Plagiarism is easy to detect.
Submissions must follow the naming convention netid_cse30246_hw3.sql and be submitted to Canvas by midnight on the due date
We will test your solution on reduced data using the command:
mysql -p -Dcse30246 < netid_cse30246_hw3.sql