Difference between revisions of "Vkuehn Week 6"
From LMU BioDB 2013
| Line 56: | Line 56: | ||
#*:1980;"Star Wars: Episode V: The Empire Strikes Back" | #*:1980;"Star Wars: Episode V: The Empire Strikes Back" | ||
#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!) | #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 year, count (*) from movie where year >=1940 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 | ||
#Which movies, in alphabetical order, did member number 42 rate as a “5”? | #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 | ||
| + | #*:2000;"Bruce Lee: A Warrior's Journey" | ||
| + | #*:2001;"Donnie Darko: Director's Cut" | ||
| + | #*:1998;"The Legend of Zelda: Ganon's Evil Tower" | ||
| + | #*:1973;"Zatoichi's Conspiracy" | ||
#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? | ||
Revision as of 18:38, 3 October 2013
Individual Journal Week 6
- What movies were released before 1915?
- select*from movie where year <1915
- 1909;"D.W. Griffith: Years of Discovery 1909-1913"
- 1896;"Lumiere Brothers' First Films"
- 1914;"Tillie's Punctured Romance"
- 1914;"Cabiria"
- select*from movie where year <1915
- What movies from the 1980s had 4-digit numbers in their titles?
- select * from movie where year>1939 and year <1950 and title~'[0123456789][0123456789][0123456789][0123456789]'
- 1943;"Batman: The 1943 Serial Collection"
- 1940;"Broadway Melody of 1940"
- select * from movie where year>1939 and year <1950 and title~'[0123456789][0123456789][0123456789][0123456789]'
- What movies have apostrophes in their titles?
- select * from movie where title~ '
- 1994;"Paula Abdul's Get Up & Dance"
- 1991;"Class of Nuke 'Em High 2"
- 2000;"By Dawn's Early Light"
- 2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
- 1962;"Sesame Street: Elmo's World: The Street We Live On"
- 2003;"Something's Gotta Give"
- 2004;"ABC Primetime: Mel Gibson's The Passion of the Christ"
- 2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
- 1973;"Zatoichi's Conspiracy"
- 1952;"We're Not Married"
- 1991;"Ken Burns' America: Empire of the Air"
- 2002;"ECW: Cyberslam '99"
- 2000;"Bruce Lee: A Warrior's Journey"
- 1968;"The Devil's Brigade"
- 2001;"Ruby's Bucket of Blood"
- 1998;"That '70s Show: Season 1"
- 2003;"Chappelle's Show: Season 1"
- 1971;"Chato's Land"
- 2001;"Michael Moore's The Awful Truth: Season 2"
- 2000;"Midsomer Murders: Strangler's Wood"
- select * from movie where title~ '
- How many movies have titles that begin with the word “Star”?
- select * from movie where title~ '^Star '
- 1995;"Star Trek: Voyager: Season 1"
- 1996;"Star Trek: Deep Space Nine: Season 5"
- 1983;"Star 80"
- 1998;"Star Trek: Voyager: Season 5"
- 2003;"Star Trek: Enterprise: Season 3"
- 1993;"Star Trek: The Next Generation: Season 7"
- 2001;"Star Trek: Enterprise: Season 1"
- 1979;"Star Trek: The Motion Picture"
- 2004;"Star Wars Animated Adventures: Ewoks"
- 1991;"Star Trek: The Next Generation: Season 5"
- 2001;"Star Ocean EX"
- 1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor"
- 1992;"Star Trek: The Next Generation: Season 6"
- 1979;"Star Trek: The Motion Picture: Bonus Material"
- 1966;"Star Trek: The Original Series: Vols. 1-15"
- 2002;"Star Trek: Enterprise: Season 2"
- 1979;"Star Blazers: Series 3: The Bolar Wars"
- 1990;"Star Trek: The Next Generation: Season 4"
- 1991;"Star Trek VI: The Undiscovered Country: Bonus Material"
- 1980;"Star Wars: Episode V: The Empire Strikes Back"
- select * 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 year, count (*) from movie where year >=1940 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
- select year, count (*) from movie where year >=1940 and year <1950 group by year order by year
- 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
- 2000;"Bruce Lee: A Warrior's Journey"
- 2001;"Donnie Darko: Director's Cut"
- 1998;"The Legend of Zelda: Ganon's Evil Tower"
- 1973;"Zatoichi's Conspiracy"
- select * from movie inner join rating on (rating.movie=movie.id) where member =42 and rating =5 order by title
- 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?
- What is the average rating of movies that begin with the word “Star”?
- Form your own query involving movies, ratings, and members, then
- State your query in plain English (like the queries above).
- Provide the SQL query "translation" of your plain English query.
- Supply the answer to your query, according to the sample movie database.
