SqlZoo.net习题答案：The Join 【Movie】

习题地址：http://sqlzoo.net/3.htm

 

表结构：　　movie(id, title, yr, director)
　　　　　　actor(id, name)
　　　　　　casting(movieid, actorid, ord)

 

1b.Give year of 'Citizen Kane'.

select yr 
from movie
where title = 'Citizen Kane'

 

1c.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.)

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

 

2a.What are the titles of the films with id 11768, 11955, 21191

select title
from movie
where id in (11768, 11955, 21191)

 

2b.What id number does the actor 'Glenn Close' have?

select id
from actor
where name = 'Glenn Close'

 

2c.What is the id of the film 'Casablanca'

select id
from movie
where title = 'Casablanca'

 

3a.Obtain the cast list for 'Casablanca'. Use the id value that you obtained in the previous question.

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

 

3b.Obtain the cast list for the film 'Alien'

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

 

3c.List the films in which 'Harrison Ford' has appeared

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

 

3d.List the films where 'Harrison Ford' has appeared - but not in the star 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 movie.title
from movie join casting on (movie.id = casting.movieid)
where casting.actorid = (select actor.id from actor where actor.name = 'Harrison Ford')  and  casting.ord <> 1

 

3e.List the films together with the leading star for all 1962 films.

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

 

4a.Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

select movie.yr, count(movie.id)
from movie join casting on (movie.id = casting.movieid) join actor on (actor.id = casting.actorid) where actor.name = 'John Travolta' 
group by movie.yr
having count(movie.id) >= all (select count(movie.id) from movie join casting on (movie.id = casting.movieid) join actor on (actor.id = casting.actorid) where actor.name = 'John Travolta' 
group by movie.yr)

 

 

4b.List the film title and the leading actor for all of 'Julie Andrews' films.

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

 

 

4c.Obtain a list of actors in who have had at least 30 starring roles.

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

 

 

4d.List the 1978 films by order of cast list size.

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

 

 

4e.List all the people who have worked with 'Art Garfunkel'.

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