Difference between revisions of "Laurmagee: Week 6"

From LMU BioDB 2013
Jump to: navigation, search
(Added Answers and Formatting)
(Fixed Formatting)
Line 6: Line 6:
 
#*#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]'
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 ~ ''''
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”?

Revision as of 17:57, 26 September 2013

  1. What movies were released before 1915?
    • select * from movie where year < 1915
      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]'
      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 ~ '
      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
  1. 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
    • 113;2000;"Bruce Lee: A Warrior's Journey";113;42;5

37;1973;"Zatoichi's Conspiracy";37;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


  1. 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.
  1. How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?


  1. What is the average rating of movies that begin with the word “Star”?
  1. 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.
Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=Laurmagee:_Week_6&oldid=2174"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox