概要

SQL ZooのMore Joinセクションの難しめの問題の結果のみをメモ。

時間のあるときに他の人の回答と比較してみたいと思う。

10. 1962映画の主演者

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

11. ジョン＝トラボルタが多忙の年

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. ジュリー＝アンドリューズ出演映画

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. 主演30本の役者達

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

14. 1978年の映画

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

15. アート＝ガーファンクルと一緒に

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