SQLZOO 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 distinct actorid from actor a
join casting c on a.id = c.movieid
where movieid=11768);

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

select name from actor 
where id in (
select actorid from casting c join movie m on movieid = id
where id = (
select id from movie where title like 'Alien' 
));

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

select title from movie m join casting c on m.id= c.movieid
join actor a on c.actorid = a.id
where  a.name = 'Harrison Ford' ;

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

select title from movie where
id in (select distinct movieid from casting 
join actor on actorid = id
where name = 'Harrison Ford'  and ord <> 1);

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

select m.title,a.name from movie m
join casting c on m.id = c.movieid
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)

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

select title,name from movie join casting on movie.id =  movieid
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 casting.ord = 1;

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

select name from 
(select name,count(actorid) from actor 
join casting on id= actorid
where ord= 1
group by name
having count(actorid) >= 30
order by name) as t;

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

select title,a from movie join
 (select id ,count(actorid) as a from movie
join casting on id= movieid
where yr = 1978
group by id
) as t  using (id)
order by a desc;