CS145 - Stanford - SQL Movie-Rating Query Exercises

DATABASE: http://www.db-class.org/course/resources/index?page=moviedata

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

SELECT Title
FROM Movie
WHERE Director = 'Steven Spielberg'

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

SELECT DISTINCT Year
FROM Movie
JOIN Rating ON Rating.mID = Movie.mID
WHERE Stars > '3'
ORDER BY Year

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

SELECT DISTINCT Title
FROM Movie
JOIN Rating
WHERE Movie.mID NOT IN (select Rating.mID FROM Rating)

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.

SELECT Name
FROM Rating
JOIN Reviewer ON Reviewer.rID = Rating.rID
WHERE RatingDate IS Null

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.

SELECT Name, Title, Stars, RatingDate
FROM Rating, Movie
JOIN Reviewer ON Rating.rID = Reviewer.rID
WHERE Rating.mID = Movie.mID
ORDER BY Name, Title, Stars

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

SELECT Name, Title
FROM Rating
JOIN Reviewer ON Rating.rID = Reviewer.rID
JOIN Movie ON Rating.mID = Movie.mID
JOIN Rating AS r2 ON Rating.mID = r2.mID AND Rating.rID = r2.rID
WHERE r2.ratingDate > Rating.ratingDate AND r2.stars > Rating.stars

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.

SELECT Title, Stars
FROM Movie
JOIN Rating ON Rating.mID = Movie.mID
GROUP BY Title
HAVING Count (stars) >= 2

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.

SELECT Title, Spread
FROM Movie
JOIN (SELECT mID, MAX(stars) - MIN(stars) AS Spread
        FROM Rating GROUP BY mID) Rating
        ON Movie.mID = Rating.mID
ORDER BY Spread DESC, Title