SQLZOO: More JOIN operations/zh

This tutorial introduces the notion of a join. The database consists of three tables movie , actor and casting .

movie
id	title	yr	director	budget	gross

actor
id	name

casting
*movieid*	*actorid*	ord

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'

列出電影北非諜影 'Casablanca'的演員名單。

什麼是演員名單?

演員名單,即是電影中各演員的真實姓名清單。

使用 movieid=11768, 這是你上一題得到的結果。

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

8.顯示電影異型'Alien' 的演員清單。

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

9.列出演員夏里遜福 'Harrison Ford' 曾演出的電影。

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

10.列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。

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

11.列出1962年首影的電影及它的第1主角。

SELECT m.title,a.name
FROM casting c
JOIN movie m ON (c.movieid= m.id)
JOIN actor a ON (c.actorid=a.id)
WHERE m.yr=1962 AND c.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主角。

是否列了電影 "Little Miss Marker"兩次?

她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。電影名稱不是獨一的。在子查詢中使用電影編號。

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

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

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

15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列

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

16.列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。

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