SQLZOO MORE JOIN OPERATION

1:List the films where the yr is 1962 [Show id, title]

SELECT id,title FROM movie WHERE yr = 1962;

2:Give year of 'Citizen Kane'

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

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%';

4:What are the titles of the films with id 11768, 11955, 21191

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

5:What id number does the actress 'Glenn Close' have?

SELECT id FROM actor WHERE name='Glenn Close';

6: What is the id of the film 'Casablanca'

SELECT id FROM movie WHERE title='Casablanca';

7: Obtain the cast list for 'Casablanca'.

The cast list is the names of the actors who were in the movie.

Use movieid=11768, this is the value that you obtained in the previous question.

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

8:Obtain the cast list for the film 'Alien'

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

9:List the films in which 'Harrison Ford' has appeared

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

10: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 title FROM movie JOIN actor
ON name = 'Harrison Ford'
JOIN casting ON casting.movieid=movie.id AND casting.actorid = actor.id AND ord !=1;

11: List the films together with the leading star for all 1962 films.

SELECT title,name FROM movie JOIN actor
ON yr=1962
JOIN casting
ON movie.id = casting.movieid AND actor.id = casting.actorid AND ord =1;

12:Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr,COUNT(title) FROM movie JOIN casting
ON movie.id = casting.movieid
JOIN actor ON actor.id = casting.actorid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING COUNT(title)=
(SELECT MAX(a) FROM (SELECT yr,COUNT(title) AS a FROM movie JOIN casting ON movie.id = casting.movieid JOIN actor ON actor.id = casting.actorid
WHERE name = 'John Travolta' GROUP BY yr) AS t)

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

Did you get "Little Miss Marker" twice ?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery

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

这里需要注意，题目也给提示了，如果你按照title来查询，是会有重复出现的，因为第一层的WHERE 筛选出2个title为'Little Miss Marker' 的电影，但是第二层里面实际只有一个是Julie Andrews参演的，但是由于title是一样的，所以导致筛选出来有2个。

所以需要用movieid来筛选，防止重复。

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

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

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

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

这题也是要注意的地方，红色部分我本来用的是name，后来想想还是actorid比较严谨一些。

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

SELECT name FROM actor JOIN casting
ON casting.actorid = actor.id
WHERE movieid IN(
SELECT casting.movieid FROM actor JOIN casting
ON casting.actorid = actor.id
WHERE name = 'Art Garfunkel' ) AND name !='Art Garfunkel'