SQL: Stanford’s Exercises - Movie Rating - PART II

You’ve started a new movie-rating website, and you’ve been collecting data on reviewers’ ratings of various movies.

Problem 10

Problem: Find the names of all reviewers who rated Gone with the Wind.

-- Find the names of all reviewers who rated Gone with the Wind. 
SELECT DISTINCT name
FROM Rating
INNER JOIN Movie
ON Movie.mID = Rating.mID
INNER JOIN Reviewer
ON Reviewer.rID = Rating.rID
WHERE title = 'Gone with the Wind';
##             name
## 1  Mike Anderson
## 2 Sarah Martinez

Problem 11

Problem: For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars.

-- For any rating where the reviewer is the same as the director of the movie, return the reviewer name, movie title, and number of stars. 
SELECT name, title, stars
FROM Rating
INNER JOIN Movie
ON Movie.mID = Rating.mID
INNER JOIN Reviewer
ON Reviewer.rID = Rating.rID
WHERE name = director;
##            name  title stars
## 1 James Cameron Avatar     5

Problem 12

Problem: Return all reviewer names and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing “The”.)

-- Return all reviewer names and movie names together in a single list, alphabetized. (Sorting by the first name of the reviewer and first word in the title is fine; no need for special processing on last names or removing "The".)  
SELECT name as cname from Reviewer
union all
select title as cname from Movie
ORDER BY cname ASC;
##                      cname
## 1             Ashley White
## 2                   Avatar
## 3          Brittany Harris
## 4            Chris Jackson
## 5             Daniel Lewis
## 6                     E.T.
## 7         Elizabeth Thomas
## 8       Gone with the Wind
## 9            James Cameron
## 10           Mike Anderson
## 11 Raiders of the Lost Ark
## 12          Sarah Martinez
## 13              Snow White
## 14               Star Wars
## 15      The Sound of Music
## 16                 Titanic

Problem 13

Problem: Find the titles of all movies not reviewed by Chris Jackson.

-- Find the titles of all movies not reviewed by Chris Jackson. 
SELECT title
FROM Movie
WHERE mID NOT IN ( 
SELECT mID FROM Rating INNER JOIN REVIEWER ON rating.rID = reviewer.rid 
WHERE name = 'Chris Jackson'
);
##                title
## 1 Gone with the Wind
## 2          Star Wars
## 3            Titanic
## 4         Snow White
## 5             Avatar

Problem 14

Problem: For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don’t pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order.

-- For all pairs of reviewers such that both reviewers gave a rating to the same movie, return the names of both reviewers. Eliminate duplicates, don't pair reviewers with themselves, and include each pair only once. For each pair, return the names in the pair in alphabetical order. 
SELECT DISTINCT Reviewer1.name, Reviewer2.name
FROM Rating R1, Rating R2, Reviewer Reviewer1, Reviewer Reviewer2
WHERE R1.mID = R2.mID AND R1.rID = Reviewer1.rID AND R2.rID = Reviewer2.rID AND Reviewer1.name < Reviewer2.name
ORDER BY Reviewer1.name, Reviewer2.name;
##               name             name
## 1     Ashley White    Chris Jackson
## 2  Brittany Harris    Chris Jackson
## 3     Daniel Lewis Elizabeth Thomas
## 4 Elizabeth Thomas    James Cameron
## 5    Mike Anderson   Sarah Martinez

Problem 15

Problem: For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars.

-- For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title, and number of stars. 
SELECT name, title, stars
FROM MOVIE
INNER JOIN RATING ON Movie.mID = Rating.mID
INNER JOIN REVIEWER ON Reviewer.rID = Rating.rID
WHERE stars = (SELECT MIN(stars) FROM RATING);
##              name                   title stars
## 1  Sarah Martinez      Gone with the Wind     2
## 2 Brittany Harris Raiders of the Lost Ark     2
## 3 Brittany Harris      The Sound of Music     2
## 4   Chris Jackson                    E.T.     2

Problem 16

Problem: List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.

