プログラミング

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’ ジョン=トラボルタが最も忙しかった年はいつですか?

その年と出演した映画の本数を表示する。 彼が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 

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です