Gleis Week 6

Movies from Text File 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 title from movie where title ~ '[0123456789][0123456789][0123456789][0123456789]' and year >= 1980 and year < 1990
   • "Cherry 2000"
   • "2010: The Year We Make Contact"
   • "1984"
   • "Cold Summer of 1953"
   • "Butthole Surfers: Blind Eye See All: Live 1985"
   • "Gundam 0080: War in the Pocket"
   • "Transylvania 6-5000"
   • "Hong Kong 1941"
   • "1969"
   • "Mystery Science Theater 3000: The Crawling Hand"
   • "Eric Clapton & Friends: Live 1986"
   • "The 2000 Year Old Man"
   • "Teenage Mutant Ninja Turtles (1980s)"
3. select title from movie where title ~ '
   • "Paula Abdul's Get Up & Dance"
   • "Class of Nuke 'Em High 2"
   • "By Dawn's Early Light"
   • "Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
   • "Sesame Street: Elmo's World: The Street We Live On"
   • "Something's Gotta Give"
   • "ABC Primetime: Mel Gibson's The Passion of the Christ"
   • "Ashtanga Yoga: Beginner's Practice with Nicki Doane"
   • "Zatoichi's Conspiracy"
   • "We're Not Married"
4. select count(*) from movie where title ~ '^Star'
   • 88
5. select year, count(*) from movie where year > 1939 and year < 1950 group by year order by year
   • 1940;34
   • 1941;33
   • 1942;33
   • 1943;33
   • 1944;36
   • 1945;37
   • 1946;39
   • 1947;37
   • 1948;36
   • 1949;41
6. select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5
   • "Bruce Lee: A Warrior's Journey";5
   • "Zatoichi's Conspiracy";5
   • "The Legend of Zelda: Ganon's Evil Tower";5
   • "Donnie Darko: Director's Cut";5
7. select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)
   • "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"
   • "Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
   • "She's All That";1;"Doug"
   • "She's All That";3;"Boomer"
   • "Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"
8. select rating, count(*) from rating where member = 42 group by rating order by rating -- answers in (rating; count)
   • 1;1
   • 3;2
   • 4;3
   • 5;4
9. select avg (rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star'
   • 2.6000000000000000
10. Find movies with a title containing the word Idaho made after 1990. Make sure answer is in chronological order.
    • select title, year from movie where title ~ 'Idaho' and year > 1990 group by title, year order by year

    • "My Own Private Idaho";1991
    • "Twin Falls Idaho";1999

Gleis (talk) 22:03, 3 October 2013 (PDT)