More JOIN operations -- SQLZOO

The JOIN operation

注意：where语句中对表示条件的需要用单引号，下面的译文使用的是有道翻译如有不正确，请直接投诉有道

01.List the films where the yr is 1962 [Show id, title]

译文：列出年上映时间为1962年的电影(显示id，片名)

SELECT id, title FROM movie WHERE yr=1962;

02.Give year of 'Citizen Kane'.

译文：请说出《公民凯恩》年份。

SELECT yr FROM movie WHERE title = 'Citizen Kane';

03.List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

译文：列出所有的《星际迷航》电影，包括id、标题和yr(所有这些电影的标题中都包含《星际迷航》这个单词)。订单结果逐年递增。

SELECT id, title, yr FROM movie WHERE title LIKE 'Star Trek%'

04.What id number does the actor 'Glenn Close' have?

译文：演员“格伦·克洛斯”的身份证号码是多少

SELECT title FROM movie WHERE id IN (11768, 11955, 21191)

05.What is the id of the film 'Casablanca'

译文：电影《卡萨布兰卡》的主题是什么?

SELECT id FROM actor WHERE name LIKE 'Glenn Close'

06.Obtain the cast list for 'Casablanca'.what is a cast list?Use movieid=11768, (or whatever value you got from the previous question)

译文：获取《卡萨布兰卡》的演员名单。什么是演员名单?使用movieid=11768，(或从上一个问题中得到的任何值)

SELECT id FROM movie WHERE title LIKE 'Casablanca'

07.Obtain the cast list for the film 'Alien'

译文：获取电影《异形》的演员名单

SELECT name FROM actor WHERE id IN ( SELECT actorid FROM movie m JOIN casting c ON m.id = c.movieid WHERE id = 11768)

08.List the films in which 'Harrison Ford' has appeared

译文：列出哈里森·福特出演过的电影

SELECT name FROM actor WHERE id IN ( SELECT actorid FROM movie m JOIN casting c ON m.id = c.movieid WHERE id = ( SELECT id FROM movie WHERE title LIKE 'Alien'))

09.List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

译文：列出“哈里森·福特”出演过的电影，但不包括主演的电影。[注:cast的ord字段给出了演员的位置。如果ord=1，则表示该演员在主演角色]

SELECT title FROM movie m JOIN casting c ON (m.id= c.movieid) WHERE c.actorid IN( SELECT id FROM actor WHERE name = 'Harrison Ford')

10.List the films together with the leading star for all 1962 films.

译文：列出1962年所有电影的电影和主演

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

11.Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

译文：哪一年是“洛克·哈德逊”最繁忙的年份，展示他每年制作超过2部电影的电影数量。

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) > 2;

12.List the film title and the leading actor for all of the films 'Julie Andrews' played in.

译文：请列出“朱莉·安德鲁斯”参演的所有影片的片名和男主角。

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

13.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

译文：获得一个至少扮演过15个角色的演员名单，按字母顺序排列。

SELECT name FROM actor WHERE id IN(SELECT actorid FROM casting WHERE ord = 1 GROUP BY actorid HAVING COUNT(*) >= 30);

14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

译文：列出1978年上映的电影，按演员数量排序，然后按片名排序。

SELECT title, COUNT(actorid) AS num
FROM casting JOIN movie ON id = movieid
WHERE yr= 1978
GROUP BY movieid, title
ORDER BY num DESC, title;

15.List all the people who have worked with 'Art Garfunkel'.

译文：列出所有与“阿特·加芬克尔”共事过的人。

SELECT name
FROM actor JOIN casting ON id = actorid
WHERE name <> 'Art Garfunkel'
AND movieid IN   
  SELECT movieid FROM casting    
    WHERE actorid IN (SELECT id FROM actor WHERE name = 'Art Garfunkel'));

练习网址：https://sqlzoo.net/wiki/More_JOIN_operations

——————————————————————————————————————————————————————————————————————————————————————————————————————————