sql zoo我的答案之更多的合拼操作

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、顯示電影異型'Alien' 的演員清單。

      SELECT actor.name FROM casting JOIN movie ON casting.movieid=movie.id JOIN actor ON casting.actorid=actor.id  WHERE movie.title='Casablanca';多表内连接需要一个能和其余表连接起来的中间表。

或者

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

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

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

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

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

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

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

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

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

12、尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。

     SELECT yr,COUNT(*)
      FROM movie JOIN casting JOIN actor
     ON movie.id=casting.movieid AND casting.actorid=actor.id
     AND(director='John Travolta' OR name='John Travolta')
     GROUP BY yr
     HAVING COUNT(*)>=ALL(SELECT COUNT(*)

                                               FROM movie JOIN casting JOIN actor
                                              ON movie.id=casting.movieid AND casting.actorid=actor.id
                                              AND (director='John Travolta' OR name='John Travolta')
                                             GROUP BY yr);

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

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

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

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

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

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

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

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