Solutions to Stanford SQL Movie Rating Exercises

movie_rating_exercises.sql

-- Q1

SELECT title FROM Movie
WHERE director="Steven Spielberg"

-- Q2

SELECT DISTINCT(year) FROM Movie
JOIN Rating as R on Movie.mID = R.mID
WHERE R.stars > 3 AND R.stars < 6
ORDER BY year ASC

-- Q3

SELECT title from Movie
LEFT JOIN Rating on Rating.mID = Movie.mID
WHERE Rating.mID IS NULL

-- Q4

SELECT name from Reviewer
JOIN Rating ON Rating.rID = Reviewer.rID
WHERE Rating.ratingDate IS NULL

-- Q5

SELECT Reviewer.name, Movie.title, Rating.stars, Rating.ratingDate FROM Rating
JOIN Movie on Movie.mID = Rating.mID
JOIN Reviewer on Reviewer.rID = Rating.rID
ORDER BY Reviewer.name, Movie.title, Rating.stars

-- Q6

SELECT Reviewer.name,Movie.title FROM
(SELECT R1.rID as x, R1.mID as y from Rating R1
JOIN Rating R2 on R2.mID = R1.mID
WHERE R2.rID = R1.rID
AND R2.ratingDate > R1.ratingDate
AND R2.stars > R1.stars)
JOIN Reviewer on Reviewer.rID = x
JOIN Movie on Movie.mID = y

-- Q7

SELECT title, MAX(stars) FROM Movie
LEFT JOIN Rating on Rating.mID = Movie.mID
WHERE Rating.rID IS NOT NULL
GROUP BY title

-- Q8

SELECT title, MAX(stars) - MIN(stars) as spread from Movie
LEFT JOIN Rating on Rating.mID = Movie.mID
GROUP BY title
HAVING spread IS NOT NULL
ORDER BY spread DESC, title

-- Q9

SELECT oldtable.o - newtable.n FROM
(SELECT 'avg' as v1, AVG(s) as o FROM
(SELECT title, year, AVG(stars) as s from Rating
LEFT JOIN Movie on Movie.mID = Rating.mID
GROUP BY Rating.mID)
WHERE year < 1980) AS oldtable

JOIN 

(SELECT 'avg' as v1, AVG(s) as n FROM
(SELECT title, year, AVG(stars) as s from Rating
LEFT JOIN Movie on Movie.mID = Rating.mID
GROUP BY Rating.mID)
WHERE year > 1980) AS newtable ON newtable.v1 = oldtable.v1