--List the films where the yr is 1962 [Show id, title]
SELECT
id,
title
FROM movie
WHERE yr = 1962;
-- Give year of 'Citizen Kane'
SELECT yr
FROM movie
WHERE title = 'Citizen Kane';
--List all of the Star Trek movies, include the id, title and results by year
SELECT
id,
title,
yr
FROM movie
WHERE title like '%Star Trek%'
ORDER BY yr;
-- What id number does the actor 'Glenn Close' have?
SELECT id
FROM actor
WHERE name = 'Glenn Close';
-- What is the id of the film 'Casablanca'
SELECT id
FROM movie
WHERE title = 'Casablanca';
-- Obtain the cast list for 'Casablanca'.Use movieid=11768, or whatever value you got from the previous question
-- 题目给的11768有问题，跑不出结果
SELECT name
FROM actor a
JOIN casting c
ON =
JOIN movie m
ON =
WHERE title = 'Casablanca';
-- Obtain the cast list for the film 'Alien'
SELECT name
FROM actor a
JOIN casting c
ON =
JOIN movie m
ON =
WHERE title = 'Alien';
-- List the films in which 'Harrison Ford' has appeared
SELECT title
FROM movie m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE name = 'Harrison Ford';
-- 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 m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE name = 'Harrison Ford' and ord != 1;
-- List the films together with the leading star for all 1962 films
SELECT
title,
name
FROM movie m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE yr = 1962 and ord = 1;
-- Show the year and the number of movies 'Rock Hudson' made each year for any year in which he made more than 2 movies
SELECT
yr,
count()
FROM movie m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE name = 'Rock Hudson'
GROUP BY yr
HAVING count() > 1;
-- List the film title and the leading actor for all of the films 'Julie Andrews' played in
SELECT
title,
name
FROM movie m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE title in (SELECT
title
FROM movie m
JOIN casting c
ON =
JOIN actor a
ON =
WHERE name = 'Julie Andrews')
and ord = 1;
-- Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles
SELECT
name
FROM actor a
JOIN casting c
ON =
GROUP BY name
HAVING count(case when ord = 1 then actorid end) >= 15
ORDER BY name;
-- 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 m
JOIN casting c
ON =
WHERE yr = 1978
GROUP BY title
ORDER BY
count(actorid) desc,
title;
-- List all the people who have worked with 'Art Garfunkel'
SELECT
name
FROM actor a
JOIN casting c
ON =
WHERE movieid in (
SELECT movieid
FROM casting c
JOIN actor a
ON =
WHERE name = 'Art Garfunkel'
) and name != 'Art Garfunkel';