Difference between revisions of "Taur.vil Week 6"

From LMU BioDB 2013
Jump to: navigation, search
(Start of assignment during class period)
 
(adding a 0 counted to 8)
 
(One intermediate revision by one user not shown)
Line 1: Line 1:
 +
[[Week_6| Week 6]] Individual Journal
  
 
1) Select Movies made before 1915
 
1) Select Movies made before 1915
Line 25: Line 26:
  
 
3) Select Movies With Apostrophes in the Title
 
3) Select Movies With Apostrophes in the Title
−
:Code: select * from movie where title ~ ''''
+
:Code: select * from movie where title ~ <nowiki>''''</nowiki>
 
  --1280 Results--
 
  --1280 Results--
 
  "Paula Abdul's Get Up & Dance"
 
  "Paula Abdul's Get Up & Dance"
Line 52: Line 53:
 
  61 movies begin with the word Star
 
  61 movies begin with the word Star
  
−
5)  
+
5) How many movies, on a year by year basis, were released in the 1940s?
−
:
+
: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) Which movies, in alphabetical order, did member number 42 rate as a “5”?
 
6) Which movies, in alphabetical order, did member number 42 rate as a “5”?
Line 62: Line 74:
 
  "Zatoichi's Conspiracy"
 
  "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 who gave the rating.
 +
:Code: select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) order by title
 +
"Bruce Lee: A Warrior's Journey";5;"Doug"
 +
"Bruce Lee: A Warrior's Journey";2;"Natalie"
 +
"Donnie Darko: Director's Cut";5;"Doug"
 +
"Hello Kitty's Paradise";3;"Doug"
 +
"Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
 +
"Sesame Street: Cookie Monster's Best Bites";3;"Boomer"
 +
"Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"
 +
"She's All That";1;"Doug"
 +
"She's All That";3;"Boomer"
 +
"Something's Gotta Give";4;"Doug"
 +
"Star Trek V: The Final Frontier";2;"Natalie"
 +
"Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"
 +
"Star Trek: Deep Space Nine: Season 5";5;"Natalie"
 +
"Star Trek: Insurrection";1;"Boomer"
 +
"Star Trek: Nemesis";2;"Natalie"
 +
"Star Trek: The Next Generation: Season 5";5;"Natalie"
 +
"Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"
 +
"Star Wars: Episode II: Attack of the Clones";1;"Boomer"
 +
"Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
 +
"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
 +
"The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
 +
"Weekend at Bernie's";4;"Doug"
 +
"Zatoichi's Conspiracy";5;"Doug"
 +
"Zatoichi's Conspiracy";4;"Natalie"
 +
 +
8) How many movie has member 42 rated 1-5 respectively?
 +
:code: 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 Count: 1
 +
Rating: 2 Count: 0
 +
Rating: 3 Count: 2
 +
Rating: 4 Count: 3
 +
Rating: 5 Count: 4
 +
 +
9) What is the average rating of movies beginning with Star?
 +
:code: select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star'
 +
The average rating was 2.6
 +
 +
10) Create your own query:
 +
: Question: What was the average year and average rating of movies watched by each reviewer?
 +
: code: select name, trunc(avg(year),2), trunc(avg(rating),2) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) group by name
 +
Name: "Boomer"; Year: 1996.14; Avg. Rating: 2.43
 +
Name: "Doug"; Year: 1994.90; Avg. Rating: 3.90
 +
Name: "Natalie"; Year: 1990.86; Rating: 3.00
 +
 +
{{Taur.vil}}
 +
 +
 +
[[Category: Journal Entry]]
 +
[[Category: Individual Homework]]
  
−
Webpage: select name, rating from movie inner join rating on (movie.id = rating.movie) where title like'%''%'
+
<!--Webpage: select name, rating from movie inner join rating on (movie.id = rating.movie) where title like'%''%' -->

Latest revision as of 18:32, 3 October 2013

Week 6 Individual Journal

1) Select Movies made before 1915

Code: select title from movie where year < 1915 
"D.W. Griffith: Years of Discovery 1909-1913"
"Lumiere Brothers' First Films"
"Tillie's Punctured Romance"
"Cabiria"

2) What movies from the 1980s had 4-digit numbers in their titles?

