SQL 08 – More JOIN operations

SQL 08 – More JOIN operations

---

Database

---

Problems

 

문제 1. yr이 1962년인 영화를 나열하시오 [Show id, title].

정답

SELECT id, title
FROM movie
WHERE yr=1962

 

문제 2. 'Citizen Kane' 영화의 상영 연도를 추출하세요.

정답

SELECT yr
FROM movie
WHERE title='Citizen Kane'

 

문제 3. 모든 Star Trek 영화를 나열하고 id, title 및 yr를 포함합니다(이 모든 영화 제목에 Star Trek이라는 단어가 포함됨). 연도별로 결과를 정렬합니다.

정답

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

 

문제 4. 글렌 클로즈'라는 배우는 몇 번 아이디를 가지고 있나요?

정답

SELECT id
FROM actor
WHERE name='Glenn Close'

 

문제 5. 글렌 클로즈'라는 배우는 몇 번 아이디를 가지고 있나요?

정답

SELECT id
FROM movie
WHERE title='Casablanca'

 

 

문제 6. '카사블랑카'의 출연자 명단을 얻으십시오.
출연자 명단이란 무엇인가?
출연자 명단은 영화에 출연했던 배우들의 이름입니다.

정답

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

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

 

 

문제 7. 영화 '에일리언'의 캐스팅리스트 추출하세요.

정답

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

 

문제 8. 영화 '에일리언'의 캐스팅리스트 추출하세요.

정답

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

 

문제 9. 영화 '에일리언'의 캐스팅리스트 추출하세요.

정답

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

 

문제 10. 1962년 출시한 모든 영화의 주연 배우를 영화 이름과 함께 나열하세요.

정답

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

 

문제 11. '락 허드슨'이 가장 바빴던 해는 그가 2편 이상의 영화를 만든 해와 연간 영화 수를 보여준다.

정답

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) > 2

 

문제 12. 영화 제목과 영화 '줄리 앤드류스'의 주연 배우를 나열하세요.
"Little Miss Marker 두 번" 받았어요?

정답

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

 

 

문제 13. 적어도 15개의 주연 배역을 맡은 배우들의 목록을 알파벳 순으로 얻으세요.

정답

SELECT name
 
FROM actor JOIN casting ON casting.actorid=actor.id
 
GROUP BY name
 
HAVING SUM(CASE ord WHEN 1 THEN 1 ELSE 0 END) >= 15
 
ORDER BY name

 

 

문제 14. 1978년에 개봉된 영화들을 캐스팅된 배우들의 수에 따라 순서대로 나열하고, 그 다음에 제목별로 나열하세요. 

정답

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

 

 

문제 15. Art Garfunkel'과 함께 일한 모든 사람들을 나열하세요.
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 movieid
 
                  FROM casting
 
                  WHERE actorid= (SELECT id
 
                                  FROM actor
 
                                  WHERE name='Art Garfunkel'
 
                                 )
 
                 ) AND name<>'Art Garfunkel'