Stephen Louie Week 6

Movies from Text File to Tables

What movies were released before 1915?

Query

select * from movie where year < 1915

Output

4975;1909;"D.W. Griffith: Years of Discovery 1909-1913"
7654;1896;"Lumiere Brothers' First Films"
9103;1914;"Tillie's Punctured Romance"
10898;1914;"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

217;1988;"Cherry 2000"
1163;1984;"2010: The Year We Make Contact"
1399;1984;"1984"
1480;1988;"Cold Summer of 1953"
4882;1985;"Butthole Surfers: Blind Eye See All: Live 1985"
5482;1989;"Gundam 0080: War in the Pocket"
5890;1985;"Transylvania 6-5000"
7725;1984;"Hong Kong 1941"
15128;1988;"1969"
15269;1989;"Mystery Science Theater 3000: The Crawling Hand"

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

Output

359

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

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

Output

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

Query

Output

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

Query

Output