Difference between revisions of "HDelgadi Week 6"
From LMU BioDB 2013
(Updating the last of the answers) |
(editing answer) |
||
| (6 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
#What movies were released before 1915? | #What movies were released before 1915? | ||
#*select * from movie where year < 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? | #What movies from the 1980s had 4-digit numbers in their titles? | ||
| − | # | + | #*select * from movie where year >= 1980 and year <1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]' |
| − | + | :217;1988;"Cherry 2000" | |
| − | 217;1988;"Cherry 2000" | + | :1163;1984;"2010: The Year We Make Contact" |
| − | 1163;1984;"2010: The Year We Make Contact" | + | :1399;1984;"1984" |
| − | 1399;1984;"1984" | + | :1480;1988;"Cold Summer of 1953" |
| − | 1480;1988;"Cold Summer of 1953" | + | :4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985" |
| − | 4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985" | + | :5482;1989;"Gundam 0080: War in the Pocket" |
| − | 5482;1989;"Gundam 0080: War in the Pocket" | + | :5890;1985;"Transylvania 6-5000" |
| − | 5890;1985;"Transylvania 6-5000" | + | :7725;1984;"Hong Kong 1941" |
| − | 7725;1984;"Hong Kong 1941" | + | :15128;1988;"1969" |
| − | 15128;1988;"1969" | + | :15269;1989;"Mystery Science Theater 3000: The Crawling Hand" |
| − | 15269;1989;"Mystery Science Theater 3000: The Crawling Hand" | + | :15390;1987;"Eric Clapton & Friends: Live 1986" |
| − | 15390;1987;"Eric Clapton & Friends: Live 1986" | + | :16985;1982;"The 2000 Year Old Man" |
| − | 16985;1982;"The 2000 Year Old Man" | + | :17007;1987;"Teenage Mutant Ninja Turtles (1980s)" |
| − | 17007;1987;"Teenage Mutant Ninja Turtles (1980s)" | + | |
| − | + | ||
#What movies have apostrophes in their titles? | #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" | + | :23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo" |
| − | 23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo" | + | :27;1962;"Sesame Street: Elmo's World: The Street We Live On" |
| − | 27;1962;"Sesame Street: Elmo's World: The Street We Live On" | + | :30;2003;"Something's Gotta Give" |
| − | 30;2003;"Something's Gotta Give" | + | :32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ" |
| − | 32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ" | + | :34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane" |
| − | 34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane" | + | :37;1973;"Zatoichi's Conspiracy" |
| − | 37;1973;"Zatoichi's Conspiracy" | + | :54;1952;"We're Not Married" |
| − | 54;1952;"We're Not Married" | + | :62;1991;"Ken Burns' America: Empire of the Air" |
| − | 62;1991;"Ken Burns' America: Empire of the Air" | + | :92;2002;"ECW: Cyberslam '99" |
| − | 92;2002;"ECW: Cyberslam '99" | + | :113;2000;"Bruce Lee: A Warrior's Journey" |
| − | 113;2000;"Bruce Lee: A Warrior's Journey" | + | :173;1968;"The Devil's Brigade" |
| − | 173;1968;"The Devil's Brigade" | + | :202;2001;"Ruby's Bucket of Blood" |
| − | 202;2001;"Ruby's Bucket of Blood" | + | :215;1998;"That '70s Show: Season 1" |
| − | 215;1998;"That '70s Show: Season 1" | + | :223;2003;"Chappelle's Show: Season 1" |
| − | 223;2003;"Chappelle's Show: Season 1" | + | :233;1971;"Chato's Land" |
| − | 233;1971;"Chato's Land" | + | :248;2001;"Michael Moore's The Awful Truth: Season 2" |
| − | 248;2001;"Michael Moore's The Awful Truth: Season 2" | + | :251;2000;"Midsomer Murders: Strangler's Wood" |
| − | 251;2000;"Midsomer Murders: Strangler's Wood" | + | :257;1973;"Charlotte's Web" |
| − | 257;1973;"Charlotte's Web" | + | :285;1997;"The Devil's Own" |
| − | 285;1997;"The Devil's Own" | + | :299;2001;"Bridget Jones's Diary" |
| − | 299;2001;"Bridget Jones's Diary" | + | :324;1994;"Mother's Boys" |
| − | 324;1994;"Mother's Boys" | + | :387;1984;"Bizet's Carmen" |
| − | 387;1984;"Bizet's Carmen" | + | :392;1993;"Danielle Steel's Heartbeat" |
| − | 392;1993;"Danielle Steel's Heartbeat" | + | :421;2003;"Agatha Christie's Poirot: Sad Cypress" |
| − | 421;2003;"Agatha Christie's Poirot: Sad Cypress" | + | :426;2001;"Recess: School's Out" |
| − | 426;2001;"Recess: School's Out" | + | :428;2004;"Barney: Barney's Colorful World: Live" |
| − | 428;2004;"Barney: Barney's Colorful World: Live" | + | :434;2004;"Extreme Engineering: Tokyo's Sky City" |
| − | 434;2004;"Extreme Engineering: Tokyo's Sky City" | + | :450;2000;"Isaac Stern: Life's Virtuoso" |
| − | 450;2000;"Isaac Stern: Life's Virtuoso" | + | :476;2004;"VeggieTales Classics: Where's God When I'm Scared?" |
| − | 476;2004;"VeggieTales Classics: Where's God When I'm Scared?" | + | :484;1992;"Danielle Steel's Jewels" |
| − | 484;1992;"Danielle Steel's Jewels" | + | :485;1967;"The Avengers '67" |
| − | 485;1967;"The Avengers '67" | + | :494;1983;"Monty Python's The Meaning of Life: Special Edition" |
| − | 494;1983;"Monty Python's The Meaning of Life: Special Edition" | + | :497;2003;"Broadway's Lost Treasures" |
| − | 497;2003;"Broadway's Lost Treasures" | + | :506;1976;"Blank Generation / Dancin' Barefoot" |
| − | 506;1976;"Blank Generation / Dancin' Barefoot" | + | :510;2004;"Blue's Clues: Blue's Room: It's Hug Day" |
| − | 510;2004;"Blue's Clues: Blue's Room: It's Hug Day" | + | :520;2004;"E's Otherwise" |
| − | 520;2004;"E's Otherwise" | + | |
| − | + | ||
#How many movies have titles that begin with the word “Star”? | #How many movies have titles that begin with the word “Star”? | ||
| − | # | + | #*select count(*) from movie where title ~ '^Star ' |
| − | + | :61 Movies that begin with "Star" | |
| − | 61 Movies that begin with "Star" | + | :138;1995;"Star Trek: Voyager: Season 1" |
| − | + | :209;1996;"Star Trek: Deep Space Nine: Season 5" | |
| − | 138;1995;"Star Trek: Voyager: Season 1" | + | :337;1983;"Star 80" |
| − | 209;1996;"Star Trek: Deep Space Nine: Season 5" | + | :345;1998;"Star Trek: Voyager: Season 5" |
| − | 337;1983;"Star 80" | + | :560;2003;"Star Trek: Enterprise: Season 3" |
| − | 345;1998;"Star Trek: Voyager: Season 5" | + | :752;1993;"Star Trek: The Next Generation: Season 7" |
| − | 560;2003;"Star Trek: Enterprise: Season 3" | + | :822;2001;"Star Trek: Enterprise: Season 1" |
| − | 752;1993;"Star Trek: The Next Generation: Season 7" | + | :1902;1979;"Star Trek: The Motion Picture" |
| − | 822;2001;"Star Trek: Enterprise: Season 1" | + | :2032;2004;"Star Wars Animated Adventures: Ewoks" |
| − | 1902;1979;"Star Trek: The Motion Picture" | + | :2040;1991;"Star Trek: The Next Generation: Season 5" |
| − | 2032;2004;"Star Wars Animated Adventures: Ewoks" | + | :2094;2001;"Star Ocean EX" |
| − | 2040;1991;"Star Trek: The Next Generation: Season 5" | + | :2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor" |
| − | 2094;2001;"Star Ocean EX" | + | :3523;1992;"Star Trek: The Next Generation: Season 6" |
| − | 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor" | + | :4428;1979;"Star Trek: The Motion Picture: Bonus Material" |
| − | 3523;1992;"Star Trek: The Next Generation: Season 6" | + | :4508;1966;"Star Trek: The Original Series: Vols. 1-15" |
| − | 4428;1979;"Star Trek: The Motion Picture: Bonus Material" | + | :4979;2002;"Star Trek: Enterprise: Season 2" |
| − | 4508;1966;"Star Trek: The Original Series: Vols. 1-15" | + | :5091;1979;"Star Blazers: Series 3: The Bolar Wars" |
| − | 4979;2002;"Star Trek: Enterprise: Season 2" | + | :5326;1990;"Star Trek: The Next Generation: Season 4" |
| − | 5091;1979;"Star Blazers: Series 3: The Bolar Wars" | + | :5408;1991;"Star Trek VI: The Undiscovered Country: Bonus Material" |
| − | 5326;1990;"Star Trek: The Next Generation: Season 4" | + | :5582;1980;"Star Wars: Episode V: The Empire Strikes Back" |
| − | 5408;1991;"Star Trek VI: The Undiscovered Country: Bonus Material" | + | :5656;1986;"Star Trek IV: The Voyage Home" |
| − | 5582;1980;"Star Wars: Episode V: The Empire Strikes Back" | + | :5705;2002;"Star Wars: Episode II: Attack of the Clones: Bonus Material" |
| − | 5656;1986;"Star Trek IV: The Voyage Home" | + | :6655;1994;"Star Trek: Deep Space Nine: Season 3" |
| − | 5705;2002;"Star Wars: Episode II: Attack of the Clones: Bonus Material" | + | :6908;1989;"Star Trek V: The Final Frontier" |
| − | 6655;1994;"Star Trek: Deep Space Nine: Season 3" | + | :7145;1984;"Star Trek III: The Search for Spock" |
| − | 6908;1989;"Star Trek V: The Final Frontier" | + | :7639;2004;"Star Wars Trilogy: Bonus Material" |
| − | 7145;1984;"Star Trek III: The Search for Spock" | + | :7865;1999;"Star Trek: Voyager: Season 6" |
| − | 7639;2004;"Star Wars Trilogy: Bonus Material" | + | :7984;1993;"Star Trek: Deep Space Nine: Season 1" |
| − | 7865;1999;"Star Trek: Voyager: Season 6" | + | :7985;1968;"Star Trek: The Original Series: Vols. 29-40" |
| − | 7984;1993;"Star Trek: Deep Space Nine: Season 1" | + | :8085;1979;"Star Blazers: Series 1: The Quest for Iscandar" |
| − | 7985;1968;"Star Trek: The Original Series: Vols. 29-40" | + | :8292;2004;"Star Wars: Clone Wars: Vol. 1" |
| − | 8085;1979;"Star Blazers: Series 1: The Quest for Iscandar" | + | :8420;2004;"Star Wars Animated Adventures: Droids" |
| − | 8292;2004;"Star Wars: Clone Wars: Vol. 1" | + | :8687;2002;"Star Wars: Episode II: Attack of the Clones" |
| − | 8420;2004;"Star Wars Animated Adventures: Droids" | + | :8809;2002;"Star Trek: Nemesis" |
| − | 8687;2002;"Star Wars: Episode II: Attack of the Clones" | + | :9628;1983;"Star Wars: Episode VI: Return of the Jedi" |
| − | 8809;2002;"Star Trek: Nemesis" | + | :9886;1999;"Star Wars: Episode I: The Phantom Menace" |
| − | 9628;1983;"Star Wars: Episode VI: Return of the Jedi" | + | :10027;1982;"Star Trek II: The Wrath of Khan" |
| − | 9886;1999;"Star Wars: Episode I: The Phantom Menace" | + | :10141;2000;"Star Trek: Voyager: Season 7" |
| − | 10027;1982;"Star Trek II: The Wrath of Khan" | + | |
| − | 10141;2000;"Star Trek: Voyager: Season 7" | + | |
| − | + | ||
#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 | |
| − | 1940;34 | + | :1941;33 |
| − | 1941;33 | + | :1942;33 |
| − | 1942;33 | + | :1943;33 |
| − | 1943;33 | + | :1944;36 |
| − | 1944;36 | + | :1945;37 |
| − | 1945;37 | + | :1946;39 |
| − | 1946;39 | + | :1947;37 |
| − | 1947;37 | + | :1948;36 |
| − | 1948;36 | + | :1949;41 |
| − | 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 |
| − | + | :113;2000;"Bruce Lee: A Warrior's Journey";113;42;5 | |
| − | 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5 | + | :13847;2001;"Donnie Darko: Director's Cut";13847;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 |
| − | 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. | ||
| − | # | + | #*select *, rating from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) |
| − | + | :30;2003;"Something's Gotta Give";30;42;4;42;"Doug";4 | |
| − | 30;2003;"Something's Gotta Give";30;42;4;42;"Doug";4 | + | :37;1973;"Zatoichi's Conspiracy";37;6;4;6;"Natalie";4 |
| − | 37;1973;"Zatoichi's Conspiracy";37;6;4;6;"Natalie";4 | + | :37;1973;"Zatoichi's Conspiracy";37;42;5;42;"Doug";5 |
| − | 37;1973;"Zatoichi's Conspiracy";37;42;5;42;"Doug";5 | + | :113;2000;"Bruce Lee: A Warrior's Journey";113;6;2;6;"Natalie";2 |
| − | 113;2000;"Bruce Lee: A Warrior's Journey";113;6;2;6;"Natalie";2 | + | :113;2000;"Bruce Lee: A Warrior's Journey";113;42;5;42;"Doug";5 |
| − | 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5;42;"Doug";5 | + | :209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5;6;"Natalie";5 |
| − | 209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5;6;"Natalie";5 | + | :2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5;6;"Natalie";5 |
| − | 2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5;6;"Natalie";5 | + | :2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1;6;"Natalie";1 |
| − | 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1;6;"Natalie";1 | + | :4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3;8;"Boomer";3 |
| − | 4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3;8;"Boomer";3 | + | :4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5;42;"Doug";5 |
| − | 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5;42;"Doug";5 | + | :6762;1993;"Hello Kitty's Paradise";6762;42;3;42;"Doug";3 |
| − | 6762;1993;"Hello Kitty's Paradise";6762;42;3;42;"Doug";3 | + | :6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4;42;"Doug";4 |
| − | 6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4;42;"Doug";4 | + | :6908;1989;"Star Trek V: The Final Frontier";6908;6;2;6;"Natalie";2 |
| − | 6908;1989;"Star Trek V: The Final Frontier";6908;6;2;6;"Natalie";2 | + | :8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1;8;"Boomer";1 |
| − | 8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1;8;"Boomer";1 | + | :8809;2002;"Star Trek: Nemesis";8809;6;2;6;"Natalie";2 |
| − | 8809;2002;"Star Trek: Nemesis";8809;6;2;6;"Natalie";2 | + | :9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5;8;"Boomer";5 |
| − | 9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5;8;"Boomer";5 | + | :10176;1989;"Weekend at Bernie's";10176;42;4;42;"Doug";4 |
| − | 10176;1989;"Weekend at Bernie's";10176;42;4;42;"Doug";4 | + | :10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2;8;"Boomer";2 |
| − | 10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2;8;"Boomer";2 | + | :12513;1998;"Star Trek: Insurrection";12513;8;1;8;"Boomer";1 |
| − | 12513;1998;"Star Trek: Insurrection";12513;8;1;8;"Boomer";1 | + | :13847;2001;"Donnie Darko: Director's Cut";13847;42;5;42;"Doug";5 |
| − | 13847;2001;"Donnie Darko: Director's Cut";13847;42;5;42;"Doug";5 | + | :15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3;42;"Doug";3 |
| − | 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3;42;"Doug";3 | + | :15532;1999;"She's All That";15532;42;1;42;"Doug";1 |
| − | 15532;1999;"She's All That";15532;42;1;42;"Doug";1 | + | :15532;1999;"She's All That";15532;8;3;8;"Boomer";3 |
| − | 15532;1999;"She's All That";15532;8;3;8;"Boomer";3 | + | :15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;2;8;"Boomer";2 |
| − | 15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;2;8;"Boomer";2 | + | |
| − | + | ||
#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 rating, count (*) from movie inner join rating on (rating.movie = movie.id) where member = 42 group by rating order by rating | #*select rating, count (*) from movie inner join rating on (rating.movie = movie.id) where member = 42 group by rating order by rating | ||
| − | + | :Rating; Count | |
| − | Rating; Count | + | :1;1 |
| − | 1;1 | + | :3;2 |
| − | 3;2 | + | :4;3 |
| − | 4;3 | + | :5;4 |
| − | 5;4 | + | |
| − | + | ||
#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 ( | + | #*select avg(rating) from movie inner join rating on (rating.movie=movie.id) where title ~'^Star' |
| − | + | :2.6000000000000000 | |
#Form your own query involving movies, ratings, and members, then | #Form your own query involving movies, ratings, and members, then | ||
#*State your query in plain English (like the queries above). | #*State your query in plain English (like the queries above). | ||
#*Provide the SQL query "translation" of your plain English query. | #*Provide the SQL query "translation" of your plain English query. | ||
#*Supply the answer to your query, according to the sample movie database. | #*Supply the answer to your query, according to the sample movie database. | ||
| − | + | :Which movies that are in alphabetical order did member 42 rate 3 or less? | |
| − | + | :select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating <= 3 order by title | |
| − | select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating <= 3 order by title | + | :6762;1993;"Hello Kitty's Paradise";6762;42;3 |
| − | 6762;1993;"Hello Kitty's Paradise";6762;42;3 | + | :15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3 |
| − | 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3 | + | :15532;1999;"She's All That";15532;42;1 |
| − | 15532;1999;"She's All That";15532;42;1 | + | |
| − | + | ||
{{HDelgadi}} | {{HDelgadi}} | ||
[[User:HDelgadi|HDelgadi]] ([[User talk:HDelgadi|talk]]) 00:06, 4 October 2013 (PDT) | [[User:HDelgadi|HDelgadi]] ([[User talk:HDelgadi|talk]]) 00:06, 4 October 2013 (PDT) | ||
Latest revision as of 05:43, 8 October 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?
- select * from movie where year >= 1980 and year <1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
- 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 ~ '
- 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"
- 257;1973;"Charlotte's Web"
- 285;1997;"The Devil's Own"
- 299;2001;"Bridget Jones's Diary"
- 324;1994;"Mother's Boys"
- 387;1984;"Bizet's Carmen"
- 392;1993;"Danielle Steel's Heartbeat"
- 421;2003;"Agatha Christie's Poirot: Sad Cypress"
- 426;2001;"Recess: School's Out"
- 428;2004;"Barney: Barney's Colorful World: Live"
- 434;2004;"Extreme Engineering: Tokyo's Sky City"
- 450;2000;"Isaac Stern: Life's Virtuoso"
- 476;2004;"VeggieTales Classics: Where's God When I'm Scared?"
- 484;1992;"Danielle Steel's Jewels"
- 485;1967;"The Avengers '67"
- 494;1983;"Monty Python's The Meaning of Life: Special Edition"
- 497;2003;"Broadway's Lost Treasures"
- 506;1976;"Blank Generation / Dancin' Barefoot"
- 510;2004;"Blue's Clues: Blue's Room: It's Hug Day"
- 520;2004;"E's Otherwise"
- How many movies have titles that begin with the word “Star”?
- select count(*) from movie where title ~ '^Star '
- 61 Movies that begin with "Star"
- 138;1995;"Star Trek: Voyager: Season 1"
- 209;1996;"Star Trek: Deep Space Nine: Season 5"
- 337;1983;"Star 80"
- 345;1998;"Star Trek: Voyager: Season 5"
- 560;2003;"Star Trek: Enterprise: Season 3"
- 752;1993;"Star Trek: The Next Generation: Season 7"
- 822;2001;"Star Trek: Enterprise: Season 1"
- 1902;1979;"Star Trek: The Motion Picture"
- 2032;2004;"Star Wars Animated Adventures: Ewoks"
- 2040;1991;"Star Trek: The Next Generation: Season 5"
- 2094;2001;"Star Ocean EX"
- 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor"
- 3523;1992;"Star Trek: The Next Generation: Season 6"
- 4428;1979;"Star Trek: The Motion Picture: Bonus Material"
- 4508;1966;"Star Trek: The Original Series: Vols. 1-15"
- 4979;2002;"Star Trek: Enterprise: Season 2"
- 5091;1979;"Star Blazers: Series 3: The Bolar Wars"
- 5326;1990;"Star Trek: The Next Generation: Season 4"
- 5408;1991;"Star Trek VI: The Undiscovered Country: Bonus Material"
- 5582;1980;"Star Wars: Episode V: The Empire Strikes Back"
- 5656;1986;"Star Trek IV: The Voyage Home"
- 5705;2002;"Star Wars: Episode II: Attack of the Clones: Bonus Material"
- 6655;1994;"Star Trek: Deep Space Nine: Season 3"
- 6908;1989;"Star Trek V: The Final Frontier"
- 7145;1984;"Star Trek III: The Search for Spock"
- 7639;2004;"Star Wars Trilogy: Bonus Material"
- 7865;1999;"Star Trek: Voyager: Season 6"
- 7984;1993;"Star Trek: Deep Space Nine: Season 1"
- 7985;1968;"Star Trek: The Original Series: Vols. 29-40"
- 8085;1979;"Star Blazers: Series 1: The Quest for Iscandar"
- 8292;2004;"Star Wars: Clone Wars: Vol. 1"
- 8420;2004;"Star Wars Animated Adventures: Droids"
- 8687;2002;"Star Wars: Episode II: Attack of the Clones"
- 8809;2002;"Star Trek: Nemesis"
- 9628;1983;"Star Wars: Episode VI: Return of the Jedi"
- 9886;1999;"Star Wars: Episode I: The Phantom Menace"
- 10027;1982;"Star Trek II: The Wrath of Khan"
- 10141;2000;"Star Trek: Voyager: Season 7"
- 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”?
- select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5 order by title
- 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.
- select *, rating from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)
- 30;2003;"Something's Gotta Give";30;42;4;42;"Doug";4
- 37;1973;"Zatoichi's Conspiracy";37;6;4;6;"Natalie";4
- 37;1973;"Zatoichi's Conspiracy";37;42;5;42;"Doug";5
- 113;2000;"Bruce Lee: A Warrior's Journey";113;6;2;6;"Natalie";2
- 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5;42;"Doug";5
- 209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5;6;"Natalie";5
- 2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5;6;"Natalie";5
- 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1;6;"Natalie";1
- 4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3;8;"Boomer";3
- 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5;42;"Doug";5
- 6762;1993;"Hello Kitty's Paradise";6762;42;3;42;"Doug";3
- 6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4;42;"Doug";4
- 6908;1989;"Star Trek V: The Final Frontier";6908;6;2;6;"Natalie";2
- 8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1;8;"Boomer";1
- 8809;2002;"Star Trek: Nemesis";8809;6;2;6;"Natalie";2
- 9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5;8;"Boomer";5
- 10176;1989;"Weekend at Bernie's";10176;42;4;42;"Doug";4
- 10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2;8;"Boomer";2
- 12513;1998;"Star Trek: Insurrection";12513;8;1;8;"Boomer";1
- 13847;2001;"Donnie Darko: Director's Cut";13847;42;5;42;"Doug";5
- 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3;42;"Doug";3
- 15532;1999;"She's All That";15532;42;1;42;"Doug";1
- 15532;1999;"She's All That";15532;8;3;8;"Boomer";3
- 15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;2;8;"Boomer";2
- How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
- select rating, count (*) from movie inner join rating on (rating.movie = movie.id) where member = 42 group by rating order by rating
- Rating; Count
- 1;1
- 3;2
- 4;3
- 5;4
- 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.6000000000000000
- 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.
- Which movies that are in alphabetical order did member 42 rate 3 or less?
- select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating <= 3 order by title
- 6762;1993;"Hello Kitty's Paradise";6762;42;3
- 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3
- 15532;1999;"She's All That";15532;42;1
- Link: [Protegen]
HDelgadi Week 6
