# More JOIN operations

## Contents

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