Movie Database

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

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

Let's go to work.

Limbering up

List the films where the yr is 1962 [Show id, title]

```SELECT id, title
FROM movie
WHERE yr=1962```
```SELECT id, title
FROM movie
WHERE yr=1962```

Give year of 'Citizen Kane'.

` `
```SELECT yr
FROM movie
WHERE title='Citizen Kane'```

List all of the Star Trek movies, include the id title and yr. (All of these movies include the words Star Trek in the title.)

` `
```SELECT id,title, yr FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr```

Looking at the id field.

What are the titles of the films with id 11768, 11955, 21191

` `
```SELECT title
FROM movie
WHERE id IN (11768, 11955, 21191)```

What id number does the actor 'Glenn Close' have?

` `
```SELECT id FROM actor
WHERE name= 'Glenn Close'```

What is the id of the film 'Casablanca'

` `
```SELECT id
FROM movie
WHERE title='Casablanca'```

Get to the point

Obtain the cast list for 'Casablanca'. Use the id value that you obtained in the previous question.

` `
```SELECT name
FROM casting, actor
WHERE movieid=(SELECT id
FROM movie
WHERE title='Casablanca')
AND actorid=actor.id```

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```

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```

List the films where 'Harrison Ford' has appeared - but not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

` `
```SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
AND ord<>1```

List the films together with the leading star 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```

Harder Questions

Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

```SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)```
```SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr) AS t
)```

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')```

Obtain a list of actors in who have had at least 30 starring roles.

` `
```SELECT name
FROM casting JOIN actor
ON  actorid = actor.id
WHERE ord=1
GROUP BY name
HAVING COUNT(movieid)>=30```

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```

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

` `
```SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
JOIN casting b ON (a.movieid=b.movieid)
JOIN actor c ON (b.actorid=c.id
AND c.name='Art Garfunkel')
WHERE d.id!=c.id```

[JOIN Quiz 2]