HDelgadi Week 6

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