SQLZOO答案--More JOIN operations

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%'

 

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 movie JOIN actor ON title = 'Alien'
    JOIN casting ON movie.id = casting.movieid AND actor.id = casting.actorid

 

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

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

 

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

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

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

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

 

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

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

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

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

注：此题不知道为什么不正确，以上供参考

 

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

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