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