Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

CS145 - Stanford - SQL Movie-Rating Modification Exercises

By: snovvblind on Nov 3rd, 2011  |  syntax: None  |  size: 0.73 KB  |  views: 792  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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)