SQLZOO:More JOIN operations/zh 笔记

More JOIN operations/zh

电影数据库

movie表：
id 编号
title 电影名称
yr 首映年份
director 导演
budget 制作费
gross 票房收入

actor表：
id 编号
name 姓名

casting表：
movieid 电影编号
actorid 演员编号
ord 角色次序
角色次序代表第1主角是1, 第2主角是2…如此类推。

联结查询

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系列的电影，包括id, title 和 yr(此系列电影都以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 casting JOIN actor ON (actor.id = actorid)
     JOIN movie ON (movie.id = movieid)
WHERE title = 'Alien'

9、列出演员 ‘Harrison Ford’ 曾出演的电影。

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

10、列出演员 ‘Harrison Ford’ 曾出演的电影，但他不是第1主角。

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

11、列出1962年首映的电影及它的第1主角。

SELECT title, name
FROM casting JOIN movie ON (movie.id = movieid)
     JOIN actor ON (actor.id = actorid)
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 (actor.id = actorid)
WHERE name = 'Julie Andrews'
AND ord = 1

14、列出按字母顺序，列出哪一演员曾作30次第1主角。

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

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

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

16、列出曾与演员’Art Garfunkel’合作过的演员姓名。

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