JOIN 表联结（二）
这次教程，介绍的还是 JOIN概念。
数据库包含了movie , actor and casting三张表。
练习题
1.1962 年的电影们
例子：查询——所有年份是 1962 年的电影，显示 id,title
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.演员 Glenn Close的 id
SELECT id
FROM actor
WHERE name='Glenn Close'
5.电影《卡萨布兰卡》的 id
SELECT id
FROM movie
WHERE title='Casablanca'
movie数据库里的JOIN表联结
针对电影数据库，常见的问题都涵盖了三张表的数据内容，诸如「哪个演员在哪部电影里出现了？」、「哪部电影怎么怎么样」。可以使用嵌入SELECT声明来回答上述问题。
也可以使用JOIN，通过两张表里共同的域值（的表格来连接，比如actor表里的 id指向casting表里的actorid，连接之后的表格涵盖了两个表格的所有属性。
示例——联结2张表
SELECT * FROM casting JOIN actor
ON casting.actorid=actor.id
WHERE actor.name='John Hurt'
示例——联结3张表
SELECT * FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE actor.name='John Hurt'
注意：有时，我们指定 field 时，使用 field 的名称actorid，有时，我们使用casting.actorid,如果 field 的名称不是唯一的，有重复的 field 名称时，必须包含表格的名称。
6.电影《卡萨布兰卡》的卡司（演员）名单？
SELECT name FROM
movie JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
WHERE movieid=11768
7.电影《Alien》的卡司（演员）名单？
SELECT name FROM
movie JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
WHERE title='Alien'
8.演员Harrison Ford参演的电影名单？
SELECT title FROM
movie JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
WHERE name='Harrison Ford'
9.演员Harrison Ford做为配角的电影名单？
SELECT title FROM
movie JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
WHERE name='Harrison Ford'
AND ord!=1
10.演员Harrison Ford做为配角的电影名单？
SELECT title, name
FROM movie
JOIN casting ON (movie.id=movieid)
JOIN actor ON (actorid=actor.id)
WHERE yr=1962
AND ord=1
更难一些的题目
11.John Travolta档期满满的年份
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
)
12.Julie Andrews参演电影中的主演名单？
SELECT title,name FROM movie
JOIN casting ON (movie.id=movieid AND ord=1)
JOIN actor ON (actorid=actor.id)
WHERE movie.id IN(
SELECT movieid FROM casting
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
)
注意：使用 subquery 子查询
13.主演 30 部电影以上的演员？
SELECT name FROM actor
JOIN casting ON (id = actorid AND (SELECT COUNT(ord) FROM casting WHERE actorid = actor.id AND ord=1)>=30)
GROUP BY name
14.1978年的电影，谁的演员最多？
SELECT title, COUNT(actorid)
FROM movie JOIN casting ON (id=movieid)
WHERE yr = 1978
GROUP BY title
ORDER BY COUNT(actorid) DESC, title
15.所有与 Art Garfunkel 共事过的电影人？
SELECT name
FROM casting
JOIN actor ON (actorid = id)
WHERE movieid IN (SELECT movieid
FROM casting
JOIN actor
ON actorid = id
WHERE name = 'Art Garfunkel')
AND name <> 'Art Garfunkel'
