sqlzoo-More JOIN

7. More JOIN 更多合并操作

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星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id, title, yr 
from movie 
where title like 'Star Trek%' 
order by yr;

4. id是 11768, 11955, 21191 的電影是什麼名稱?

select title 
from movie 
where id in (11768, 11955, 21191);

5. 女演員’Glenn Close’的編號 id是什麼?

select id 
from actor 
where name = 'Glenn Close';

6. 電影北非諜影’Casablanca’ 的編號 id是什麼?

select id 
from movie 
where title = 'Casablanca';

7. 列出電影北非諜影 'Casablanca’的演員名單。

select name 
from actor 
where id in (select actorid 
			 from casting 
			 where movieid = 11768);

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

select name
from actor 
where id in (select actorid 
			 from casting 
			 where movieid in (select id 
			 				   from movie 
			 				   where title like 'Alien'))
order by id;

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

select title 
from movie 
where id in (select movieid 
			 from casting 
			 where actorid = (select id 
			 				  from actor 
			 				  where name = 'Harrison Ford'));

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

select title 
from movie 
where id in (select movieid 
 		     from casting
 		     where actorid = (select id 
 						      from actor 
 						      where name = 'Harrison Ford')
 and ord != 1);

11. 列出1962年首影的電影及它的第1主角。

select title , name 
from casting c join movie m on c.movieid= m.id join actor a on c.actorid=a.id 
where m.yr=1962 and c.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 title, name 
from casting c join movie m on c.movieid = m.id join actor a on c.actorid=a.id 
where c.movieid in (select c.movieid 
					from casting c join actor a on c.actorid= a.id join movie m on m.id=c.movieid 
					where a.name = 'Julie Andrews') 
and c.ord = 1;

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

select distinct(name) 
from casting c join movie m on c.movieid = m.id join actor a on c.actorid=a.id 
where c.actorid in (select actorid 
					from casting 
					where ord=1 
					group by actorid having count(*)>=30)
order by name;

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

/* 判断不出来 */
SELECT title, COUNT(actorid) AS num
FROM casting JOIN movie ON id = movieid
WHERE yr= 1978
GROUP BY movieid, title
ORDER BY num DESC, title;

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

select name 
from casting c join actor a on c.actorid=a.id join movie m on c.movieid=m.id 
where a.name!='Art Garfunkel' 
and movieid in (select movieid 
 				from casting c join actor a on c.actorid=a.id join movie m on c.movieid=m.id 
            	  where a.name='Art GarFunkel');