More JOIN operations/ja

From SQLZoo
Jump to navigation Jump to 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なら主演

※ id は movie.id と actor.id の2種類あるので注意。SQLではテーブル名(movieとactor)とフィールド名を . で接続して区別する。

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

ポイント解説

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

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

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

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

※訳者注 データベースエンジンがMySQLに設定されている場合の値。

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年の映画

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

Using_Null/ja これで確実に十分。外部結合(OUTER JOIN)に関する次のチュートリアルを見る状況ですよね。

DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects