Difference between revisions of "Mpetredi Week 6"

From LMU BioDB 2013
Jump to: navigation, search
(added answer 8)
(Added remaining requirements for assignment)
 
(7 intermediate revisions by one user not shown)
Line 1: Line 1:
−
==Movies from Text File to Tables==
+
==[[user:mpetredi|'''Mitchell Petredis''']]==
 +
 
 +
===Assignment Reference===
 +
 
 +
[[Week 6]]
 +
 
 +
===Movies from Text File to Tables===
 
#select * from movie where year < 1915
 
#select * from movie where year < 1915
 
#*4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
 
#*4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
Line 5: Line 11:
 
#*9103;1914;"Tillie's Punctured Romance"
 
#*9103;1914;"Tillie's Punctured Romance"
 
#*10898;1914;"Cabiria"
 
#*10898;1914;"Cabiria"
−
#
+
#select * from movie where year > 1979 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)"
 +
# select * from movie where title ~ ''''
 +
#*1280 Total Results
 +
#**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"
 
#select count(*) from movie where title ~ '^Star '
 
#select count(*) from movie where title ~ '^Star '
 
#*61
 
#*61
−
#
+
#select year, count (*) from movie where year > 1939 and year < 1950 group by year order by year
−
#
+
#*1940;34
−
#
+
#*1941;33
−
#select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5
+
#*1942;33
−
#*113;2000;"Bruce Lee: A Warrior's Journey";113;42;5
+
#*1943;33
−
#*37;1973;"Zatoichi's Conspiracy";37;42;5
+
#*1944;36
−
#*4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
+
#*1945;37
−
#*13847;2001;"Donnie Darko: Director's Cut";13847;42;5
+
#*1946;39
 +
#*1947;37
 +
#*1948;36
 +
#*1949;41
 +
#select title from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title
 +
#*"Bruce Lee: A Warrior's Journey"
 +
#*"Donnie Darko: Director's Cut"
 +
#*"The Legend of Zelda: Ganon's Evil Tower"
 +
#*"Zatoichi's Conspiracy"
 +
#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"
 +
#*"Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
 +
#*"She's All That";1;"Doug"
 +
#*"She's All That";3;"Boomer"
 +
#*"Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"
 +
#select rating, count(*) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 group by rating order by rating
 +
#*Rating 1 = 1 movie
 +
#*Rating 2 = 0 movies
 +
#*Rating 3 = 2 movies
 +
#*Rating 4 = 3 movies
 +
#*Rating 5 = 4 movies
 +
#select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star'
 +
#*2.6
 +
#What are the best movies according to each member?
 +
#*select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where rating = 5 order by title
 +
#**"Bruce Lee: A Warrior's Journey";5;"Doug"
 +
#**"Donnie Darko: Director's Cut";5;"Doug"
 +
#**"Star Trek: Deep Space Nine: Season 5";5;"Natalie"
 +
#**"Star Trek: The Next Generation: Season 5";5;"Natalie"
 +
#**"Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
 +
#**"The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
 +
#**"Zatoichi's Conspiracy";5;"Doug"
 +
 
 +
[[Category: Individual Homework]]
 +
[[Category: Journal Entry]]

Latest revision as of 21:07, 3 October 2013

[edit] Mitchell Petredis

[edit] Assignment Reference

Week 6

[edit] Movies from Text File to Tables

  1. 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"
  2. select * from movie where year > 1979 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)"
  3. select * from movie where title ~ '
    • 1280 Total Results
      • 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"
  4. select count(*) from movie where title ~ '^Star '
    • 61
  5. select year, count (*) from movie where year > 1939 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. select title from movie inner join rating on (movie.id = rating.movie) where member = 42 and rating = 5 order by title
    • "Bruce Lee: A Warrior's Journey"
    • "Donnie Darko: Director's Cut"
    • "The Legend of Zelda: Ganon's Evil Tower"
    • "Zatoichi's Conspiracy"
  7. 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"
    • "Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
    • "She's All That";1;"Doug"
    • "She's All That";3;"Boomer"
    • "Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"
  8. select rating, count(*) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where member = 42 group by rating order by rating
    • Rating 1 = 1 movie
    • Rating 2 = 0 movies
    • Rating 3 = 2 movies
    • Rating 4 = 3 movies
    • Rating 5 = 4 movies
  9. select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star'
    • 2.6
  10. What are the best movies according to each member?
    • select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where rating = 5 order by title
      • "Bruce Lee: A Warrior's Journey";5;"Doug"
      • "Donnie Darko: Director's Cut";5;"Doug"
      • "Star Trek: Deep Space Nine: Season 5";5;"Natalie"
      • "Star Trek: The Next Generation: Season 5";5;"Natalie"
      • "Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
      • "The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
      • "Zatoichi's Conspiracy";5;"Doug"
Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=Mpetredi_Week_6&oldid=2549"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox