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、列出電影北非諜影 'Casablanca’的演員名單。
select name from actor
where id in
(select distinct actorid from actor a
join casting c on a.id = c.movieid
where movieid=11768);
8、顯示電影異型’Alien’ 的演員清單。
select name from actor
where id in (
select actorid from casting c join movie m on movieid = id
where id = (
select id from movie where title like 'Alien'
));
9、列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。
select title from movie m join casting c on m.id= c.movieid
join actor a on c.actorid = a.id
where a.name = 'Harrison Ford' ;
10、列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影,但他不是第1主角。
select title from movie where
id in (select distinct movieid from casting
join actor on actorid = id
where name = 'Harrison Ford' and ord <> 1);
11、列出1962年首影的電影及它的第1主角。
select m.title,a.name from movie m
join casting c on m.id = c.movieid
join actor a on c.actorid = a.id
where m.yr = 1962 and c.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)
12、列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
select title,name from movie join casting on movie.id = movieid
join actor on actorid = actor.id
where movie.id in
(SELECT movieid FROM casting
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews' )) and casting.ord = 1;
14、列出按字母順序,列出哪一演員曾作30次第1主角。
select name from
(select name,count(actorid) from actor
join casting on id= actorid
where ord= 1
group by name
having count(actorid) >= 30
order by name) as t;
15、列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
select title,a from movie join
(select id ,count(actorid) as a from movie
join casting on id= movieid
where yr = 1978
group by id
) as t using (id)
order by a desc;