SQLzoo练习题 7 更多的合拼操作

電影數據庫

此教程練習表格合拼。數據庫有三個表格：

movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)；

actor演員(id編號, name姓名)

casting角色(movieid電影編號, actorid演員編號, ord角色次序)

角色次序代表第1主角是1, 第2主角是2…如此類推.

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

Looking at the id field.

***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 actor
where id in (select actorid
             from casting
             where movieid=(select id
                            from movie
                            where title='Alien'))

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

select title
from movie
where id in (select movieid
             from casting
             where actorid=(select id
                            from actor 
                            where name='Harrison Ford'))

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

select title
from movie
where id in (select movieid
             from casting
             where ord>1
             and actorid=(select id
                          from actor 
                          where name='Harrison Ford'))

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

select title,name
from movie join casting on(movie.id=movieid)
     join actor on (actorid=actor.id)
where yr=1962 and 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  (select distinct title,movie.id
       from movie join casting on (movie.id=casting.movieid)
                  join actor on (casting.actorid=actor.id)
       where name='Julie Andrews') as a
join casting on (a.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where ord=1

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

select name
from movie join casting on (movie.id=casting.movieid)
           join actor on (casting.actorid=actor.id)
where ord=1
group by name
having count(name)>=30

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

select title,count(name)
from movie join casting on(movie.id=casting.movieid)
           join actor on (casting.movieid=actor.id)
where yr=1978
group by title
order by count(name) desc,title

这里必须给title也进行排序
***16.列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。 ***

select name
from actor
where name!='Art Garfunkel'
and actor.id in (select distinct actorid
                 from casting
                 where movieid in (select movieid
                                   from casting
                                   where actorid=(select id
                                                  from actor
                                                  where name='Art Garfunkel')))

13.16用join语法重做