SQLZOO错题总结

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

 

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

之所以要

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,name
from movie join actor on movie.id=actor.id join casting on movieid=movie.id
where movie.id=(select movieid from casting join actor on actorid=actor.id where name='Julie Andrews' and ord=1)

正确答案：

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
and
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')

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

select name
from actor join casting on actor.id=actorid
where ord=1
group by name,actorid
having count(actorid)>=30
order 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.title
order by 2 desc,title;