Difference between revisions of "More JOIN operations/ja"
Kobashi.kaz (talk | contribs) |
Kobashi.kaz (talk | contribs) |
||
Line 207: | Line 207: | ||
</div> | </div> | ||
− | == | + | ==1962映画の主演者== |
<div class='qu'> | <div class='qu'> | ||
− | + | ||
+ | 1962年の全映画を、そのタイトルと主演と併記してリスト表示。 | ||
+ | |||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
</source> | </source> | ||
Line 223: | Line 225: | ||
</div> | </div> | ||
<hr> | <hr> | ||
− | + | より難しい問題 | |
<hr> | <hr> | ||
Revision as of 20:53, 28 May 2018
このチュートリアルで join の理解を深める。データベースは、3つのテーブルで構成される。 movie(映画) と actor(役者) と casting(出演) である。
テーブルの詳細は、以下である。
id | title タイトル | yr 年 | director 監督 | budget 予算 | gross 収益 |
id | name 名前 |
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.
Contents
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を使う。 (または、これまでの問題で得た値を何でも使ってよい)
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