Dwilliams week 6 assignment
From LMU BioDB 2013
1) Select Movies made before 1915
- Code: select title from movie where year < 1915
"D.W. Griffith: Years of Discovery 1909-1913" "Lumiere Brothers' First Films" "Tillie's Punctured Romance" "Cabiria"
2) What movies from the 1980s had 4-digit numbers in their titles?
- Code: select * from movie where year > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
"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 Movies With Apostrophes in the Title
- Code: select * from movie where title ~ ''''
--1280 Results-- "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" "Ken Burns' America: Empire of the Air" "ECW: Cyberslam '99" "Bruce Lee: A Warrior's Journey" "The Devil's Brigade" "Ruby's Bucket of Blood" "That '70s Show: Season 1" "Chappelle's Show: Season 1" "Chato's Land" "Michael Moore's The Awful Truth: Season 2" "Midsomer Murders: Strangler's Wood"
4) How many movies have titles that begin with the word “Star”?
- Code: select count (*) from movie where title ~ '^Star '
61 movies begin with the word Star
5) How many movies, on a year by year basis, were released in the 1940s?
- 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) Which movies, in alphabetical order, did member number 42 rate as a “5”?
- Code: select title from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title
"Bruce Lee: A Warrior's Journey" "Donnie Darko: Director's Cut" "The Legend of Zelda: Ganon's Evil Tower" "Zatoichi's Conspiracy"
7) Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member who gave the rating.
- Code: select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) order by title
"Bruce Lee: A Warrior's Journey";5;"Doug" "Bruce Lee: A Warrior's Journey";2;"Natalie" "Donnie Darko: Director's Cut";5;"Doug" "Hello Kitty's Paradise";3;"Doug" "Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug" "Sesame Street: Cookie Monster's Best Bites";3;"Boomer" "Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug" "She's All That";1;"Doug" "She's All That";3;"Boomer" "Something's Gotta Give";4;"Doug" "Star Trek V: The Final Frontier";2;"Natalie" "Star Trek V: The Final Frontier: Bonus Material";2;"Boomer" "Star Trek: Deep Space Nine: Season 5";5;"Natalie" "Star Trek: Insurrection";1;"Boomer" "Star Trek: Nemesis";2;"Natalie" "Star Trek: The Next Generation: Season 5";5;"Natalie" "Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer" "Star Wars: Episode II: Attack of the Clones";1;"Boomer" "Star Wars: Episode VI: Return of the Jedi";5;"Boomer" "Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie" "The Legend of Zelda: Ganon's Evil Tower";5;"Doug" "Weekend at Bernie's";4;"Doug" "Zatoichi's Conspiracy";5;"Doug" "Zatoichi's Conspiracy";4;"Natalie"
8) How many movie has member 42 rated 1-5 respectively?
- code: select rating, count(*) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 group by rating order by rating
Rating: 1 Count: 1 Rating: 2 Count: 0 Rating: 3 Count: 2 Rating: 4 Count: 3 Rating: 5 Count: 4
9) What is the average rating of movies beginning with Star?
- code: select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star'
Average Rating = 2.6
10) Form your own query: What movies (in alphabetical order) did member number 6 rate as a 3?
- code: select title from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 6 order by title
"Bruce Lee: A Warrior's Journey" "Star Trek V: The Final Frontier" "Star Trek: Deep Space Nine: Season 5" "Star Trek: Nemesis" "Star Trek: The Next Generation: Season 5" "Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor" "Zatoichi's Conspiracy"
