SQLZOO中文版答案记录/More JOIN operations

1

SELECT id, title
 FROM movie
 WHERE yr=1962

2

SELECT yr
 FROM movie
 WHERE title like '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 like 'Glenn Close'

6

select id
 from movie
 where title like 'Casablanca'

7、这里movieid是6结果得到的id

select name from actor
 where id in
 (select actorid from casting where movieid=27)

8

select name from actor
 where id in
 (select actorid from movie m join casting c on m.id=c.movieid
 where title like 'Alien')

9

select title from movie where id in
 (select movieid from casting c join actor a on c.actorid=a.id
 where name like 'Harrison Ford')

10、ord>1或ord!=1

select title from movie where id in
 (select movieid from casting c join actor a on c.actorid=a.id
 where name like 'Harrison Ford' and ord>1)

11、两次合并

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

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、两种方式，区别在后面，一种不合并直接从actorid出发，一种先合并，再用movieid

select title,name from casting c
 join movie m on m.id=c.movieid
 join actor a on a.id=c.actorid
 where movieid in
(select movieid from casting
where actorid in 
(select id from actor where name='Julie Andrews'))
and ord=1

select title,name from casting c
 join movie m on m.id=c.movieid
 join actor a on a.id=c.actorid
 where movieid in
(select movieid from casting c
 join actor a on a.id=c.actorid
 where name='Julie Andrews') and ord=1

14、注意区分是等于30次还是大于30次。英文版里是大于15次 >=15

select name from actor a
 join casting c on a.id=c.actorid
 where ord=1
 group by name
 having count(name)=30
 order by name

15

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

16

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