SQLZOOのMore JOIN operationsを解いてみた

さいきんSQLを勉強していまして、Gunosyの「非エンジニアが最速でSQLをマスターする方法」に書いてある『10年戦えるデータ分析入門』→SQLZOO (https://sqlzoo.net/wiki/SQL_Tutorial) という順番で学習を進めています。

　SQLZOOは出力が合っているのか違っているのかは教えてくれるのですが、どんなSQL文を書けば正解できるのか教えてくれません。すでに日本語でSQLZOOの解答を公開してくださっている方もおられますが、私も自分の勉強のアウトプットとして、解答例を公開したいと思います。

　SQLZOOは問題文が英語なので、あまり自信はありませんが問題文の和訳も用意しました。意訳なので正確さに欠けるかもしれませんが……

• SQLZOOのJOINの解答例はこちら　→　https://rnsr0371.boy.jp/2020/12/19/sqlzoojoin/

1962 movies

yrが1962の映画を列挙してください。idとタイトルを示すこと。

SELECT
    id
    , title
FROM movie
WHERE yr = 1962;

When was Citizen Kane released?

Citizen Kaneの公開年を示してください。

SELECT
    yr
FROM movie
WHERE title = 'Citizen Kane’;

Star Trek movies

すべてのスタートレックの映画を列挙してください（これらの映画はすべてタイトルに Star Trekという単語が含まれています）。id, タイトル, 公開年を含めること。順番は公開年でソートすること。

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

id for actor Glenn Close

Glenn Closeのidはいくつですか？

SELECT
    id
FROM actor
WHERE name = 'Glenn Close';

id for Casablanca

Casablanca という映画のidはいくつですか？

SELECT
    id
FROM movie
WHERE title = 'Casablanca’;

Cast list for Casablanca

Casablancaの俳優リスト（俳優の名前のリスト）を作ってください。movieid=11768を使うか、以前の問題から得た値を使ってください。

SELECT
    actor.name
FROM movie
    JOIN casting ON movie.id = casting.movieid
    JOIN actor ON casting.actorid = actor.id
WHERE movie.id = 11768;

Alien cast list

Alienという映画の俳優リストを作ってください。

SELECT
    actor.name
FROM movie
    JOIN casting ON movie.id = casting.movieid
    JOIN actor ON casting.actorid = actor.id
WHERE movie.title = 'Alien’;

Harrison Ford movies

Harrison Fordが出演している映画を列挙してください。

SELECT
    movie.title
FROM movie
    JOIN casting ON movie.id = casting.movieid
    JOIN actor ON casting.actorid = actor.id
WHERE actor.name = 'Harrison Ford’;

Harrison Ford as a supporting actor

Harrison Fordがstarring role以外で登場する映画を列挙してください。（メモ：castingのordフィールドは俳優の地位を教えてくれます。もしord = 1ならその俳優はstarring roleです）

SELECT
    movie.title
FROM movie
    JOIN casting ON movie.id = casting.movieid
    JOIN actor ON casting.actorid = actor.id
WHERE actor.name = 'Harrison Ford'
    AND casting.ord <> 1;

Lead actors in 1962 movies

1962年のすべての映画について、映画名とleading star(*注：ord = 1のキャスト)を一緒に列挙してください。

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

Busy years for Rock Hudson

Rock Hudsonが最も忙しかった年はいつでしょうか。彼が3つ以上映画を作ったすべての年について、年・各年に作った映画の数を示してください。

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

Lead actor in Julie Andrews movies

Julie Andrewsが出演したすべての映画の、映画のタイトルと主演俳優を列挙してください。

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

Actors with 15 leading roles

13

アルファベッド順で、主演した回数が15回以上の俳優のリストを作ってください。

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

14

1978年に公開された映画のリストを作ってください。出演した俳優の数でソートし、その上でタイトルでソートしてください。

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

15

Art Garfunkelと共演したすべての人を列挙してください。

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