SQLZOO答案： More JOIN operations（全部通过）

前十题都太简单了，就不给答案了
11、Which were the busiest years for ‘John Travolta’, 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='John Travolta'
GROUP BY yr
HAVING COUNT(title) > 2;

12、List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.

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

13、Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.

SELECT name FROM actor WHERE id IN(SELECT actorid FROM 
  casting WHERE ord = 1 GROUP BY actorid HAVING COUNT(*) >= 30);

14、List the films released in the year 1978 ordered by the number of actors in the cast, then by title.（给的参考答案有点问题，其实是要降序）

SELECT title, COUNT(actorid) AS num
FROM casting JOIN movie ON id = movieid
WHERE yr= 1978
GROUP BY movieid, title
ORDER BY num DESC, title;

15、List all the people who have worked with ‘Art Garfunkel’.

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