A Gentle Introduction to


Pick an engine:

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 which have at least 5000 votes. [Show title, votes

1b Give year of 'Citizen Kane'.

1c Show the title and score for the Police Academy films. [The films have a title that is LIKE 'Police Academy%]

1d Why did they make seven of them?

1e List the titles and scores of the films containing the word 'Dog'

Looking at the id field.

2a What are the titles of the films with id 1, 2, 3

2b What id number does 'Glenn Close' have?

2c What is the id of the film 'Casablanca'

Get to the point.

3a Obtain the cast list for 'Star Wars' which has id 1

3b Obtain the cast list for the film 'Alien'

3c List the films in which 'Harrison Ford' has appeared

3d List the films where 'Harrison Ford' has appeared - but not in the star role.

3e List the films together with their stars for all 1962 films.

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.

4b List the film title and the leading actor for all of 'Julie Andrews' films.

4c Obtain a list of actors in who have had at least 10 starring roles.

4d List the 1978 films by order of cast list size.

4e List all the people who have worked with 'Art Garfunkel'.

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