SQLZOO 7 More JOIN

1.List the films where the yr is 1962 [Show id, title]

列出年份为1962年的电影[Show id, title]

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.

列出所有星际旅行电影，包括id,title,yr（所有的星际旅行电影名都包含Star Trek字符串），将结果按照年份排序

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 like 'Casablanca'

6.Obtain the cast list for ‘Casablanca’.

获取电影’Casablanca’的演员名单

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

7.Obtain the cast list for the film ‘Alien’

获取电影’Alien’的演员名单

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

8.List the films in which ‘Harrison Ford’ has appeared

列出’Harrison Ford’出演过的电影

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

9.List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

列出’Harrison Ford’ 出演过的电影，但不要是主演。[注意:actor表的ord字段给出了演员的位置。如果ord=1，则该演员为主演]

select title from movie join casting on movieid=id
where actorid = 
(select id from actor 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 movieid = movie.id 
join actor on actor.id = actorid 
where ord=1 and yr = 1962

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.

哪一年是“洛克·哈德森”最繁忙的年份，展示这一年以及他每年制作的电影数量，如果他制作的电影超过2部

select yr,count(title) from movie
join casting on movieid = movie.id
join actor on actorid = actor.id
where casting.actorid = 
(select id from actor where name = 'Rock Hudson')
group by yr having count(title) > 2