sqlzoo练习系列（十二）——合并之电影数据库

【Tips】

• sqlzoo官网链接：【https://sqlzoo.net/wiki/SQL_Tutorial/zh】
• 每个系列开头介绍所用表及其信息
• 每道题含有题目描述、代码和结果，本系列第15题测试未通过
• 题目为自己简写，最好在官网查看具体题目
• 部分测试结果不完整，仅为一部分截图

---

合并之电影数据库练习链接：【https://sqlzoo.net/wiki/More_JOIN_operations/zh】

所用的表movie，actor，casting

---

1.列出1962年首映的电影，显示id,title

SELECT  id,title
 FROM movie
 WHERE yr=1962

2.电影Citizen Kane的首映年份

SELECT yr
FROM movie
WHERE title= 'Citizen Kane' 

3.列出Star Trek全系列电影，按年份排序

SELECT id,title,yr
FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr    

4.id是 11768, 11955, 21191 的电影是什么名称?

SELECT title
FROM movie
WHERE id IN (11768,11955,21191)

5.女演员'Glenn Close'的编号id是什么

SELECT id FROM actor
WHERE name='Glenn Close'

6.电影'Casablanca'的编号id是什么

SELECT id FROM movie
WHERE title='Casablanca'

7.列出电影 'Casablanca'的演员名单（真实姓名），使用movieid=11768

SELECT name
FROM actor JOIN casting ON id=actorid
WHERE movieid=11768

8.显示电影Alien的演员清单

SELECT name
FROM actor JOIN casting ON id=actorid
WHERE movieid=(SELECT id FROM movie
               WHERE title='Alien')

9.列出演员Harrison Ford曾演出的电影

SELECT title FROM movie
JOIN casting ON movie.id=casting.movieid 
JOIN actor ON casting.actorid=actor.id
WHERE actor.name='Harrison Ford'

10.列出Harrison Ford出演的电影，但他不是主角

SELECT title FROM movie
JOIN casting ON movie.id=casting.movieid JOIN actor ON casting.actorid=actor.id
WHERE actor.name='Harrison Ford' AND ord!=1

11.列出1962年首映的电影及它的第一主角

SELECT title,name FROM movie
JOIN casting ON movie.id=casting.movieid JOIN actor ON casting.actorid=actor.id
WHERE yr=1962 AND ord=1

12.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)

13.列出演员'Julie Andrews'曾参与的电影名称及其第1主角

SELECT title,name FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE movie.id IN 
(SELECT movie.id FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
WHERE name='Julie Andrews') 
AND ord=1 

14.列出按字母顺序，列出哪些演員曾作30次第1主角

SELECT name FROM actor JOIN casting 
ON id=actorid
WHERE ord=1
GROUP BY name
HAVING COUNT(name)>=30
ORDER BY name

15.列出1978年首映的电影名称及角色数目，按此数目由多至少排列

• 测试未通过

SELECT title,COUNT(actorid) FROM movie
JOIN casting ON id=movieid
WHERE yr=1978
GROUP BY title
ORDER BY COUNT(actorid) DESC

16.列出曾与演员'Art Garfunkel'合作的演员姓名

SELECT name FROM movie
JOIN casting ON movie.id=casting.movieid
JOIN actor ON actorid=actor.id
WHERE title IN (SELECT DISTINCT title FROM movie
JOIN casting ON movie.id=casting.movieid
JOIN actor ON actorid=actor.id
WHERE name='Art Garfunkel')
AND name != 'Art Garfunkel'