CS145 - Stanford - SQL Movie-Rating Modification Exercises

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

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

INSERT into Reviewer
VALUES (209, 'Roger Ebert')

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

INSERT into Rating
SELECT rID, mID, 5, NULL
FROM Reviewer, Movie
WHERE Reviewer.name = 'James Cameron'

3. 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.)

UPDATE Movie
SET Year = Year+25
WHERE mID in (SELECT mID
                       FROM Rating
                       GROUP BY mID
                       HAVING avg(stars) >=4)