Difference between revisions of "HDelgadi Week 6"

Revision as of 02:13, 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