Stephen Louie Week 6

Movies from Text File to Tables

What movies were released before 1915?

Query

select * from movie where year < 1915

Output

D.W. Griffith: Years of Discovery 1909-1913
Lumiere Brothers' First Films
Tillie's Punctured Romance
Cabiria

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

Query

select * from movie where year >= 1980 and year < 1990 and title ~ '[0123456789][0123456789][0123456789][0123456789]'

Output

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)

What movies have apostrophes in their titles?

Query

select * from movie where title ~ '

Output

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

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

Query

select count(*) from movie where title ~ '^Star%'

Output

61

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!)

Query

select count(*) from movie where year >= 1940 and year < 1950 group by year order by year

Output

*1940:34
*1941:33
*1942:33
*1943:33
*1944:36
*1945:37
*1946:39
*1947:37
*1948:36
*1949:41

Which movies, in alphabetical order, did member number 42 rate as a “5”?

Query

select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5

Output

Bruce Lee: A Warrior's Journey
Donnie Darko: Director's Cut
The Legend of Zelda: Ganon's Evil Tower
Zatoichi's Conspiracy

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.

Query

select title, rating, name from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member)

Output

Star Trek: Deep Space Nine: Season 5";5;"Natalie"
Star Trek: The Next Generation: Season 5";5;"Natalie"
Star Trek V: The Final Frontier";2;"Natalie"
Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
Star Trek: Nemesis";2;"Natalie"
Zatoichi's Conspiracy";4;"Natalie"
Bruce Lee: A Warrior's Journey";2;"Natalie"
Star Wars: Episode II: Attack of the Clones";1;"Boomer"
Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"

How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?

Query select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating < 6 order by rating Output

1:2
2:0
3:6
4:9
5:12

What is the average rating of movies that begin with the word “Star”?

Query

select avg(rating) from movie inner join rating on (movie.id = rating.movie) where title ~ 'Star%'

Output

0

What movies did member number 8 rate as a 5?

Query

select * from movie inner join rating on (rating.movie = movie.id) where member = 8 and rating =5 order by rating

Output

Star Wars: Episode VI: Return of the Jedi

Slouie (talk) 23:57, 3 October 2013 (PDT)

Journal Entry