Individual hw week 6

1.) What movies were released before 1915? select * from movie where year < 1915

1. D.W. Griffith: Years of Discovery 1909-1913
2. Lumiere Brothers' First Films
3. Tillie's Punctured Romance
4. 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]'

1. 1988;"Cherry 2000"
2. 1984;"2010: The Year We Make Contact"
3. 1984;"1984"
4. 1988;"Cold Summer of 1953"
5. 1985;"Butthole Surfers: Blind Eye See All: Live 1985"
6. 1989;"Gundam 0080: War in the Pocket"
7. 1985;"Transylvania 6-5000"
8. 1984;"Hong Kong 1941"
9. 1988;"1969"
10. 1989;"Mystery Science Theater 3000: The Crawling Hand"
11. 1987;"Eric Clapton & Friends: Live 1986"
12. 1982;"The 2000 Year Old Man"
13. 1987;"Teenage Mutant Ninja Turtles (1980s)"

3.)What movies have apostrophes in their titles? select * from movie where title ~ '

4.)How many movies have titles that begin with the word “Star”? select count (*) from movie where title ~ '^Star'

• 61

5.)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!) select movie where year > 1939 and year < 1950 group by year order by year

1. 34
2. 33
3. 33
4. 36
5. 37
6. 39
7. 37
8. 36
9. 41

6.)Which movies, in alphabetical order, did member number 42 rate as a “5”? select * from movie inner join rating on (rating.movie = movie.id) where member = 42 and rating = 5

1. 13;2000;"Bruce Lee: A Warrior's Journey";113;42;5
2. 37;1973;"Zatoichi's Conspiracy";37;42;5
3. 4765;1998;"The Legend of Zelda: Ganon's Evil Tower";4765;42;5
4. 13847;2001;"Donnie Darko: Director's Cut";13847;42;5

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 (rating.movie = movie.id) inner join member on (rating.member = member.id)

1. "Something's Gotta Give";4;"Doug"
2. "Zatoichi's Conspiracy";4;"Natalie"
3. "Zatoichi's Conspiracy";5;"Doug"
4. "Bruce Lee: A Warrior's Journey";2;"Natalie"
5. "Bruce Lee: A Warrior's Journey";5;"Doug"
6. "Star Trek: Deep Space Nine: Season 5";5;"Natalie"
7. "Star Trek: The Next Generation: Season 5";5;"Natalie"
8. "Star Wars: Ewok Adventures: Caravan of Courage / The Battle for Endor";1;"Natalie"
9. "Sesame Street: Cookie Monster's Best Bites";3;"Boomer"
10. "The Legend of Zelda: Ganon's Evil Tower";5;"Doug"
11. "Hello Kitty's Paradise";3;"Doug"
12. "Sesame Street: Elmo's Sing-Along Guessing Game";4;"Doug"
13. "Star Trek V: The Final Frontier";2;"Natalie"
14. "Star Wars: Episode II: Attack of the Clones";1;"Boomer"
15. "Star Trek: Nemesis";2;"Natalie"
16. "Star Wars: Episode VI: Return of the Jedi";5;"Boomer"
17. "Weekend at Bernie's";4;"Doug"
18. "Star Wars: Episode I: The Phantom Menace: Bonus Material";2;"Boomer"
19. "Star Trek: Insurrection";1;"Boomer"
20. "Donnie Darko: Director's Cut";5;"Doug"

8.)How many movies has member number 42 rated as 1, 2, 3, 4, and 5, respectively? select rating, count (*)from rating where member = 42 group by rating order by rating

1. 1;1
2. 3;2
3. 4;3
4. 5;4

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'

• 2.6

10.)What is the average year for movies that include alien in the title and what were their ratings? select avg(year) movie inner join rating on (rating.movie = movie.id) where title ~'alien'