7. More JOIN operations /SQL ZOOの答えと解説

この章では 主に中間テーブルがある時のSQLを学習していく

今までの解説

6. JOIN /SQL ZOOの答えと解説

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

SELECT id, title FROM movie WHERE yr=1962

2. 「市民ケーン」の上映年は？ ‘Citizen Kane’の年を示す。

SELECT yr FROM movie WHERE title = 'Citizen Kane'

3. スタートレック（’Star Trek’）というシリーズ映画のリストを表示

スタートレック（’Star Trek’）というシリーズ映画のリストを表示（ id title yr ）。

年の順に掲載。

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

4. 女優 ‘Glenn Close’　の id ナンバーは何ですか？

SELECT id FROM actor WHERE name = 'Glenn Close'

5. 映画 ‘Casablanca’ カサブランカの id は何ですか？

SELECT id FROM movie WHERE title = 'Casablanca' ;

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

‘Casablanca’

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

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

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

SELECT a.name FROM movie m 
JOIN casting c ON c.movieid = m.id 
JOIN actor a ON a.id = c.actorid 
WHERE m.id = ( 
 SELECT id FROM movie WHERE title = 'Alien' 
); 

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

SELECT title FROM movie m 
JOIN casting c ON c.movieid = m.id 
WHERE c.actorid = ( 
 SELECT id FROM actor WHERE name = 'Harrison Ford' 
) 

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

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

SELECT title FROM movie m 
JOIN casting c ON c.movieid = m.id 
WHERE c.actorid = ( 
 SELECT id FROM actor WHERE name = 'Harrison Ford' 
) AND c.ord 

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

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

11. ‘John Travolta’ ジョン＝トラボルタが最も忙しかった年はいつですか？

その年と出演した映画の本数を表示する。 彼が２本より多く出演した各年について表示する。 havingで抽出条件を指定。 最大のcountをもつ年を抽出

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 
)

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

SELECT movieid FROM casting c WHERE actorid IN ( SELECT id FROM actor WHERE name=’Julie Andrews’)） のサブクエリでは、Julie Andrewsが出演した映画のリストを。

JOIN casting c ON (movieid = m.id AND c.ord = 1) とすることでcastingを結合する時に、主演のものだけ結合させています

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

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

HAVINGで条件抽出 30以上なので＝を含まないと、失敗するので注意

SELECT name FROM actor a JOIN casting c ON (actorid = a.id AND c.ord = 1) 
GROUP BY name HAVING count(id) = 30 ORDER BY name

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

条件にはないが、 titleでもORDERはかけること。

SELECT title,count(actorid) FROM movie m 
JOIN casting c ON movieid = m.id 
WHERE yr = '1978' GROUP BY title ORDER BY count(actorid) desc, title

15. ‘Art Garfunkel’ アート＝ガーファンクルと一緒に仕事をした人々をすべて表示。

SELECT DISTINCT(a.name) FROM actor a 
JOIN casting c ON (c.actorid=a.id) 
JOIN casting c2 ON (c.movieid=c2.movieid) 
JOIN actor a2 ON (c2.actorid=a2.id AND a2.name='Art Garfunkel') 
WHERE a.id=a2.id