スポンサーリンク
この章では 主に中間テーブルがある時のSQLを学習していく
今までの解説
スポンサーリンク
目次
- 1. 1962 年の映画のリストを表示（ id と title を表示）
- 2. 「市民ケーン」の上映年は？ ‘Citizen Kane’の年を示す。
- 3. スタートレック（’Star Trek’）というシリーズ映画のリストを表示
- 4. 女優 ‘Glenn Close’ の id ナンバーは何ですか？
- 5. 映画 ‘Casablanca’ カサブランカの id は何ですか？
- 6. 映画カサブランカの出演リスト(name)を出力する。
- 7. 映画「エイリアン」’Alien’の出演者リストを表示。
- 8. ‘Harrison Ford’ ハリソン＝フォードが出演した映画のリストを表示する。
- 9. ‘Harrison Ford’ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。
- 10. 1962年の全映画を、そのタイトルと主演と併記してリスト表示。
- 11. ‘John Travolta’ ジョン＝トラボルタが最も忙しかった年はいつですか？
- 12. ‘Julie Andrews’ ジュリー＝アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。
- 13. 少なくとも３０タイトル以上に主演した役者の名前をアルファベット順に掲載。
- 14. 1978年の映画を、出演者数が多い順に、タイトルを表示。
- 15. ‘Art Garfunkel’ アート＝ガーファンクルと一緒に仕事をした人々をすべて表示。
1. 1962 年の映画のリストを表示（ id と title を表示）
SELECT id, title FROM movie
WHERE yr=19622. 「市民ケーン」の上映年は？ ‘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 name14. 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, title15. ‘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 スポンサーリンク
