Kevinmcgee Week 6

Movies from Text Titles to Tables

1. select * from movie where year < 1915
   • "D.W. Griffith: Years of Discovery 1909-1913"
   • "Lumiere Brothers' First Films"
   • "Tillie's Punctured Romance"
   • "Cabiria"
2. select * from movie where year >1979 and year <1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
   • 1988;"Cherry 2000"
   • 1984;"2010: The Year We Make Contact"
   • 1984;"1984"
   • 1988;"Cold Summer of 1953"
   • 1985;"Butthole Surfers: Blind Eye See All: Live 1985"
   • 1989;"Gundam 0080: War in the Pocket"
   • 1985;"Transylvania 6-5000"
   • 1984;"Hong Kong 1941"
   • 1988;"1969"
   • 1989;"Mystery Science Theater 3000: The Crawling Hand"
   • 1987;"Eric Clapton & Friends: Live 1986"
   • 1982;"The 2000 Year Old Man"
   • 1987;"Teenage Mutant Ninja Turtles (1980s)"
3. select * from movie where title ~ '
4. select count (*) from movie where title ~ '^Star '
   • 61
5. select count (*) from movie where year > 1939 and year < 1950 group by year order by year
   • 34
   • 33
   • 33
   • 36
   • 37
   • 39
   • 37
   • 36
   • 41
6. select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5
   • 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
   • 37;1973;"Zatoichi's Conspiracy";37;42;5
   • 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
   • 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
7. select title, rating, name from movie inner join rating on (rating.movie = movie.id) inner join member on (rating.member = member.id)
   • "Something's Gotta Give";4;"Doug"
   • "Zatoichi's Conspiracy";4;"Natalie"
   • "Zatoichi's Conspiracy";5;"Doug"
   • "Bruce Lee: A Warrior's Journey";2;"Natalie"
   • "Bruce Lee: A Warrior's Journey";5;"Doug"
   • "Star Trek: Deep Space Nine: Season 5";5;"Natalie"
   • "Star Trek: The Next Generation: Season 5";5;"Natalie"
   • "Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
   • "Sesame Street: Cookie Monster's Best Bites";3;"Boomer"
   • "The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
   • "Hello Kitty's Paradise";3;"Doug"
   • "Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"
   • "Star Trek V: The Final Frontier";2;"Natalie"
   • "Star Wars: Episode II: Attack of the Clones";1;"Boomer"
   • "Star Trek: Nemesis";2;"Natalie"
   • "Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
   • "Weekend at Bernie's";4;"Doug"
   • "Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"
   • "Star Trek: Insurrection";1;"Boomer"
   • "Donnie Darko: Director's Cut";5;"Doug"
8. select rating, count (*) from rating where member = 42 group by rating order by rating
   • 1;1
   • 3;2
   • 4;3
   • 5;4
9. select avg(year) from movie where title ~ '^Star'
   • 1991
10. • In this query I was attempting to find any movie in the last decade that was rated a 5 and by who.
    • select title, year, rating, name from movie inner join rating on (rating.movie = movie.id) inner join member on (rating.member = member.id) where rating = 5 and year > 2000 and year <2011
    • "Donnie Darko: Director's Cut";2001;5;"Doug"