More JOIN operations/ja

このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。 movie（映画） と actor（役者） と casting（出演） である。

テーブルの詳細は、以下である。

movie 映画

id

title タイトル

yr 年

director 監督

budget 予算

gross 収益

actor 役者

id

name 名前

casting

movieid

actorid

ord 出演掲載順序 1なら主演

※ SQL の表記で id は movie.id と actor.id の2種類あるので注意。

※ movie.id = actor.id のようには使われない（映画のid と 役者のid なので 参照キーと外部キーの関係にはならない）

※ movie.id = casting.movieid や actor.id = casting.actorid の関係がテーブル間にある。

映画データベースの詳細は以下のリンクから表示。 More details about the database.

サマリー

1962 映画

1962 年の映画のリストを表示（ id と title を表示）

（ヒント） 実行するだけ。文法の確認。

SELECT id, title
 FROM movie
 WHERE yr=1962

SELECT id, title
 FROM movie
 WHERE yr=1962

「市民ケーン」の上映年は？

'Citizen Kane'の年を示す。

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

スタートレック映画

スタートレック（'Star Trek'）というシリーズ映画のリストを表示（ id title yr ）。 年の順に掲載。

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

グレン・クローズのid

女優 'Glenn Close' 　の id ナンバーは何ですか？

SELECT id FROM actor
  WHERE name= 'Glenn Close'

カサブランカのid

映画 'Casablanca' カサブランカの id は何ですか？

SELECT id 
FROM movie 
WHERE title='Casablanca'

ポイント解説

Get to the point

カサブランカの出演者リスト

映画カサブランカの出演リスト(name)を出力する。'Casablanca'

その映画に出演した役者のリスト

movieid=11768を使う。 (または、これまでの問題で得た値を何でも使ってよい)

SELECT name
  FROM casting, actor
  WHERE movieid=(SELECT id 
             FROM movie 
             WHERE title='Casablanca')
    AND actorid=actor.id

エイリアンの出演リスト

映画「エイリアン」'Alien'の出演者リストを表示。

SELECT name
  FROM movie, casting, actor
  WHERE title='Alien'
    AND movieid=movie.id
    AND actorid=actor.id

ハリソン＝フォード映画

'Harrison Ford'  ハリソン＝フォードが出演した映画のリストを表示する。    

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id

ハリソン＝フォード共演

'Harrison Ford'ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。

[Note: ord は、映画の出演リスト順、1 が主演を意味する。]

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

1962映画の主演者

1962年の全映画を、そのタイトルと主演と併記してリスト表示。

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

---

より難しい問題

---

Busy years for John Travolta

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)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

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)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid 
JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

Lead actor in Julie Andrews movies

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')

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

Actors with 30 leading roles

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

SELECT name
    FROM casting JOIN actor
      ON  actorid = actor.id
    WHERE ord=1
    GROUP BY name
    HAVING COUNT(movieid)>=30

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC,1 ASC

List all the people who have worked with 'Art Garfunkel'.

SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
   JOIN casting b on (a.movieid=b.movieid)
   JOIN actor c on (b.actorid=c.id 
                and c.name='Art Garfunkel')
  WHERE d.id!=c.id

Clear your results

JOIN Quiz 2

That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.