Code: select * from movie where year > 1979 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]' 
"Cherry 2000"
"2010: The Year We Make Contact"
"1984"
"Cold Summer of 1953"
"Butthole Surfers: Blind Eye See All: Live 1985"
"Gundam 0080: War in the Pocket"
"Transylvania 6-5000"
"Hong Kong 1941"
"1969"
"Mystery Science Theater 3000: The Crawling Hand"
"Eric Clapton & Friends: Live 1986"
"The 2000 Year Old Man"
"Teenage Mutant Ninja Turtles (1980s)"


3) Select Movies With Apostrophes in the Title

Code: select * from movie where title ~ '''' 
--1280 Results--
"Paula Abdul's Get Up & Dance"
"Class of Nuke 'Em High 2"
"By Dawn's Early Light"
"Clifford: Clifford Saves the Day! / Clifford's Fluffiest Friend Cleo"
"Sesame Street: Elmo's World: The Street We Live On"
"Something's Gotta Give"
"ABC Primetime: Mel Gibson's The Passion of the Christ"
"Ashtanga Yoga: Beginner's Practice with Nicki Doane"
"Zatoichi's Conspiracy"
"We're Not Married"
"Ken Burns' America: Empire of the Air"
"ECW: Cyberslam '99"
"Bruce Lee: A Warrior's Journey"
"The Devil's Brigade"
"Ruby's Bucket of Blood"
"That '70s Show: Season 1"
"Chappelle's Show: Season 1"
"Chato's Land"
"Michael Moore's The Awful Truth: Season 2"
"Midsomer Murders: Strangler's Wood"

4) How many movies have titles that begin with the word “Star”?

Code: select count (*) from movie where title ~ '^Star ' 
61 movies begin with the word Star

5) How many movies, on a year by year basis, were released in the 1940s?

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) Which movies, in alphabetical order, did member number 42 rate as a “5”?

Code: 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) Produce a table that lists every movie that has been rated, the rating that the movie got, and the name of the member who gave the rating.

Code: select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) order by title 
"Bruce Lee: A Warrior's Journey";5;"Doug"
"Bruce Lee: A Warrior's Journey";2;"Natalie"
"Donnie Darko: Director's Cut";5;"Doug"
"Hello Kitty's Paradise";3;"Doug"
"Pokemon Master Quest: Collector's Box: Quest 1";3;"Doug"
"Sesame Street: Cookie Monster's Best Bites";3;"Boomer"
"Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"
"She's All That";1;"Doug"
"She's All That";3;"Boomer"
"Something's Gotta Give";4;"Doug"
"Star Trek V: The Final Frontier";2;"Natalie"
"Star Trek V: The Final Frontier: Bonus Material";2;"Boomer"
"Star Trek: Deep Space Nine: Season 5";5;"Natalie"
"Star Trek: Insurrection";1;"Boomer"
"Star Trek: Nemesis";2;"Natalie"
"Star Trek: The Next Generation: Season 5";5;"Natalie"
"Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"
"Star Wars: Episode II: Attack of the Clones";1;"Boomer"
"Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
"Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
"The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
"Weekend at Bernie's";4;"Doug"
"Zatoichi's Conspiracy";5;"Doug"
"Zatoichi's Conspiracy";4;"Natalie"

8) How many movie has member 42 rated 1-5 respectively?

code: 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 Count: 1
Rating: 2 Count: 0
Rating: 3 Count: 2
Rating: 4 Count: 3
Rating: 5 Count: 4

9) What is the average rating of movies beginning with Star?

code: select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ '^Star' 
The average rating was 2.6

10) Create your own query:

Question: What was the average year and average rating of movies watched by each reviewer?
code: select name, trunc(avg(year),2), trunc(avg(rating),2) from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) group by name 
Name: "Boomer"; Year: 1996.14; Avg. Rating: 2.43
Name: "Doug"; Year: 1994.90; Avg. Rating: 3.90
Name: "Natalie"; Year: 1990.86; Rating: 3.00

By Tauras Vilgalys

As part of Biological Databases


Please Remember the Harassing of Deities is Strictly Prohibited

Never Forget Samson

Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2013/index.php?title=Taur.vil_Week_6&oldid=2525"
Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox