SQLZOO More_JOIN_operations 答案 电影数据库

题目网址：https://sqlzoo.net/wiki/More_JOIN_operations/zh

SELECT  id, title
FROM movie
WHERE yr=1962

select yr
from movie
where title='Citizen Kane'

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

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

select id
from actor
where name='Glenn Close'

select id
from movie
where title='Casablanca'

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

select actor.name
from movie,actor, casting
where actor.id=casting.actorid 
      and movie.id=casting.movieid 
      and title='Alien'

select title
from movie,actor, casting
where actor.id=casting.actorid 
      and movie.id=casting.movieid 
      and actor.name='Harrison Ford'

select title
from movie,actor, casting
where actor.id=casting.actorid 
      and movie.id=casting.movieid 
      and actor.name='Harrison Ford' 
      and casting.ord!=1

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

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

select title,name
from movie JOIN casting ON movie.id=movieid
         JOIN actor ON actorid=actor.id
where movieid in(
  select movieid
  from casting JOIN actor ON actorid=actor.id
  where name='Julie Andrews'
) AND ord=1

select distinct name
from actor join casting on id=actorid
where actorid in (
  select actorid 
  from casting
  where ord=1
  group by actorid
  having count(ord)>=30
)
order by name

15. 不通过，似乎内容一样，但title的排序不对。

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

select name
from actor join casting on id=actorid
where name!='Art Garfunkel' and movieid in (
  select movieid 
  from actor join casting on id=actorid
  where name='Art Garfunkel'
)