第7关 More JOIN operations - SQLZOO
练习 join

-- 1.List the films where the yr is 1962 [Show id, title]
-- 练习where 
select id,title 
from movie
where yr=1962


-- 2.Give year of 'Citizen Kane'.
-- 练习where 
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.
-- 练习 like order by 

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

-- 4.What id number does the actor 'Glenn Close' have?
-- 练习 where 
select id 
from actor 
where name = 'Glenn Close'


-- 5. What is the id of the film 'Casablanca'
-- 练习where 
select id 
from movie 
where title = 'Casablanca'

-- 6. Obtain the cast list for 'Casablanca'.
--练习一表连多表
select name 
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
where title = 'Casablanca'


-- 7. Obtain the cast list for the film 'Alien'
--练习一表连多表
select name 
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
where title = 'Alien'


-- 8.List the films in which 'Harrison Ford' has appeared
-- 练习一表连多表
select title 
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
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]
-- 练习一表连多表
select title 
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
where name = 'Harrison Ford'
and ord !=1

-- 10.List the films together with the leading star for all 1962 films.
-- 练习一表连多表
select title,name
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
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.
-- 练习 join 和 group by  having 
select yr, count(title)
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
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.
-- join 和 子查询联合用

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

-- 13.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles
--  练习join on  group by  having  order by 

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

-- 14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
-- 练习 join on  group by order by 
-- having是分组后过滤功能，

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

-- 15. List all the people who have worked with 'Art Garfunkel'.
-- 注意排除'Art Garfunkel'本人。  
select name 
from casting
join actor on actor.id = actorid
join movie on movie.id = movieid 
where movieid in (
	select movieid 
	from casting
	join actor on actor.id = actorid
	where name = 'Art Garfunkel')
	and name != 'Art Garfunkel'