sqlzoo 练习7——More JOIN operations

sqlzoo-More JOIN operations: https://sqlzoo.net/wiki/More_JOIN_operations

这期主要是 多个 JOIN 一起使用，将多个表联系起来。

1、List the films where the yr is 1962 [Show id, title]

SELECT id, title
FROM movie
WHERE yr=1962

2、Give year of ‘Citizen Kane’.

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

3、List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

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

4、What id number does the actor ‘Glenn Close’ have?

SELECT id
FROM actor
WHERE name = 'Glenn Close' 

5、What is the id of the film ‘Casablanca’

SELECT id
FROM movie
WHERE title =  'Casablanca'

6、Obtain the cast list for ‘Casablanca’.
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)

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

• 以为要用两个JOIN。但是看了别的答案，都是用嵌套。

//做了第10题后，看了别的答案，发现还真的可以使用两个 JOIN 的
SELECT name 
FROM actor AS a 
	JOIN casting AS c ON (a.id=c.actorid)
	JOIN movie As m ON (c.movieid=m.id)
WHERE title = 'Casablanca'

7、Obtain the cast list for the film ‘Alien’

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

//多个 JOIN
SELECT name
FROM actor AS a 
	JOIN casting AS c ON (a.id=c.actorid)
	JOIN movie As m ON (c.movieid=m.id)
WHERE title = 'Alien')

8、List the films in which ‘Harrison Ford’ has appeared

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

//多个 JOIN
SELECT title 
FROM movie AS m 
	JOIN casting AS c ON (m.id=c.movieid)
	JOIN actor AS a ON (a.id=c.actorid)
WHERE name =  'Harrison Ford'

9、List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

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

//多个 JOIN
SELECT title
FROM movie AS m 
	JOIN casting AS c ON (m.id=c.movieid)
	JOIN actor AS a ON (a.id=c.actorid)
WHERE name= 'Harrison Ford'
AND ord !=1 

• 第一个WHERE那里 用 IN 会更保险，即使嵌套里只返回一个结果，一般不止一个。

10、List the films together with the leading star for all 1962 films.

SELECT title, name
FROM actor As a 
	JOIN casting AS c ON (a.id=c.actorid) 
	JOIN movie AS m ON (c.movieid=m.id)
WHERE yr=1962 AND ord=1

• 还真的可以使用两个JOIN ！！！
• 多个JOIN 可以一起使用，只要将两个表相同值的列对应起来，所有表两两对齐就可。
• 有相同名字的列要用别名区分开来。第一个表应该是SELECT的列所在的表更好。
• 这里用不了嵌套，因为要返回来自两个表的两个列，嵌套只能返回一个表的列值。

11、Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr, COUNT(title) 
FROM movie AS m 
	JOIN casting AS c ON (m.id=c.movieid)
	JOIN actor AS a ON (c.actorid=a.id)
WHERE name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 1

12、

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

• 因为Julie 参演的电影不一定是主演。所以应该先找到她参演的电影，然后找到这些电影的主演。
• 用了一层嵌套，嵌套里面有用了JOIN。

13、Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

SELECT name
FROM actor AS a 
	JOIN casting AS c ON (a.id=c.actorid)
	JOIN movie AS m ON (c.movieid=m.id)
WHERE ord=1
GROUP BY name
HAVING COUNT(name) >= 15
ORDER BY name ASC

• 上次提到的分组的一般结构：

SELECT 列1, 列2，聚集函数(列3) AS 别名3 ... 
FROM 表
WHERE 	条件1 //选行
GROUP BY 列1 //分组
HAVING  条件2 //选组
ORDER BY 列1 ASC , 列2 ... //排序

14、List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

SELECT title, COUNT(name) AS num_a
FROM movie AS m
	JOIN casting AS c ON (m.id=c.movieid)
	JOIN actor AS a ON (c.actorid=a.id)
WHERE yr=1978
GROUP BY title
ORDER BY num_a DESC,title

15、List all the people who have worked with ‘Art Garfunkel’.

SELECT name
FROM actor AS a
	JOIN casting AS c ON (a.id=c.actorid)
	JOIN movie AS m ON (c.movieid = m.id)
WHERE name != 'Art Garfunkel'
AND movieid in 
	(SELECT movieid 
	 FROM casting JOIN actor ON (actorid=id)
		WHERE name='Art Garfunkel')

• 知道了多个JOIN 可以一起使用之后，感觉还行。
• 关键是要理清那些逻辑关系。先找什么，在找什么。