Difference between revisions of "Laurmagee: Week 6"

Latest revision as of 18:39, 26 September 2013

1. What movies were released before 1915?
   • select * from movie where year < 1915
   • movie id; year; title
     1. 4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
     2. 7654;1896;"Lumiere Brothers' First Films"
     3. 9103;1914;"Tillie's Punctured Romance"
     4. 10898;1914;"Cabiria"
2. What movies from the 1980s had 4-digit numbers in their titles?
   • select * from movie where year > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
   • movie id; year; title
     1. 217;1988;"Cherry 2000"
     2. 1163;1984;"2010: The Year We Make Contact"
     3. 1399;1984;"1984"
     4. 1480;1988;"Cold Summer of 1953"
     5. 4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985"
     6. 5482;1989;"Gundam 0080: War in the Pocket"
     7. 5890;1985;"Transylvania 6-5000"
     8. 7725;1984;"Hong Kong 1941"
     9. 15128;1988;"1969"
     10. 15269;1989;"Mystery Science Theater 3000: The Crawling Hand"
     11. 15390;1987;"Eric Clapton & Friends: Live 1986"
     12. 16985;1982;"The 2000 Year Old Man"
     13. 17007;1987;"Teenage Mutant Ninja Turtles (1980s)"
3. What movies have apostrophes in their titles?
   • select * from movie where title ~ '
   • movie id; year; title
     1. 4;1994;"Paula Abdul's Get Up & Dance"
     2. 9;1991;"Class of Nuke 'Em High 2"
     3. 19;2000;"By Dawn's Early Light"
     4. 23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
     5. 27;1962;"Sesame Street: Elmo's World: The Street We Live On"
     6. 30;2003;"Something's Gotta Give"
     7. 32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ"
     8. 34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
     9. 37;1973;"Zatoichi's Conspiracy"
     10. 54;1952;"We're Not Married"
     11. 62;1991;"Ken Burns' America: Empire of the Air"
     12. 92;2002;"ECW: Cyberslam '99"
     13. 113;2000;"Bruce Lee: A Warrior's Journey"
     14. 173;1968;"The Devil's Brigade"
     15. 202;2001;"Ruby's Bucket of Blood"
     16. 215;1998;"That '70s Show: Season 1"
     17. 223;2003;"Chappelle's Show: Season 1"
     18. 233;1971;"Chato's Land"
     19. 248;2001;"Michael Moore's The Awful Truth: Season 2"
     20. 251;2000;"Midsomer Murders: Strangler's Wood"
4. How many movies have titles that begin with the word “Star”?
   • select count(*) from movie where title ~ '^Star'
     • 88
5. How many movies, on a year by year basis, were released in the 1940s (years 1940 to 1949)? (don’t answer this question by listing the movies then counting the results!)
   • select count(*) from movie where year > 1939 and year < 1950
     • 359
   • select count(*) from movie where year = 1940
     • 34
   • select count(*) from movie where year = 1941
     • 33
   • select count(*) from movie where year = 1942
     • 33
   • select count(*) from movie where year = 1943
     • 33
   • select count(*) from movie where year = 1944
     • 36
   • select count(*) from movie where year = 1945
     • 37
   • select count(*) from movie where year = 1946
     • 39
   • select count(*) from movie where year = 1947
     • 37
   • select count(*) from movie where year = 1948
     • 36
   • select count(*) from movie where year = 1949
     • 41
6. Which movies, in alphabetical order, did member number 42 rate as a “5”?
   • select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5 order by title
   • movie id; year; title; movie id; member id; rating
     1. 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
     2. 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
     3. 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
     4. 37;1973;"Zatoichi's Conspiracy";37;42;5
7. Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member that gave this rating.
   • select * from movie inner join rating on (rating.movie = movie.id)
   • movie id; year; title; movie id; member id; rating
     1. 30;2003;"Something's Gotta Give";30;42;4
     2. 37;1973;"Zatoichi's Conspiracy";37;6;4
     3. 37;1973;"Zatoichi's Conspiracy";37;42;5
     4. 113;2000;"Bruce Lee: A Warrior's Journey";113;6;2
     5. 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
     6. 209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5
     7. 2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5
     8. 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1
     9. 4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3
     10. 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
     11. 6762;1993;"Hello Kitty's Paradise";6762;42;3
     12. 6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4
     13. 6908;1989;"Star Trek V: The Final Frontier";6908;6;2
     14. 8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1
     15. 8809;2002;"Star Trek: Nemesis";8809;6;2
     16. 9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5
     17. 10176;1989;"Weekend at Bernie's";10176;42;4
     18. 10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2
     19. 12513;1998;"Star Trek: Insurrection";12513;8;1
     20. 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
     21. 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3
     22. 15532;1999;"She's All That";15532;42;1
     23. 15532;1999;"She's All That";15532;8;3
     24. 15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;2
8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
   • select count(*) from rating where member = 42
     • 10
9. What is the average rating of movies that begin with the word “Star”?
   • select avg(rating) from movie inner join rating on (rating.movie = movie.id) where title ~ '^Star'
     • 2.6
10. Form your own query involving movies, ratings, and members
    • How many movies has member number 8 rated as a 2?
    • select * from movie inner join rating on (rating.movie = movie.id) where member = 8 and rating = 2
    • 2