Taur.vil Week 6

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