SQL Zoo 7.More JOIN operations

以下数据均来自SQL Zoo

1.List the films where the yr is 1962 [Show id, title](列出1962年的电影)

SELECT id, title
 FROM movie
 WHERE yr=1962

2.Give year of 'Citizen Kane'.(给出《公民凯恩》的年份)

select yr from movie where title = 'Citizen Kane'

3.List all of the Star Trek movies, include the id, title and yr. Order results by year.(列出所有星际迷航电影，包括id，标题和年份。按年排序结果)

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

4.What id number does the actor 'Glenn Close' have?(演员格伦·克洛斯的身份证号码)

select id from actor where name = 'Glenn Close'

5.What is the id of the film 'Casablanca'.(电影《卡萨布兰卡》的主题)

select id from movie where title = 'Casablanca'

6.Obtain the cast list for 'Casablanca'.(获得《卡萨布兰卡》的演员名单)

select name from casting 
join actor on casting.actorid = actor.id where movieid = 11768

7.Obtain the cast list for the film 'Alien'.(获取电影《异形》的演员名单)

select name from actor 
join casting on actor.id = casting.actorid where movieid = 
(select id from movie where title = 'Alien')

8.List the films in which 'Harrison Ford' has appeared.(列出哈里森·福特出演过的电影)

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

9.List the films where 'Harrison Ford' has appeared - but not in the starring role.(列出哈里森·福特出演过但不是主演的电影)

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

10.List the films together with the leading star for all 1962 films.(列出所有1962年电影的电影和主演)

select title,name from movie 
join casting on movie.id = casting.movieid
join actor on casting.actorid = actor.id
where yr = 1962 and ord=1

11.Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.(这是Rock Hudson最繁忙的年份，显示了他每年制作的电影数量，其中任何一年他制作了超过2部电影)

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

12.List the film title and the leading actor for all of the films 'Julie Andrews' played in.(列出朱莉·安德鲁斯出演的所有电影的片名和主演)

select distinct(title),name 
from movie join casting on movie.id=movieid
join actor on actorid=actor.id 
and ord=1
and movieid in (
  select t2.movieid 
  from actor t1  join casting t2 on t2.actorid=t1.id 
  and name='Julie Andrews')

13.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.(按字母顺序获得一份出演过至少15个主演角色的演员名单)

select name from actor 
left join casting on actor.id = casting.actorid 
where ord=1 group by name having count(ord)>=15

14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.(按演员人数，再按片名，列出1978年上映的电影)

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

15.List all the people who have worked with 'Art Garfunkel'.(列出所有与“Art Garfunkel”合作过的人)

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