本文共 2213 字，大约阅读时间需要 7 分钟。

1.More Join （http://zh.sqlzoo.net/wiki/More_JOIN_operations）

第10题 List the films together with the leading star for all 1962 films.

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

2.11题

Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

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.yrhaving 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)

group by movie.yr

是因为题目要求以each year为单位进行数据搜索，因此要返回的movie.id的个数应该是在各个yr底下的。

至于having条件句的限定，是因为题目要求选出 the busiest year，故一个count(movie.id)>all(【把前面的语句全部重复了一遍】)。此处没有写入每年电影数>2这个限制要求，是因为已经有了比全部年份的数量大这个限制了，就不用再写。

12.

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

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

正确答案：

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

13.Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.

select namefrom actor join casting on actor.id=actoridwhere ord=1group by name,actoridhaving count(actorid)>=30order by actor.name

14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

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

转载地址：http://wirki.baihongyu.com/