Difference between revisions of "Laurmagee: Week 6"
(Added Notes from Class) |
(Added Journal Week 6 Information) |
||
| Line 1: | Line 1: | ||
| − | select * from movie where year < 1915 | + | # What movies were released before 1915? |
| − | + | #*select * from movie where year < 1915 | |
| − | 4975;1909;"D.W. Griffith: Years of Discovery 1909-1913" | + | #*4975;1909;"D.W. Griffith: Years of Discovery 1909-1913" |
7654;1896;"Lumiere Brothers' First Films" | 7654;1896;"Lumiere Brothers' First Films" | ||
9103;1914;"Tillie's Punctured Romance" | 9103;1914;"Tillie's Punctured Romance" | ||
10898;1914;"Cabiria" | 10898;1914;"Cabiria" | ||
| + | # What movies from the 1980s had 4-digit numbers in their titles? | ||
| + | #* | ||
| + | #* | ||
| − | select * from movie where title ~ '''' | + | # What movies have apostrophes in their titles? |
| − | + | #*select * from movie where title ~ '''' | |
| − | 4;1994;"Paula Abdul's Get Up & Dance" | + | #*4;1994;"Paula Abdul's Get Up & Dance" |
9;1991;"Class of Nuke 'Em High 2" | 9;1991;"Class of Nuke 'Em High 2" | ||
19;2000;"By Dawn's Early Light" | 19;2000;"By Dawn's Early Light" | ||
| Line 30: | Line 33: | ||
251;2000;"Midsomer Murders: Strangler's Wood" | 251;2000;"Midsomer Murders: Strangler's Wood" | ||
| − | select count(*) from movie where title ~ '^Star' | + | # How many movies have titles that begin with the word “Star”? |
| + | #*select count(*) from movie where title ~ '^Star' | ||
| + | #*88 | ||
| − | + | # 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!) | |
| + | #* | ||
| + | #* | ||
| − | + | # 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 | |
| − | 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 |
| − | + | ||
| − | 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5 | + | |
37;1973;"Zatoichi's Conspiracy";37;42;5 | 37;1973;"Zatoichi's Conspiracy";37;42;5 | ||
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 | ||
13847;2001;"Donnie Darko: Director's Cut";13847;42;5 | 13847;2001;"Donnie Darko: Director's Cut";13847;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? | ||
| + | #* | ||
| + | #* | ||
| + | |||
| + | |||
| + | # 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. | ||
| + | |||
| + | select * from movie where title ~ '[0123456789][0123456789][0123456789][0123456789] | ||
Revision as of 17:40, 26 September 2013
- What movies were released before 1915?
- select * from movie where year < 1915
- 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?
- What movies have apostrophes in their titles?
- select * from movie where 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
- 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!)
- 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
- 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
- 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.
select * from movie where title ~ '[0123456789][0123456789][0123456789][0123456789]
