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'.
SELECT yr FROM movie WHERE title='Citizen Kane'
|
|
| 1c |
Show the title and score for the Police Academy
films. [The films have a title that is LIKE 'Police Academy%]
SELECT title, score FROM movie
WHERE title LIKE 'Police Academy%'
|
|
| 1d |
Why did they make seven of them?
Police Academy was the worst comedy I'd ever seen.
Then I saw Police Academy 2...
|
|
| 1e |
List the titles and scores of the films containing
the word 'Dog'
SELECT title, score FROM movie
WHERE title LIKE '%Dog%'
|
|
Looking at the id field.
| 2a |
What are the titles of the films with id 1, 2, 3
SELECT title FROM movie WHERE id IN (1,2,3)
|
|
| 2b |
What id number does 'Glenn Close' have?
SELECT id FROM actor
WHERE name= 'Glenn Close'
|
|
| 2c |
What is the id of the film 'Casablanca'
SELECT id FROM movie WHERE title='Casablanca'
|
|
Get to the point.
| 3a |
Obtain the cast list for 'Star Wars' which has id
1
SELECT name
FROM casting, actor
WHERE movieid=1
AND actorid=actor.id
|
|
| 3b |
Obtain the cast list for the film 'Alien'
SELECT name
FROM movie, casting, actor
WHERE title='Alien'
AND movieid=movie.id
AND actorid=actor.id
|
|
| 3c |
List the films in which 'Harrison Ford' has appeared
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
|
|
| 3d |
List the films where 'Harrison Ford' has appeared
- but not in the star role.
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
AND ord>1
|
|
| 3e |
List the films together with their stars for all
1962 films.
SELECT title, name
FROM movie, casting, actor
WHERE yr=1962
AND movieid=movie.id
AND actorid=actor.id
AND ord=1
|
|
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.
SELECT yr, COUNT(movie.id)
FROM movie, casting, actor
WHERE name='John Travolta'
AND movieid=movie.id
AND actorid=actor.id
GROUP BY yr
|
|
| 4b |
List the film title and the leading actor for all of
'Julie Andrews' films.
SELECT title, name
FROM movie, casting, actor
WHERE movieid=movie.id
AND actorid=actor.id
AND ord=1
AND movieid IN
(SELECT movieid FROM casting, actor
WHERE actorid=actor.id
AND name='Julie Andrews')
|
|
| 4c |
Obtain a list of actors in who have had at least 10
starring roles.
SELECT name FROM actor
WHERE id IN
(SELECT actorid
FROM casting
WHERE ord=1
GROUP BY actorid
HAVING COUNT(movieid)>10)
|
|
| 4d |
List the 1978 films by order of cast list size.
SELECT title, COUNT(actorid)
FROM casting, movie
WHERE yr=1978
AND movieid=movie.id
GROUP BY title
ORDER BY 2 DESC
|
|
| 4e |
List all the people who have worked with 'Art
Garfunkel'.
SELECT name
FROM actor, casting
WHERE actorid=actor.id
AND movieid IN (
SELECT movieid FROM casting, actor
WHERE actorid=actor.id
AND name='Art Garfunkel'
)
|
|
That is definitely enough. Students should, under no
circumstances look at the next tutorial,
concerning outer joins.