SQLZOO(More JOIN operations)Writeup

1.

SELECT id, title
 FROM movie
 WHERE yr=1962

2.

select yr from movie where title='Citizen Kane'

3.

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

4.

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

5.

select id from actor where name='Glenn Close'

6.

select id from movie where title='Casablanca'

7.

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

8.

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

9.

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

10.

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

11.

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

12.

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
)

13.

SELECT DISTINCT(title),name FROM 
     movie  join casting on movie.id=movieid  join actor on actorid=actor.id 
WHERE movieid IN (
  SELECT t2.movieid FROM actor t1 inner join casting t2 on t2.actorid=t1.id and name='Julie Andrews') and ord=1

14.

SELECT name
    FROM casting JOIN actor
      ON  actorid = actor.id
   where ord=1
    group by name
    having count(movieid)>=30 

15.

SELECT title,count(actorid) FROM 
     movie  join casting on movie.id=movieid  join actor on actorid=actor.id 
    where yr=1978 
    group by title
   order by 2 desc

为什么这里不能是order by(actor) desc？

16.

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