More JOIN operations

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

movie

id

title

yr

director

budget

gross

actor

id

name

casting

movieid

actorid

ord

More details about the database.

Summary

1962 movies

List the films where the yr is 1962 and the budget is over 2000000 [Show id, title]

SELECT id, title
 FROM movie
 WHERE yr=1962

SELECT id, title
 FROM movie
 WHERE yr=1962
   AND budget>2000000

When was Citizen Kane released?

Give year of 'Citizen Kane'.

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

Star Trek movies

List all of the Star Trek movies, include the id, title and yr (all of these movies start with the words Star Trek in the title). Order results by year.

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

id for actor Glenn Close

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

SELECT id FROM actor
  WHERE name= 'Glenn Close'

id for Casablanca

What is the id of the 1942 film 'Casablanca'

SELECT id 
FROM movie 
WHERE title='Casablanca' and yr=1942

Get to the point

Cast list for Casablanca

Obtain the cast list for 1942's 'Casablanca'.

The cast list is the names of the actors who were in the movie.

Use movieid=11768, (or whatever value you got from the previous question)

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

Alien cast list

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

Harrison Ford movies

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

Harrison Ford as a supporting actor

List the films where 'Harrison Ford' has appeared - but not in the starring 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

Lead actors in 1962 movies

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

---

Busy years for Rock Hudson

Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE name='Doris Day'
GROUP BY yr
HAVING COUNT(title) > 1

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
where name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

Lead actor in Julie Andrews movies

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')

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

Using nested queries

Using multiple JOINS

Actors with 15 leading roles

Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

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

released in the year 1978

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC,1 ASC

with 'Art Garfunkel'

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

Clear your results

JOIN Quiz 2

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