SQLZOO答题笔记——More JOIN operations/zh

8.顯示電影異型’Alien’ 的演員清單。

SELECT name
from movie left join casting
on movie.id=casting.movieid
left join actor
on actor.id=casting.actorid
where movie.title='Alien'

9.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

SELECT title
from movie left join casting
on movie.id=casting.movieid
left join actor
on actor.id=casting.actorid
where actor.name='Harrison Ford'

10.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

SELECT title
from movie left join casting
on movie.id=casting.movieid
left join actor
on actor.id=casting.actorid
where actor.name='Harrison Ford' and casting.ord!=1

12.尊·特拉華達’John Travolta’最忙是哪一年?

顯示年份和該年的電影數目。

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
)

13.列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。

Select distinct(title),name
From movie join casting on movieid=movie.id
Join actor on actorid=actor.id
where movie.id in
(SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')) and ord=1;

14.列出按字母順序，列出哪一演員曾作至少30次第1主角。

select name from actor join casting on actor.id=actorid where ord=1 group by name having count(movieid)>=30 order by name asc;

15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

select title,count(actorid) as c
from movie join
casting on movie.id=casting.movieid
where yr=1978
group by title
order by c desc

#16.列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。

select distinct name
from actor join casting on actor.id=actorid
where movieid in (select movieid from casting where actorid
in (select id from actor where name='Art Garfunkel'))
and name!='Art Garfunkel'