[SQLZOO] More JOIN operations

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

SELECT id, title
 FROM movie
 WHERE yr=1962

2. When was Citizen Kane released?
   Give year of 'Citizen Kane'.

select yr
 from movie
 where title like 'Citizen Kane';

3.Star Trek movies
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. id for actor Glenn Close
   What id number does the actor 'Glenn Close' have?

select id
 from actor
 where name like 'Glenn Close';

5. id for Casablanca
   What is the id of the film 'Casablanca'

select id
 from movie
 where title like 'Casablanca';

6. Cast list for Casablanca
   Obtain the cast list for 'Casablanca'.

SELECT actor.name
FROM actor JOIN casting
ON casting.actorid = actor.id
join movie
on casting.movieid = movie.id
WHERE movie.title = 'casablanca';

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

select actor.name
from actor join casting
on casting.actorid = actor.id
where casting.movieid = (select id 
                          from movie 
                          where title = 'Alien');

8. Harrison Ford movies
   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';

9. Harrison Ford as a supporting actor
   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;

10. Lead actors in 1962 movies
    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 b.ord = 1;

11. Busy years for Rock Hudson
    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 yr,COUNT(title) 
 from movie 
 join casting on movie.id=movieid
 join actor   on actorid=actor.id
  where name='Rock Hudson'
  group by yr
  having count(title) > 1;

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

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

/*제일 어려웠던 문제
서브쿼리를 이용하여 JA가 출연한 영화의 리스트를 먼저 뽑으면 의외로 쉽게 풀렸다.
이걸로 이틀 고민한건 안비밀 */

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

select actor.name
 from casting 
 join actor
on actor.id = casting.actorid
where ord = 1
group by actor.name
having count(ord) >= 15

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

select movie.title, count(casting.ord)
from movie
join casting
on casting.movieid = movie.id
where movie.yr = 1978
group by movie.title
order by count(casting.ord) desc, movie.title;

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

select distinct actor.name
from casting
join actor on casting.actorid = actor.id
where casting.movieid in (select casting.movieid
                           from casting
                           join actor on casting.actorid = actor.id
                           where actor.name = 'Art Garfunkel')
and actor.name != 'Art Garfunkel'
order by actor.name;

// 'Art Garfunkel' 이름은 써주기

<느낀점>
sql은 python처럼 정해진 형식이 없어서
문제를 풀 때 마다 내가 조금씩 다른 형식으로 코드를 짠다는 걸 느꼈다.
코드를 일관성있게 통일하고, 대문자/소문자를 구분하는 연습도 해야겠다.