SHARE
TWEET

CS145 - Stanford - SQL Movie-Rating Modification Exercises

snovvblind Nov 3rd, 2011 895 Never
  1. DATABASE: http://www.db-class.org/course/resources/index?page=moviedata
  2.  
  3. 1. Add the reviewer Roger Ebert to your database, with an rID of 209.
  4.  
  5. INSERT into Reviewer
  6. VALUES (209, 'Roger Ebert')
  7.  
  8. 2. Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL.
  9.  
  10. INSERT into Rating
  11. SELECT rID, mID, 5, NULL
  12. FROM Reviewer, Movie
  13. WHERE Reviewer.name = 'James Cameron'
  14.  
  15. 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.)
  16.  
  17. UPDATE Movie
  18. SET Year = Year+25
  19. WHERE mID in (SELECT mID
  20.                        FROM Rating
  21.                        GROUP BY mID
  22.                        HAVING avg(stars) >=4)
RAW Paste Data
Top