【SQL練習】【SQLZOO】 7 More JOIN operations

7 More JOIN operations

問題はこちらです。
More JOIN operations/ja - SQLZOO

以下が解答となります。

1.1962 映画

【解答】

SELECT id, title
FROM movie
WHERE yr = 1962;

2.「市民ケーン」の上映年は？

【解答】

SELECT yr
FROM movie
WHERE title = 'Citizen Kane';

3.スタートレック映画

【解答】

SELECT id, title, yr
FROM movie
WHERE title LIKE 'Star Trek%';

4.グレン・クローズのid

【解答】

SELECT id
FROM actor
WHERE name = 'Glenn Close';

5.カサブランカのid

【解答】

SELECT id
FROM movie
WHERE title = 'Casablanca';

6.カサブランカの出演者リスト

【解答】

SELECT name FROM actor WHERE id IN 
(SELECT actorid FROM casting 
WHERE movieid = (SELECT id FROM movie
WHERE title = 'Casablanca'));

7.エイリアンの出演リスト

【解答】

SELECT name FROM actor 
WHERE id
IN (SELECT actorid FROM casting 
WHERE movieid = 
(SELECT id
FROM movie
WHERE title = 'Alien'));

8.ハリソン＝フォード映画

【解答】

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

9.ハリソン＝フォード共演

【解答】

SELECT title FROM movie 
LEFT JOIN casting 
ON movie.id = casting.movieid
WHERE casting.actorid =
(SELECT id
FROM actor
WHERE name = 'Harrison Ford')
AND ord <> 1;

10.1962映画の主演者

【解答】

SELECT title, name FROM movie 
LEFT JOIN casting ON movie.id = casting.movieid
LEFT JOIN actor ON casting.actorid = actor.id
WHERE movie.yr = 1962
AND ord = 1;

11.ジョン＝トラボルタが多忙の年

【解答】

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.ジュリー＝アンドリューズ出演映画

【解答】

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

13.主演30本の役者達

【解答】

SELECT name, COUNT(name) FROM casting 
LEFT JOIN actor ON casting.actorid = actor.id
WHERE casting.ord = 1
GROUP BY name
HAVING COUNT(name) >= 30
ORDER BY 1 ASC;

14.

【解答】

SELECT title, COUNT(actorid) FROM movie 
LEFT JOIN casting ON movie.id = casting.movieid
WHERE movie.yr =1978 GROUP BY title
ORDER BY 2 DESC, 1 ASC;

15.

【解答】

SELECT name FROM movie 
JOIN casting ON movie.id         = casting.movieid 
JOIN actor   ON casting.actorid  = actor.id 
WHERE casting.movieid IN
(SELECT movieid FROM casting
JOIN actor ON casting.actorid  = actor.id 
WHERE actor.name = 'Art Garfunkel')
AND actor.name <> 'Art Garfunkel';