-- List movie titles and average ratings, from highest-rated to lowest-rated. If two or more movies have the same average rating, list them in alphabetical order.  
SELECT title, AVG(Stars)
FROM MOVIE
INNER JOIN RATING ON Movie.mID = Rating.mID
INNER JOIN REVIEWER ON Reviewer.rID = Rating.rID
GROUP BY rating.mid, title
ORDER BY AVG(STARS) DESC, title ASC;
##                     title      avg
## 1              Snow White 4.500000
## 2                  Avatar 4.000000
## 3 Raiders of the Lost Ark 3.333333
## 4      Gone with the Wind 3.000000
## 5                    E.T. 2.500000
## 6      The Sound of Music 2.500000

Problem 17

Problem: Find the names of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.)

-- Find the names of all reviewers who have contributed three or more ratings. (As an extra challenge, try writing the query without HAVING or without COUNT.) 
SELECT name
FROM MOVIE
INNER JOIN RATING ON Movie.mID = Rating.mID
INNER JOIN REVIEWER ON Reviewer.rID = Rating.rID
GROUP BY reviewer.rID, name
HAVING COUNT(*) >= 3;
##              name
## 1 Brittany Harris
## 2   Chris Jackson

Problem 18

Problem: Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title. (As an extra challenge, try writing the query both with and without COUNT.)

-- Some directors directed more than one movie. For all such directors, return the titles of all movies directed by them, along with the director name. Sort by director name, then movie title. (As an extra challenge, try writing the query both with and without COUNT.) 
SELECT M1.title, m1.director
FROM MOVIE M1
INNER JOIN MOVIE M2 ON M1.director = M2.director
GROUP BY m1.mID, m1.title, m1.director
HAVING COUNT(*) >1
ORDER BY m1.director, m1.title;
##                     title         director
## 1                  Avatar    James Cameron
## 2                 Titanic    James Cameron
## 3                    E.T. Steven Spielberg
## 4 Raiders of the Lost Ark Steven Spielberg

Problem 19

Problem: Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

-- Find the movie(s) with the highest average rating. Return the movie title(s) and average rating. (Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)  
SELECT title, AVG(stars) as average
FROM MOVIE
INNER JOIN RATING ON Movie.mID = Rating.mID
INNER JOIN REVIEWER ON Reviewer.rID = Rating.rID
GROUP BY movie.mID, title
HAVING  AVG(stars) = (
SELECT MAX(avg_stars)
FROM (
SELECT title, AVG(stars) AS avg_stars
FROM MOVIE
INNER JOIN RATING ON Rating.mID = Movie.mID
GROUP BY movie.mID, title
) I
);
##        title average
## 1 Snow White     4.5

Problem 20

Problem: Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating. (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the lowest average rating and then choosing the movie(s) with that average rating.)

-- Find the movie(s) with the lowest average rating. Return the movie title(s) and average rating. (Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the lowest average rating and then choosing the movie(s) with that average rating.)  
SELECT title, AVG(stars) as average
FROM MOVIE
INNER JOIN RATING ON Movie.mID = Rating.mID
INNER JOIN REVIEWER ON Reviewer.rID = Rating.rID
GROUP BY movie.mID, title
HAVING  AVG(stars) = (
SELECT MIN(avg_stars)
FROM (
SELECT title, AVG(stars) AS avg_stars
FROM MOVIE
INNER JOIN RATING ON Rating.mID = Movie.mID
GROUP BY movie.mID, title
) I
);
##                title average
## 1 The Sound of Music     2.5
## 2               E.T.     2.5

Problem 21

Problem: For each director, return the director’s name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.

-- For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL. 
SELECT director, title, MAX(stars)
FROM Movie
INNER JOIN RATING ON rating.mID = movie.mID
WHERE DIRECTOR is NOT NULL
GROUP BY DIRECTOR, title;
##           director                   title max
## 1      Robert Wise      The Sound of Music   3
## 2   Victor Fleming      Gone with the Wind   4
## 3 Steven Spielberg                    E.T.   3
## 4    James Cameron                  Avatar   5
## 5 Steven Spielberg Raiders of the Lost Ark   4