スポンサーリンク
この章では 主に中間テーブルがある時の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. 少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。
- 14. 1978年の映画を、出演者数が多い順に、タイトルを表示。
- 15. ‘Art Garfunkel’ アート=ガーファンクルと一緒に仕事をした人々をすべて表示。
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’ ジョン=トラボルタが最も忙しかった年はいつですか?
その年と出演した映画の本数を表示する。 彼が2本より多く出演した各年について表示する。 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. 少なくとも30タイトル以上に主演した役者の名前をアルファベット順に掲載。
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
スポンサーリンク