本系列刷题笔记主要用以记录刷SQLZOO的过程中的思路、个人答案以及陌生的或者新的知识点。

题目来源 - SQLZOO
SQLZOO中题目中文版本与英文版本略有差异，题目以英文版为准

相关文章
SQL练习_1 | 0,1,2 | SQLZOO_20191002
SQL练习_2 | 3 | SQLZOO_20191008
SQL练习_3 | 4,5 | SQLZOO_20191010
SQL练习_4 | 6 | SQLZOO_20191012

目录
7 More JOIN Operations

7 More JOIN Operations

查询表格

查询表格_movie, actor, casting

7_1 List the films where the yr is 1962 [Show id, title]

SELECT id,
       title
FROM movie
WHERE yr = 1962

7_2 Give year of 'Citizen Kane'.

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

7_3 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.

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

7_4 What id number does the actor 'Glenn Close' have?

SELECT id
FROM actor
WHERE name = 'Glenn Close'

7_5 What is the id of the film 'Casablanca'

SELECT id
FROM movie
WHERE title = 'Casablanca'

7_6 Obtain the cast list for 'Casablanca'. What is a cast list?

Use movieid=11768, (or whatever value you got from the previous question)

SELECT name
FROM casting a
  JOIN actor b ON b.id = a.actorid
WHERE a.movieid = 11768

7_7 Obtain the cast list for the film 'Alien'

SELECT name
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE a.title = 'Alien'

7_8 List the films in which 'Harrison Ford' has appeared

SELECT a.title
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford'

7_9 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]

SELECT a.title
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford'
AND   b.ord <> 1

7_10 List the films together with the leading star for all 1962 films.

SELECT a.title,
       c.name
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE a.yr = '1962'
AND   ord = 1

7_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.

SELECT a.yr,
       COUNT(a.title)
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE name = 'Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

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

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

7_13 Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.

SELECT c.name
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE b.ord = 1
GROUP BY c.name
HAVING COUNT(a.title) >= 30
ORDER BY c.name ASC

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

SELECT a.title,
       COUNT(c.name)
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE a.yr = '1978'
GROUP BY a.title
ORDER BY COUNT(c.name) DESC,
         a.title

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

SELECT DISTINCT c.name
FROM movie a
  JOIN casting b ON a.id = b.movieid
  JOIN actor c ON b.actorid = c.id
WHERE a.id IN (SELECT a.id
               FROM movie a
                 JOIN casting b ON a.id = b.movieid
                 JOIN actor c ON b.actorid = c.id
               WHERE c.name = 'Art Garfunkel')
AND   c.name <> 'Art Garfunkel'