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

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

Problem 22

Problem: Add the reviewer Roger Ebert to your database, with an rID of 209.

-- Add the reviewer Roger Ebert to your database, with an rID of 209. 
INSERT INTO Reviewer(rID, name)
VALUES (209, 'Roger Ebert')
##   rid             name
## 1 201   Sarah Martinez
## 2 202     Daniel Lewis
## 3 203  Brittany Harris
## 4 204    Mike Anderson
## 5 205    Chris Jackson
## 6 206 Elizabeth Thomas
## 7 207    James Cameron
## 8 208     Ashley White

Problem 23

Problem: Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL.

-- Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL. 

INSERT INTO Rating  ( rID, mID, stars, ratingDate )
SELECT Reviewer.rID , Movie.mID, 5, NULL FROM Movie
LEFT OUTER JOIN Rating USING (mid)
LEFT OUTER JOIN Reviewer USING (rid)
WHERE Reviewer.name='James Cameron' 
##   rid mid stars ratingdate
## 1 206 106     5 2011-01-19
## 2 207 107     5 2011-01-20

Problem 24

Problem: For all movies that have an average rating of 4 stars or higher, add 25 to the release year. (Update the existing tuples; don’t insert new tuples.)

-- Find the titles of all movies that have no ratings. 
UPDATE movie
SET year = year + 25
WHERE mID IN (
  SELECT mID FROM (
  SELECT AVG(stars) AS astar, mID FROM Rating
  WHERE mID=rating.mID
  GROUP BY mID
  HAVING AVG(stars) >=4) d
) 
##   mid                   title year         director
## 1 101      Gone with the Wind 1939   Victor Fleming
## 2 102               Star Wars 1977     George Lucas
## 3 103      The Sound of Music 1965      Robert Wise
## 4 104                    E.T. 1982 Steven Spielberg
## 5 105                 Titanic 1997    James Cameron
## 6 106              Snow White 1937             <NA>
## 7 107                  Avatar 2009    James Cameron
## 8 108 Raiders of the Lost Ark 1981 Steven Spielberg

Problem 25

Problem: Remove all ratings where the movie’s year is before 1970 or after 2000, and the rating is fewer than 4 stars.

-- Remove all ratings where the movie's year is before 1970 or after 2000, and the rating is fewer than 4 stars. 
DELETE FROM rating
WHERE mID IN (SELECT mID FROM movie WHERE year <1970 OR year > 2000)
AND stars < 4;
##    rid mid stars                   title year
## 1  201 101     4      Gone with the Wind 1939
## 2  201 101     2      Gone with the Wind 1939
## 3  202 106     4              Snow White 1937
## 4  203 103     2      The Sound of Music 1965
## 5  203 108     2 Raiders of the Lost Ark 1981
## 6  203 108     4 Raiders of the Lost Ark 1981
## 7  204 101     3      Gone with the Wind 1939
## 8  205 103     3      The Sound of Music 1965
## 9  205 104     2                    E.T. 1982
## 10 205 108     4 Raiders of the Lost Ark 1981
## 11 206 106     5              Snow White 1937
## 12 206 107     3                  Avatar 2009
## 13 207 107     5                  Avatar 2009
## 14 208 104     3                    E.T. 1982