Widom Movies DB

This problem set uses a movie-rating database. An SQL script to implement the database in MySQL and a printout are available in the attachments to this problem set. This is a very small dataset but it is enough for some interesting queries. Here's the schema:

Movie ( mID, title, year, director )
/*meaning: There is a movie with ID number mID, a title, a release year, and a director. SQL syntax for a comment */

Reviewer ( rID, name )
/*meaning: The reviewer with ID number rID has a certain name. */

Rating ( rID, mID, stars, ratingDate )
/*meaning: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate. */

Implement the database in MySQL exactly as you did for the Widom College Application database in the Software setup.

Each problem asks you to write a query in SQL and execute it in MySQL. When you are satisfied with your results capture a screenshot for inclusion in your problem set submission.

• Unless a specific result ordering is asked for, you can return the result rows in any order.
• You are to translate the English into an SQL query which computes the desired result over all possible data for this database. It is possible that your SQL query gets a correct output but is, in fact, not correct. (For example, if the problem asks for a complex question which requires accessing all of the tables, but over our small data set, the condition is satisfied only by "Star Wars", then the SQL query "SELECT title FROM Movie WHERE title = 'Star Wars'" looks correct but will be marked incorrect because your SQL code doesn't reflect the actual question.)

1 - Find the titles of all movies directed by Steven Spielberg.

2 - Find all years that have a movie that received a rating of 4 or 5, and sort them in increasing order.

3 - Find the titles of all movies that have no ratings.

4 - Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date.

5 - Write a query to return the ratings data in a more readable format: reviewer name, movie title, stars, and ratingDate. Also, sort the data, first by reviewer name, then by movie title, and lastly by number of stars.

6 - For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, write a query which returns the reviewer's name and the title of the movie.

7 - For each movie that has at least one rating, find the highest number of stars that movie received. Return the movie title and number of stars. Sort by movie title.

8 - For each movie, return the title and the 'rating spread', that is, the difference between highest and lowest ratings given to that movie. Sort by rating spread from highest to lowest, then by movie title.

9 - Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980. (Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

Widom Movies Database

Table Movie

Table Reviewer

Table Rating