More JOIN operations/ja

From SQLZOO
Jump to: navigation, search
Language:Project:Language policy English  • 日本語 • 中文

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

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

movie 映画
id title タイトル yr 年 director 監督 budget 予算 gross 収益


actor 役者
id name 名前
casting 出演
movieid actorid ord 出演掲載順序 1なら主演

Movie-er.png

※ 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

より難しい問題


ジョン=トラボルタが多忙の年

'John Travolta' ジョン=トラボルタが最も忙しかった年はいつですか? その年と出演した映画の本数を表示する。

彼が2本より多く出演した各年について表示する。

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
)

ジュリー=アンドリューズ出演映画

'Julie Andrews' ジュリー=アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。

Julie Andrews は Little Miss Marker の1980 年のリメイクに主演したが、オリジナル(1934)ではしていない。

Title はユニークフィールドではない。サブクエリーでidのテーブルを作成する。

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

主演30本の役者達

少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。


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

1978年の映画を、出演者数が多い順に、タイトルを表示。


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

'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

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