列出全部Star Trek星空奇遇記系列的電影,包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
select id, title, yr
from movie
where title like'Star Trek%'orderby yr;
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’的演員名單。
select name
from actor
where id in(select actorid
from casting
where movieid =11768);
顯示電影異型’Alien’ 的演員清單。
select name
from actor
where id in(select actorid
from casting
where movieid in(select id
from movie
where title like'Alien'))orderby id;
列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。
select title
from movie
where id in(select movieid
from casting
where actorid =(select id
from actor
where name ='Harrison Ford'));
列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影,但他不是第1主角。
select title
from movie
where id in(select movieid
from casting
where actorid =(select id
from actor
where name ='Harrison Ford')and ord !=1);
列出1962年首影的電影及它的第1主角。
select title , name
from casting c join movie m on c.movieid= m.id join actor a on c.actorid=a.id
where m.yr=1962and c.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'GROUPBY yr
HAVINGCOUNT(title)=(SELECTMAX(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'GROUPBY yr)AS t
)
列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
select title, name
from casting c join movie m on c.movieid = m.id join actor a on c.actorid=a.id
where c.movieid in(select c.movieid
from casting c join actor a on c.actorid= a.id join movie m on m.id=c.movieid
where a.name ='Julie Andrews')and c.ord =1;
列出按字母順序,列出哪一演員曾作30次第1主角。
selectdistinct(name)from casting c join movie m on c.movieid = m.id join actor a on c.actorid=a.id
where c.actorid in(select actorid
from casting
where ord=1groupby actorid havingcount(*)>=30)orderby name;
列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
/* 判断不出来 */SELECT title,COUNT(actorid)AS num
FROM casting JOIN movie ON id = movieid
WHERE yr=1978GROUPBY movieid, title
ORDERBY num DESC, title;
列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。
select name
from casting c join actor a on c.actorid=a.id join movie m on c.movieid=m.id
where a.name!='Art Garfunkel'and movieid in(select movieid
from casting c join actor a on c.actorid=a.id join movie m on c.movieid=m.id
where a.name='Art GarFunkel');
7. More JOIN 更多合并操作列出1962年首影的電影, [顯示 id, title]select id, title from movie where yr=1962;電影大國民 ‘Citizen Kane’ 的首影年份。select yr from movie where title ='Citizen Kane';列出全部Star Trek星空奇遇記系列的電影...