Overview

Notes on just the answers to the harder problems in the More Join section of SQL Zoo.

I'd like to compare with other people's answers when I have time.

10. Lead Actors in 1962 Films

SELECT title, name
FROM movie
JOIN casting ON movie.id = movieid
JOIN actor   ON actor.id = actorid
WHERE ord = '1'
  AND yr = '1962'

11. John Travolta's Busy Year

SELECT yr, count(title)
FROM movie
WHERE id IN (SELECT movieid
             FROM casting
             WHERE actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
            )
GROUP BY yr
HAVING count(title) >= 2

12. Julie Andrews Films

SELECT title, name
FROM actor
  JOIN casting ON actor.id = actorid
  JOIN movie ON movieid = movie.id
WHERE ord = 1
  AND movieid IN (SELECT id
                  FROM movie
                    JOIN casting ON id = movieid
                  WHERE actorid = (SELECT id FROM actor WHERE name = 'Julie Andrews' )
                  )

13. Actors with 30 Lead Roles

SELECT name
FROM actor
  JOIN casting ON id = actorid
GROUP BY name
HAVING count(movieid) >= 30
ORDER BY name

14. 1978 Films

SELECT title, count(actorid)
FROM movie
  JOIN casting ON id = movieid
WHERE yr = '1978'
GROUP BY title
ORDER BY count(actorid) DESC, title

15. Together with Art Garfunkel

SELECT DISTINCT name
FROM casting
  JOIN actor ON actorid = id
WHERE movieid IN (SELECT movieid
                  FROM casting
                  WHERE actorid = (SELECT id FROM actor
                                   WHERE name = 'Art Garfunkel' )
                 )
AND actorid <> (SELECT id FROM actor
                 WHERE name = 'Art Garfunkel' )