More JOIN operations
movie电影(id编号,title电影名称,yr首映年份,director导演,budget预算,gross票房)
actor(id编号,name姓名)
casting(movieid电影编号,actorid演员编号,ord角色次序)
-
列出1962年首映的电影,显示其id,title
SELECT id,title FROM movie WHERE yr=1962;
-
电影citizen kane 的首映年份
select yr from movie where title = 'Citizen Kane';
-
列出所有star trek星空奇遇系列的电影,包括id,title,yr.按年份顺序排列
select id,title,yr from movie where title like 'Star Trek%' order by 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 join casting on id = actorid where movieid = 11768;
-
显示电影类型Alien的演员清单
select name from actor where id in( select actorid from movie join casting on id = movieid where title like 'Alien');
-
列出演员Harrison Ford曾出演的电影
select title from movie where id in ( select movieid from casting join actor on actorid = id where name = 'Harrison Ford');
-
列出演员Harrison Ford曾演出的电影,但不是第一主角。
select title from movie where id in ( select movieid from casting join actor on actorid = id where ord != 1 and name = 'Harrison Ford');
-
列出1962年首映的电影及其第一主角
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 c.ord = 1 and yr = 1962;
-
john travolta 最忙的年份,显示年份和该年的电影数目。
方式一:
SELECT yr,COUNT(title) FROM movie m JOIN casting c ON m.id = c.movieid JOIN actor a ON c.actorid = a.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 )
方式二:
SELECT yr,COUNT(title) FROM movie m JOIN casting c ON m.id = c.movieid JOIN actor a ON c.actorid = a.id where name = 'John Travolta' GROUP BY yr order by count(title) DESC limit 1;
-
列出演员Julie Andrews曾参演的电影名称及其第一主角。
select title,name from movie m join casting c on m.id = c.movieid join actor a on c.actorid = a.id where movieid in (SELECT movieid FROM casting WHERE actorid IN ( SELECT id FROM actor WHERE name='Julie Andrews')) and ord = 1;
-
按字母顺序列出哪一演员曾做过30次第一主角。
select distinct(name) from actor a join casting c on a.id = c.actorid join movie m on c.movieid = m.id where c.actorid in ( select actorid from casting c where ord = 1 group by actorid having count(ord) >= 30);
-
列出1978年首映的电影名称及角色数目,按照数目由多到少排序。
select title,count(actorid) from movie m join casting c on c.movieid=m.id join actor a on c.actorid=a.id where yr=1978 group by title order by count(actorid) desc,title;
(这一题sqlzoo提交会报错,但得出的结果是符合题目的,具体报错原因暂不知,个人觉得是title顺序的问题,望知道错误原因的同学告知。)
-
列出曾与Art Garfunkel合作过的演员姓名。
select name from actor a join casting c on a.id = c.actorid join movie m on m.id = c.movieid where name != 'Art Garfunkel' and m.id in ( select movieid from casting c join movie m on m.id = c.movieid join actor a on c.actorid = a.id where a.name = 'Art Garfunkel');