Difference between revisions of "Laurmagee: Week 6"

From LMU BioDB 2013
Jump to: navigation, search
(Added Answers and Formatting)
(Fixed Category)
 
(5 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
−
#*
+
#*movie id; year; title
 
#*#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 > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
 
#*select * from movie where year > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
−
#*
+
#*movie id; year; title
 
#*#217;1988;"Cherry 2000"
 
#*#217;1988;"Cherry 2000"
 
#*#1163;1984;"2010: The Year We Make Contact"
 
#*#1163;1984;"2010: The Year We Make Contact"
Line 23: Line 22:
 
#*#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 ~ ''''
 
#*select * from movie where title ~ ''''
−
#*
+
#*movie id; year; 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"
Line 47: Line 45:
 
#*#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"
−
 
 
# 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'
 
#*select count(*) from movie where title ~ '^Star'
−
#*#88
+
#**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!)
 
# 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 count(*) from movie where year > 1939 and year < 1950
 
#*select count(*) from movie where year > 1939 and year < 1950
−
#*#359
+
#**359
 
#*select count(*) from movie where year = 1940
 
#*select count(*) from movie where year = 1940
−
#*#34
+
#**34
 
#*select count(*) from movie where year = 1941
 
#*select count(*) from movie where year = 1941
−
#*#33
+
#**33
 
#*select count(*) from movie where year = 1942
 
#*select count(*) from movie where year = 1942
−
#*#33
+
#**33
 
#*select count(*) from movie where year = 1943
 
#*select count(*) from movie where year = 1943
−
#*#33
+
#**33
 
#*select count(*) from movie where year = 1944
 
#*select count(*) from movie where year = 1944
−
#*#36
+
#**36
 
#*select count(*) from movie where year = 1945
 
#*select count(*) from movie where year = 1945
−
#*#37
+
#**37
 
#*select count(*) from movie where year = 1946
 
#*select count(*) from movie where year = 1946
−
#*#39
+
#**39
 
#*select count(*) from movie where year = 1947
 
#*select count(*) from movie where year = 1947
−
#*#37
+
#**37
 
#*select count(*) from movie where year = 1948
 
#*select count(*) from movie where year = 1948
−
#*#36
+
#**36
 
#*select count(*) from movie where year = 1949
 
#*select count(*) from movie where year = 1949
−
#*#41
+
#**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
+
#*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
+
#*movie id; year; title; movie id; member id; rating
−
37;1973;"Zatoichi's Conspiracy";37;42;5
+
#*#113;2000;"Bruce Lee: A Warrior's Journey";113;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
+
#*#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.
 
# 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 * from movie inner join rating on (rating.movie = movie.id)
−
#*
+
#*movie id; year; title; movie id; member id; rating
−
 
+
#*#30;2003;"Something's Gotta Give";30;42;4
 +
#*#37;1973;"Zatoichi's Conspiracy";37;6;4
 +
#*#37;1973;"Zatoichi's Conspiracy";37;42;5
 +
#*#113;2000;"Bruce Lee: A Warrior's Journey";113;6;2
 +
#*#113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
 +
#*#209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5
 +
#*#2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5
 +
#*#2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1
 +
#*#4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3
 +
#*#4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
 +
#*#6762;1993;"Hello Kitty's Paradise";6762;42;3
 +
#*#6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4
 +
#*#6908;1989;"Star Trek V: The Final Frontier";6908;6;2
 +
#*#8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1
 +
#*#8809;2002;"Star Trek: Nemesis";8809;6;2
 +
#*#9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5
 +
#*#10176;1989;"Weekend at Bernie's";10176;42;4
 +
#*#10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2
 +
#*#12513;1998;"Star Trek: Insurrection";12513;8;1
 +
#*#13847;2001;"Donnie Darko: Director's Cut";13847;42;5
 +
#*#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;8;3
 +
#*#15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;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 count(*) from rating where member = 42
−
#*
+
#**10
−
 
+
−
 
+
 
# 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 (rating.movie = movie.id) where title ~ '^Star'
−
#*
+
#**2.6
−
 
+
# Form your own query involving movies, ratings, and members
−
# Form your own query involving movies, ratings, and members, then
+
#* How many movies has member number 8 rated as a 2?
−
#* State your query in plain English (like the queries above).
+
#*select * from movie inner join rating on (rating.movie = movie.id) where member = 8 and rating = 2
−
#* Provide the SQL query "translation" of your plain English query.
+
#*2
−
#* Supply the answer to your query, according to the sample movie database.
+
[[Category:Journal Entry]]

Latest revision as of 18:39, 26 September 2013

  1. What movies were released before 1915?
    • select * from movie where year < 1915
    • movie id; year; title
      1. 4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
      2. 7654;1896;"Lumiere Brothers' First Films"
      3. 9103;1914;"Tillie's Punctured Romance"
      4. 10898;1914;"Cabiria"
  2. What movies from the 1980s had 4-digit numbers in their titles?
    • select * from movie where year > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
    • movie id; year; title
      1. 217;1988;"Cherry 2000"
      2. 1163;1984;"2010: The Year We Make Contact"
      3. 1399;1984;"1984"
      4. 1480;1988;"Cold Summer of 1953"
      5. 4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985"
      6. 5482;1989;"Gundam 0080: War in the Pocket"
      7. 5890;1985;"Transylvania 6-5000"
      8. 7725;1984;"Hong Kong 1941"
      9. 15128;1988;"1969"
      10. 15269;1989;"Mystery Science Theater 3000: The Crawling Hand"
      11. 15390;1987;"Eric Clapton & Friends: Live 1986"
      12. 16985;1982;"The 2000 Year Old Man"
      13. 17007;1987;"Teenage Mutant Ninja Turtles (1980s)"
  3. What movies have apostrophes in their titles?
    • select * from movie where title ~ '
    • movie id; year; title
      1. 4;1994;"Paula Abdul's Get Up & Dance"
      2. 9;1991;"Class of Nuke 'Em High 2"
      3. 19;2000;"By Dawn's Early Light"
      4. 23;2001;"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
      5. 27;1962;"Sesame Street: Elmo's World: The Street We Live On"
      6. 30;2003;"Something's Gotta Give"
      7. 32;2004;"ABC Primetime: Mel Gibson's The Passion of the Christ"
      8. 34;2003;"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
      9. 37;1973;"Zatoichi's Conspiracy"
      10. 54;1952;"We're Not Married"
      11. 62;1991;"Ken Burns' America: Empire of the Air"
      12. 92;2002;"ECW: Cyberslam '99"
      13. 113;2000;"Bruce Lee: A Warrior's Journey"
      14. 173;1968;"The Devil's Brigade"
      15. 202;2001;"Ruby's Bucket of Blood"
      16. 215;1998;"That '70s Show: Season 1"
      17. 223;2003;"Chappelle's Show: Season 1"
      18. 233;1971;"Chato's Land"
      19. 248;2001;"Michael Moore's The Awful Truth: Season 2"
      20. 251;2000;"Midsomer Murders: Strangler's Wood"
  4. How many movies have titles that begin with the word “Star”?
    • select count(*) from movie where title ~ '^Star'
      • 88
  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 count(*) from movie where year > 1939 and year < 1950
      • 359
    • select count(*) from movie where year = 1940
      • 34
    • select count(*) from movie where year = 1941
      • 33
    • select count(*) from movie where year = 1942
      • 33
    • select count(*) from movie where year = 1943
      • 33
    • select count(*) from movie where year = 1944
      • 36
    • select count(*) from movie where year = 1945
      • 37
    • select count(*) from movie where year = 1946
      • 39
    • select count(*) from movie where year = 1947
      • 37
    • select count(*) from movie where year = 1948
      • 36
    • select count(*) from movie where year = 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
    • movie id; year; title; movie id; member id; rating
      1. 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
      2. 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
      3. 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
      4. 37;1973;"Zatoichi's Conspiracy";37;42;5
  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 * from movie inner join rating on (rating.movie = movie.id)
    • movie id; year; title; movie id; member id; rating
      1. 30;2003;"Something's Gotta Give";30;42;4
      2. 37;1973;"Zatoichi's Conspiracy";37;6;4
      3. 37;1973;"Zatoichi's Conspiracy";37;42;5
      4. 113;2000;"Bruce Lee: A Warrior's Journey";113;6;2
      5. 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
      6. 209;1996;"Star Trek: Deep Space Nine: Season 5";209;6;5
      7. 2040;1991;"Star Trek: The Next Generation: Season 5";2040;6;5
      8. 2610;1985;"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";2610;6;1
      9. 4006;2003;"Sesame Street: Cookie Monster's Best Bites";4006;8;3
      10. 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
      11. 6762;1993;"Hello Kitty's Paradise";6762;42;3
      12. 6853;1991;"Sesame Street: Elmo's Sing-Along Guessing Game";6853;42;4
      13. 6908;1989;"Star Trek V: The Final Frontier";6908;6;2
      14. 8687;2002;"Star Wars: Episode II: Attack of the Clones";8687;8;1
      15. 8809;2002;"Star Trek: Nemesis";8809;6;2
      16. 9628;1983;"Star Wars: Episode VI: Return of the Jedi";9628;8;5
      17. 10176;1989;"Weekend at Bernie's";10176;42;4
      18. 10877;1999;"Star Wars: Episode I: The Phantom Menace: Bonus Material";10877;8;2
      19. 12513;1998;"Star Trek: Insurrection";12513;8;1
      20. 13847;2001;"Donnie Darko: Director's Cut";13847;42;5
      21. 15127;2002;"Pokemon Master Quest: Collector's Box: Quest 1";15127;42;3
      22. 15532;1999;"She's All That";15532;42;1
      23. 15532;1999;"She's All That";15532;8;3
      24. 15923;1989;"Star Trek V: The Final Frontier: Bonus Material";15923;8;2
  8. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?
    • select count(*) from rating where member = 42
      • 10
  9. 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.6
  10. Form your own query involving movies, ratings, and members
    • How many movies has member number 8 rated as a 2?
    • select * from movie inner join rating on (rating.movie = movie.id) where member = 8 and rating = 2
    • 2
Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=Laurmagee:_Week_6&oldid=2213"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox