[MySQL] SQL ZOO More JOIN 답 해설

More JOIN operations - SQLZOO

 

기본 정보

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

 

#1 1962 movies

yr = 1962인 movie 데이터

SELECT id, title
FROM movie
WHERE yr = 1962

 

#2 When was Citizen Kane released?

title = 'citizen kane'인 movie 데이터

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

 

#3 Star Trek movies

title LIKE '%Start Trek%'인 movie 데이터

yr로 정렬

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

 

#4 id for actor Glenn Close

name = 'Glenn Close'인 actor 데이터

SELECT id
FROM actor
WHERE name = 'Glenn Close'

 

#5 id for Casablanca

title = 'Casablanca'인 movie 데이터

SELECT id
FROM movie
WHERE title =  'Casablanca'

 

#6 Cast list for Casablanca

actor -> id, name

casting -> movieid, actorid, ord

movieid = (title = Casablanca인 movie 데이터의 id 값)인 actor-casting 데이터

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

 

#7 Alien cast list

actor -> id, name

casting -> movieid, actorid, ord

movieid = (title = 'Alien'인 movie 데이터의 id 값)인 actor-casting 데이터

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

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

movie id와 actor id를 연결시켜주는건 casting에서 하고 actor.name을 찾아서 movie.title까지 넘어가야 하므로 movie에 casting과 actor을 조인하면 된다.

name = 'Harrison Ford'인 movie-casting-actor 데이터

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

 

#9 Harrison Ford as a supporting actor

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

위에 문제랑 비슷한 문제다.

다른점은 WHERE 문에서 ord값이 1이 아닌 조건을 추가로 걸어야 한다. 

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

 

#10 Lead actors in 1962 movies

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

위에 문제랑 비슷한 문제다.

다른점은 WHERE 문에서 yr = 1962 AND ord = 1인 조건을 걸어야 한다.

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

 

#11 Busy years for Rock Hudson

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

여기서부터 어려운 문제다. 

name = Rock Hudson인 조건을 걸어 yr로 그룹화 한다. 그러면 각 연도별로 Rock Hudson이 나온 movie데이터에 접근 할 수 있고 카운트를 하면 각 연도별 moive의 수를 알 수 있다. Having에 1보다 큰 카운트 값 조건을 걸어 2편 이상의 영화를 만든 조건을 걸고 yr과 COUNT(title)을 출력한다. 

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

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

name = Julie Andrews인 movie 데이터에 접근해서 title 값과 그 movie의 ord = 1인 name 값을 출력한다.

조금 많이 복잡한데 풀다보면 왜 서브쿼리를 서야 하는지 감이 온다. 

name = Julie Andews인 movie를 FROM하고 ord = 1인 actor을 JOIN한다.그리고 title로 정렬하면 된다.

SELECT DISTINCT m.title, a.name
FROM (SELECT movie.*
      FROM movie
      JOIN casting
      ON casting.movieid = movie.id
      JOIN actor
      ON actor.id = casting.actorid
      WHERE actor.name = 'Julie Andrews') AS m
JOIN (SELECT actor.*, casting.movieid AS movieid
      FROM actor
      JOIN casting
      ON casting.actorid = actor.id
      WHERE casting.ord = 1) as a
ON m.id = a.movieid
ORDER BY m.title

 

#13 Actors with 15 leading roles

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

name, ord로 그룹화를 시켜주어야 한다. 각 배우마다 주연의 수를 구해야하기 때문이다.HAVING으로 주연을 15번 이상 조건을 걸어준다.

 

SELECT actor.name
FROM movie
JOIN casting
ON casting.movieid = movie.id
JOIN actor
ON actor.id = casting.actorid
GROUP BY actor.name, casting.ord
HAVING COUNT(*) >= 15

 

#14

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

1978년도 영화만 선택해서 그것을 title로 그룹지어주면 1978년도의 개봉된 영화 목록이 그룹되고 casting에 그 영화의 배우 수는 데이터의 수와 같으므로, 카운트를 해준 값을 정렬로 사용한다. 그리고 후에 제목 순으로 한다.

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

 

#15

movie -> id, title, yr, director, budget, gross

actor -> id, name

casting -> movieid, actorid, ord

12번 문제랑 비슷한 유형이다.

name = 'Art Garfunkel'인 movie 데이터를 FROM 해주고 name != 'Art Garfunkel' 인 actor AS a 데이터 JOIN 해주어서 a.name을 출력해주면 된다. 

SELECT a.name
FROM (SELECT movie.*
      FROM movie
      JOIN casting
      ON casting.movieid = movie.id
      JOIN actor
      ON actor.id = casting.actorid
      WHERE actor.name = 'Art Garfunkel') AS m
JOIN (SELECT actor.*, casting.movieid
      FROM actor
      JOIN casting
      ON casting.actorid = actor.id
      WHERE actor.name != 'Art Garfunkel') as a
ON m.id = a.movieid

 

정리하기

JOIN에 서브쿼리까지 하는건 좀 복잡해서 힘들었따..

그래도 이만한 사이트는 없는 것 같다.

문제 수도 많고 여러가지 실험도 자유롭게 해볼 수 있어서 좋은 것 같다.