Difference between revisions of "Kevinmcgee Week 6"

From LMU BioDB 2013
Jump to: navigation, search
(Added SQL answers)
Line 21: Line 21:
 
#*1982;"The 2000 Year Old Man"
 
#*1982;"The 2000 Year Old Man"
 
#*1987;"Teenage Mutant Ninja Turtles (1980s)"
 
#*1987;"Teenage Mutant Ninja Turtles (1980s)"
−
 
+
#select * from movie where title ~ ''''
−
 
+
#select count (*) from movie where title ~ '^Star '
−
4select count (*) from movie where title ~ '^Star '
+
#*61
−
*61
+
#select count (*) from movie where year > 1939 and year < 1950 group by year order by year
−
 
+
#*34
−
6 select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5  
+
#*33
−
*113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
+
#*33
−
37;1973;"Zatoichi's Conspiracy";37;42;5
+
#*36
−
4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
+
#*37
−
13847;2001;"Donnie Darko: Director's Cut";13847;42;5
+
#*39
 +
#*37
 +
#*36
 +
#*41
 +
#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
 +
#select title, rating, name from movie inner join rating on (rating.movie = movie.id) inner join member on (rating.member = member.id)
 +
#*"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"
 +
#select rating, count (*) from rating where member = 42 group by rating order by rating
 +
#*1;1
 +
#*3;2
 +
#*4;3
 +
#*5;4
 +
#select avg(year) from movie where title ~ '^Star'
 +
#*1991
 +
#

Revision as of 18:26, 3 October 2013

Movies from Text Titles to Tables

  1. select * from movie where year < 1915
    • "D.W. Griffith: Years of Discovery 1909-1913"
    • "Lumiere Brothers' First Films"
    • "Tillie's Punctured Romance"
    • "Cabiria"
  2. select * from movie where year >1979 and year <1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'
    • 1988;"Cherry 2000"
    • 1984;"2010: The Year We Make Contact"
    • 1984;"1984"
    • 1988;"Cold Summer of 1953"
    • 1985;"Butthole Surfers: Blind Eye See All: Live 1985"
    • 1989;"Gundam 0080: War in the Pocket"
    • 1985;"Transylvania 6-5000"
    • 1984;"Hong Kong 1941"
    • 1988;"1969"
    • 1989;"Mystery Science Theater 3000: The Crawling Hand"
    • 1987;"Eric Clapton & Friends: Live 1986"
    • 1982;"The 2000 Year Old Man"
    • 1987;"Teenage Mutant Ninja Turtles (1980s)"
  3. select * from movie where title ~ '
  4. select count (*) from movie where title ~ '^Star '
    • 61
  5. select count (*) from movie where year > 1939 and year < 1950 group by year order by year
    • 34
    • 33
    • 33
    • 36
    • 37
    • 39
    • 37
    • 36
    • 41
  6. 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
  7. select title, rating, name from movie inner join rating on (rating.movie = movie.id) inner join member on (rating.member = member.id)
    • "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. select rating, count (*) from rating where member = 42 group by rating order by rating
    • 1;1
    • 3;2
    • 4;3
    • 5;4
  9. select avg(year) from movie where title ~ '^Star'
    • 1991
Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=Kevinmcgee_Week_6&oldid=2519"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox