关于SQLZOO的几个问题

chencan1987118

这是SQLZOO的里面的一些问题。。运行出来结果是正确的了。大家看看这些语句中有可以提高运行效率的吗= =~写SQL语句不久，写的不好，大家给些建议吧
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)

'John Travolta'哪一年最忙？ 显示出他每年的出演电影数量．
select yr,b from (select actor.name,movie.yr,count(movie.id) b  from actor,movie,casting where movie.id=casting.movieid and actor.id=casting.actorid and actor.name='John Travolta' group by yr order by count(movie.id)desc)a
  
4b. 列出有'Julie Andrews'参演的所有电影名称以及该片的主角．
select movie.title,actor.name from  movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and title in (select movie.title from movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and  actor.name='Julie Andrews' ) and casting.ord=1

4c. 列出那些最起码在10部电影中出任过主角的演员
select actor.name from  movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and casting.ord=1 group by actor.name having count(movie.id)>=10) a

按演员人数的多少依次列出1978 的电影以及演员数．人数最多排在最前．
select movie.title,count(actor.name) from movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and yr=1978 group by movie.title order by count(actor.name)desc

4e. 列出所有和'Art Garfunkel'合作过的演员（不要列出'Art Garfunkel'他自己）.
select actor.name from movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and movie.title in(
select movie.title from  movie,actor, casting where movie.id=casting.movieid and actor.id=casting.actorid and name='Art Garfunkel') and name not like 'Art Garfunkel'

chencan1987118

补充下。4C后面的最后的）a是不要的。发的时候多复制了点