|
这是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' |
|