More JOIN operations/zh

電影數據庫

此教程練習表格合拼。數據庫有三個表格
movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)
actor演員(id編號, name姓名)
casting角色(movieid電影編號, actorid演員編號, ord角色次序)
角色次序代表第1主角是1, 第2主角是2...如此類推.

More details about the database.

現在開始.

熱身

列出1962年首影的電影， [顯示 id, title]

SELECT  title
 FROM movie
 WHERE yr=1980

SELECT id, title
 FROM movie
 WHERE yr=1962

電影大國民 'Citizen Kane' 的首影年份。

SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

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

Looking at the id field.

id是 11768, 11955, 21191 的電影是什麼名稱?

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

女演員'Glenn Close'的編號 id是什麼?

SELECT id FROM actor
  WHERE name= 'Glenn Close'

電影北非諜影'Casablanca' 的編號 id是什麼?

SELECT id 
FROM movie 
WHERE title='Casablanca'

合拼語法

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

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

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

SELECT name
  FROM casting, actor
  WHERE movieid=(SELECT id 
             FROM movie 
             WHERE title='Casablanca')
    AND actorid=actor.id

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

SELECT name
  FROM movie, casting, actor
  WHERE title='Alien'
    AND movieid=movie.id
    AND actorid=actor.id

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

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

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

SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id
  AND ord<>1

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

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

困難的題目

尊·特拉華達'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
)

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
)

列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。

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

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')

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

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

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

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

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

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

SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
   JOIN casting b on (a.movieid=b.movieid)
   JOIN actor c on (b.actorid=c.id 
                and c.name='Art Garfunkel')
  WHERE d.id!=c.id

Clear your results

JOIN Quiz 2

That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.