SQLZOO练习-- More JOIN operations（含题目翻译）

知识点

join on连接（两个表、三个表），join+子查询

数据表

表的连接关系

• movie.id = casting.movieid
• actor.id = casting.actorid

说明

1. movie：电影id、电影名称、上映年份、导演、预算、票房
2. actor：演员id、演员
3. casting：电影id、演员id、主演标识（1代表主演）

题目内容

1.List the films where the yr is 1962 [Show id, title].（查询上映年份为1962年的电影id和电影名称）

select id, title from movie
where yr=1962

2.Give year of 'Citizen Kane'.（查询电影‘Citizen Kane’的上映年份）

select yr from movie
where title = 'Citizen Kane'

3.List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.（查询名称包含‘Star Trek’的电影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?（查询演员'Glenn Close'的演员id）

select id from actor
where name = 'Glenn Close'

5.What is the id of the film 'Casablanca'.（查询电影 'Casablanca'的电影id）

select id from movie
where title = 'Casablanca'

6.Obtain the cast list for 'Casablanca'（movieid=11768）.（查询电影 'Casablanca'的演员）

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

7.Obtain the cast list for the film 'Alien'.（查询电影 'Alien'的演员）

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

8.List the films in which 'Harrison Ford' has appeared.（查询'Harrison Ford'参加过的电影）

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

9.List the films where 'Harrison Ford' has appeared - but not in the starring role. （查询'Harrison Ford'参加但不担任主演的电影）

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

10.List the films together with the leading star for all 1962 films.（查询1962年的电影以及电影主演）

select title,name
from casting join actor on casting.actorid = actor.id
join movie on casting.movieid = movie.id
and movie.yr = 1962
and casting.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 casting join movie on casting.movieid = movie.id 
join actor on casting.actorid = actor.id
and 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 casting join  actor on casting.actorid = actor.id
and ord = 1
group by  name
having count (movieid) >= 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 movie.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 actor join casting on actor.id = casting.actorid
and name !=  'Art Garfunkel'
and movieid in (select movieidfrom actor join casting on actor.id = casting.actoridand actor.name = 'Art Garfunkel')