Vkuehn Week 6

Individual Journal Week 6

1. 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"

2. 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"

3. 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"

4. 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"

5. 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

6. 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"

7. 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 title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)

     "Something's Gotta Give";4;"Doug"

     "Zatoichi's Conspiracy";4;"Natalie"

     "Zatoichi's Conspiracy";5;"Doug"

     "Bruce Lee: A Warrior's Journey";2;"Natalie"

     "Bruce Lee: A Warrior's Journey";5;"Doug"

     "Star Trek: Deep Space Nine: Season 5";5;"Natalie"

     "Star Trek: The Next Generation: Season 5";5;"Natalie"

     "Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"

     "Sesame Street: Cookie Monster's Best Bites";3;"Boomer"

     "The Legend of Zelda: Ganon's Evil Tower";5;"Doug"

     "Hello Kitty's Paradise";3;"Doug"

     "Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"

     "Star Trek V: The Final Frontier";2;"Natalie"

     "Star Wars: Episode II: Attack of the Clones";1;"Boomer"

     "Star Trek: Nemesis";2;"Natalie"

     "Star Wars: Episode VI: Return of the Jedi";5;"Boomer"

     "Weekend at Bernie's";4;"Doug"

     "Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"

     "Star Trek: Insurrection";1;"Boomer"

     "Donnie Darko: Director's Cut";5;"Doug"

8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
   • select rating, count (*) from rating where member =42 group by rating order by rating

     1 was rated 1

     0 were rated 2

     2 were rated 3

     3 were rated 4

     4 were rated 5

9. What is the average rating of movies that begin with the word “Star”?
   • select avg (rating) from movie inner join rating on (movie.id = rating.movie)where title~'^Star'

     average rating is 2.6 for movies beginning with Star

10. Form your own query involving movies, ratings, and members, then
    • Which movies got a rating of 5 and who rated these movies.

      SELECT mo.title, ra.rating, me.name FROM rating ra JOIN movie mo ON mo.id = ra.movie JOIN member me ON me.id = ra.member WHERE rating = 5 ;

      "Star Trek: The Next Generation: Season 5";5;"Natalie"

      "Star Trek: Deep Space Nine: Season 5";5;"Natalie"

      "Star Wars: Episode VI: Return of the Jedi";5;"Boomer"

      "Donnie Darko: Director's Cut";5;"Doug"

      "The Legend of Zelda: Ganon's Evil Tower";5;"Doug"

      "Zatoichi's Conspiracy";5;"Doug"

      "Bruce Lee: A Warrior's Journey";5;"Doug"