Difference between revisions of "Laurmagee: Week 6"
From LMU BioDB 2013
(Added Titles and Answers) |
(Almost Done! One More Left) |
||
| Line 78: | Line 78: | ||
#*#4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5 | #*#4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5 | ||
#*#37;1973;"Zatoichi's Conspiracy";37;42;5 | #*#37;1973;"Zatoichi's Conspiracy";37;42;5 | ||
| − | |||
| − | |||
| − | |||
# 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. | # 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. | ||
#* | #* | ||
#* | #* | ||
| − | |||
# How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively? | # How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively? | ||
| − | #* | + | #*select count(*) from rating where member = 42 |
| − | #* | + | #*10 |
| − | + | ||
| − | + | ||
# What is the average rating of movies that begin with the word “Star”? | # 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 |
| − | + | # Form your own query involving movies, ratings, and members | |
| − | # 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 |
| − | #* | + | |
Revision as of 18:19, 26 September 2013
- What movies were released before 1915?
- select * from movie where year < 1915
- movie id; year; title
- 4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
- 7654;1896;"Lumiere Brothers' First Films"
- 9103;1914;"Tillie's Punctured Romance"
- 10898;1914;"Cabiria"
- 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
- 217;1988;"Cherry 2000"
- 1163;1984;"2010: The Year We Make Contact"
- 1399;1984;"1984"
- 1480;1988;"Cold Summer of 1953"
- 4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985"
- 5482;1989;"Gundam 0080: War in the Pocket"
- 5890;1985;"Transylvania 6-5000"
- 7725;1984;"Hong Kong 1941"
- 15128;1988;"1969"
- 15269;1989;"Mystery Science Theater 3000: The Crawling Hand"
- 15390;1987;"Eric Clapton & Friends: Live 1986"
- 16985;1982;"The 2000 Year Old Man"
- 17007;1987;"Teenage Mutant Ninja Turtles (1980s)"
- What movies have apostrophes in their titles?
- select * from movie where title ~ '
- movie id; year; title
- 4;1994;"Paula Abdul's Get Up & Dance"
- 9;1991;"Class of Nuke 'Em High 2"
- 19;2000;"By Dawn's Early Light"
- 23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
- 27;1962;"Sesame Street: Elmo's World: The Street We Live On"
- 30;2003;"Something's Gotta Give"
- 32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ"
- 34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
- 37;1973;"Zatoichi's Conspiracy"
- 54;1952;"We're Not Married"
- 62;1991;"Ken Burns' America: Empire of the Air"
- 92;2002;"ECW: Cyberslam '99"
- 113;2000;"Bruce Lee: A Warrior's Journey"
- 173;1968;"The Devil's Brigade"
- 202;2001;"Ruby's Bucket of Blood"
- 215;1998;"That '70s Show: Season 1"
- 223;2003;"Chappelle's Show: Season 1"
- 233;1971;"Chato's Land"
- 248;2001;"Michael Moore's The Awful Truth: Season 2"
- 251;2000;"Midsomer Murders: Strangler's Wood"
- How many movies have titles that begin with the word “Star”?
- select count(*) from movie where title ~ '^Star'
- 88
- select count(*) from movie where title ~ '^Star'
- 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
- select count(*) from movie where year > 1939 and year < 1950
- 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
- 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
- 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
- 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
- 37;1973;"Zatoichi's Conspiracy";37;42;5
- 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.
- How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
- select count(*) from rating where member = 42
- 10
- 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
- select avg(rating) from movie inner join rating on (rating.movie = movie.id) where title ~ '^Star'
- 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
