Lena Week 6

1.) What movies were released before 1915?

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?

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.)What movies have apostrophes in their titles?

select * from movie where title ~''

"Dinosaur Planet"

"Isle of Man TT 2004 Review" "Character" "Paula Abdul's Get Up & Dance" "The Rise and Fall of ECW" "Sick" "8 Man" "What the #$*! Do We Know!?" "Class of Nuke 'Em High 2" "Fighter"

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

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

61 movies.

5.) How many movies, on a year by year basis, were released in the 1940s (years 1940 to 1949)?

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

There are 359 from the 1940s. 34 from 1940, 33 from 1941, 33 from 1942, 33 from 1943, 36 from 1944, 37 from 1945, 39 from 1946, 37 from 1947, 38 from 1948, and 41 from 1949.

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

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 that gave this rating.

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.)How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively?

select rating, count (*) from movie inner join rating on (movie.id = rating.movie) where member = 42 group by rating order by rating

1 movie was rated 1, 0 movies were rated 2, 2 movies were rated 3, 3 movies were rated 4, 4 movies were rated 5.

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

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

The average rating was 2.6

10.) What are the titles of all of the movies that were rated a 5 by members?

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

"Star Trek: The Next Generation: Season 5"

"Star Trek: Deep Space Nine: Season 5" "Star Wars: Episode VI: Return of the Jedi" "Donnie Darko: Director's Cut" "The Legend of Zelda: Ganon's Evil Tower" "Zatoichi's Conspiracy" "Bruce Lee: A Warrior's Journey"

Lena Hunt