Quick Ref.
Functions
date
number
string
Data Types
date
number
string

Movie Database

This tutorial introduces the notion of a join. The database consists of three tables movie , actor and casting .

movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)

More details about the database.

Let's go to work.

Limbering up

1a. List the films where the yr is 1962 [Show id, title]
movie(id, title, yr, score, votes, director)
Results
1b. Give year of 'Citizen Kane'.
movie(id, title, yr, score, votes, director)
Results
1c. List all of the Star Trek movies, include the id title and yr.
movie(id, title, yr, score, votes, director)
Results

Looking at the id field.

2a. What are the titles of the films with id 1, 2, 3
movie(id, title, yr, score, votes, director)
Results
2b. What id number does the actor 'Glenn Close' have?
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
2c. What is the id of the film 'Casablanca'
movie(id, title, yr, score, votes, director)
Results

Get to the point.

3a. Obtain the cast list for 'Casablanca'. Use the id value that you obtained in the previous question.
actor(id, name)
casting(movieid, actorid, ord)
Results
3b. Obtain the cast list for the film 'Alien'
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
3c. List the films in which 'Harrison Ford' has appeared
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
3d. List the films where 'Harrison Ford' has appeared - but not in the star role.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
3e. List the films together with the leading star for all 1962 films.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results

That's plenty joins for now. Students with an unhealthy interest in databases or movies may try the following harder questions; although they might be better advised to go out and get some fresh air.

4a. Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
4b. List the film title and the leading actor for all of 'Julie Andrews' films.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
4c. Obtain a list of actors in who have had at least 10 starring roles.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
4d. List the 1978 films by order of cast list size.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results
4e. List all the people who have worked with 'Art Garfunkel'.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
Results

That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.