4. 과제1
4. 과제(23. 10. 20)
https://github.com/gikpreet/class-relational_database/blob/main/Module%2006%20SQL/01_introduction.adoc
DatamotionMovieDatabase에서, 아래 질의들을 SQL Query 식으로 작성후 Query를 제출해주세요
1. 영화 ‘퍼스트 맨’의 제작 연도, 영문 제목, 러닝 타임, 플롯을 출력하세요.
1
2
3
select ReleaseYear, Title, RunningTime, plot
from movie
where KoreanTitle = "퍼스트 맨";
2. 2003년에 개봉한 영화의 한글 제목과 영문 제목을 출력하세요
1
2
3
select Title, KoreanTitle
from movie
where ReleaseYear = 2003;
3. 영화 ‘글래디에이터’의 작곡가를 고르세요
1
2
3
4
5
6
7
8
9
10
SELECT Name
from person
where personID in (select personID
from appear
where MovieID in ( select MovieID
from movie
where koreanTitle = '글래디에이터') and roleID in (select roleID
from role
where RolekorName = '작곡'
));
4. 영화 ‘매트릭스’ 의 감독이 몇명인지 출력하세요
1
2
3
4
5
6
7
8
9
10
SELECT count(Name)
from person
where personID in (select personID
from appear
where MovieID in ( select MovieID
from movie
where koreanTitle = '매트릭스') and roleID in (select roleID
from role
where RolekorName = '감독'
));
5. 감독이 2명 이상인 영화를 출력하세요
1
2
3
4
5
6
7
8
9
select title
from movie
where MovieID in (select movieid
from appear
where RoleID in (select roleID
from Role
where RoleKorName = '감독')
group by(MovieID)
having count(movieid) >= 2);
6. ‘한스 짐머’가 참여한 영화 중 아카데미를 수상한 영화를 출력하세요
1
2
3
4
5
6
7
8
9
select koreantitle
from movie
where movieid in (select movieid
from appear
where personid = (SELECT personid FROM DatamotionMovieDatabase.person where koreanname = '한스 짐머'))
and
movieid in (select movieid
from appear
where appearid in (SELECT distinct(appearid) FROM DatamotionMovieDatabase.awardinvolve))
7. 감독이 ‘제임스 카메론’이고 ‘아놀드 슈워제네거’가 출연한 영화를 출력하세요
1
2
3
4
5
6
7
8
9
select Title
from movie
where movieid in (select movieid
from appear
where (personid in (SELECT personid FROM DatamotionMovieDatabase.person where koreanname = '제임스 카메론')) and (roleID in (select roleID from role where RolekorName = '감독')))
and
movieid in (select movieid
from appear
where personid in ((SELECT personid FROM DatamotionMovieDatabase.person where koreanname = '아놀드 슈워제네거')) and (roleID in (select roleID from role where RolekorName = '배우')));
8. 상영시간이 100분 이상인 영화 중 레오나르도 디카프리오가 출연한 영화를 고르시오
1
2
3
4
5
select Title
from movie
where RunningTime > 100 and movieid in (select movieid
from appear
where (personid in (SELECT personid FROM DatamotionMovieDatabase.person where koreanname = '레오나르도 디카프리오')));
9. 청소년 관람불가 등급의 영화 중 가장 많은 수익을 얻은 영화를 고르시오
1
2
3
select koreantitle
from movie
where BoxOfficeUSGross + BoxOfficeWWGross = (select MAX(BoxOfficeUSGross + BoxOfficeWWGross) from movie where GradeInKoreaID = 4 );
10. 1999년 이전에 제작된 영화의 수익 평균을 고르시오
1
2
3
select avg(BoxOfficeUSGross + BoxOfficeWWGross)
from movie
where releaseYear <= 1999;
11. 가장 많은 제작비가 투입된 영화를 고르시오.
1
2
select max(Budget)
from movie;
12. 제작한 영화의 제작비 총합이 가장 높은 감독은 누구입니까?
1
2
3
4
5
6
7
select Name
from person
where personid =
(select a.personid
from movie as m join appear as a on m.MovieID = a.MovieID
where a.roleid = 2
group by PersonID limit 1)
13. 출연한 영화의 모든 수익을 합하여, 총 수입이 가장 많은 배우를 출력하세요.
1
2
3
4
5
6
7
select koreanname
from person
where personid = (select a.personid, m.BoxOfficeWWGross + m.BoxOfficeUSGross
from appear as a
join movie as m on a.movieid = m.movieid
order by (m.BoxOfficeWWGross + m.BoxOfficeUSGross) desc
limit 1);
14. 제작비가 가장 적게 투입된 영화의 수익을 고르세요. (제작비가 0인 영화는 제외합니다)
1
2
3
4
5
select koreantitle
from movie
where budget = ( select MIN(Budget)
from movie
where budget != 0);
15. 제작비가 5000만 달러 이하인 영화의 미국내 평균 수익을 고르세요
1
2
3
select avg(BoxOfficeUSGross)
from movie
where Budget <= 50000000
16. 액션 장르 영화의 평균 수익을 고르세요
1
2
3
4
5
select avg(BoxOfficeWWGross + BoxOfficeUSGross)
from movie
where MovieID in (select MovieID
from moviegenre
where genreID in (select GenreID from genre where genreName = 'action'));
17. 드라마, 전쟁 장르의 영화를 고르세요.
1
2
3
4
5
6
7
select title
from movie
where MovieID in (select MovieID
from moviegenre
where genreID in( (select GenreID from genre where genreName = 'Drama'))
or
(select GenreId from genre where genreName = 'war'));
**18. 톰 행크스가 출연한 영화 중 상영 시간이 가장 긴 영화의 제목, 한글제목, 개봉연도를 출력하세요.
- 아카데미 남우주연상을 가장 많이 수상한 배우를 고르시오**
1
2
3
4
5
6
7
8
select p.koreanname
from person p
join appear a on p.personid = a.personid
join awardinvolve as award on a.appearid = award.appearid
join sector as s on award.sectorid = s.SectorID
where s.SectorKorName = '남우주연상' and award.WinningID = 2
GROUP BY p.koreanname
ORDER BY count(p.koreanname) DESC;
20. 아카데미상을 가장 많이 수상한 영화인을 고르시오 (‘수상자 없음’이 이름인 영화인은 제외합니다)
1
2
3
4
5
6
7
8
9
10
11
select p.koreanname
from person as p join appear as a on p.personid = a.personid
join awardinvolve as award on a.AppearID = award.appearid
where a.roleid in (select r.roleid
from role as r
where roleid = 6 or roleid = 7)
and
award.WinningID = 2
group by p.koreanname
order by count(p.koreanname) desc
limit 1;
21. 아카데미 남우주연상을 2번 이상 수상한 배우를 고르시오
1
2
3
4
5
6
7
8
select p.koreanname
from person p
join appear a on p.personid = a.personid
join awardinvolve as award on a.appearid = award.appearid
join sector as s on award.sectorid = s.SectorID
where s.SectorKorName = '남우주연상' and award.WinningID = 2
GROUP BY p.koreanname
having count(p.KoreanName) >= 2;
23. 아카데미상을 가장 많이 수상한 사람을 고르세요.
1
2
3
4
5
6
7
8
select p.koreanname
from person as p join appear as a on p.personid = a.personid
join awardinvolve as award on a.AppearID = award.appearid
where
award.WinningID = 2
group by p.koreanname
order by count(p.koreanname) desc
limit 1 offset 1;
24. 아카데미상에 가장 많이 노미네이트 된 영화를 고르세요.
1
2
3
4
5
6
7
8
9
10
select m.Title
from movie as m join appear as a on m.movieid = a.movieid
join awardinvolve as award on a.AppearID = award.appearid
where award.SectorID in (select SectorID
from sector
where SectorKorName Like '%영화%')
and
award.WinningID = 1
group by m.title
order by count(m.Title) desc
25. 가장 많은 영화에 출연한 여배우를 고르세요.
1
2
3
4
5
6
7
select p.koreanname,count(p.personid)
from appear as a
join person as p on p.personid = a.PersonID
where a.roleid = ( select roleid from role where rolename = 'actress' )
group by p.personid, p.koreanname
order by count(p.personid) desc
limit 1;
26. 수익이 가장 높은 영화 TOP 10을 출력하세요.
1
2
3
4
select koreantitle
from movie
order by BoxOfficeWWGross + BoxOfficeUSGross desc
limit 10;
27. 수익이 10억불 이상인 영화중 제작비가 1억불 이하인 영화를 고르시오.
1
2
3
4
select koreantitle
from movie
where BoxOfficeWWGross + BoxOfficeUSGross > 1000000000
and budget < 100000000
28. 전쟁 영화를 가장 많이 감독한 사람을 고르세요.
1
2
3
4
5
6
7
8
9
select p.koreanname
from person as p
join appear as a on p.personid = a.personid
where a.roleid in (select roleid from role where rolename = 'director')
and
a.movieid in (select movieid from moviegenre where genreid in (select genreid from genre where genrename = 'war'))
group by p.koreanname, p.personid
order by count(p.personid) desc
limit 1;
29. 드라마에 가장 많이 출연한 사람을 고르세요.
1
2
3
4
5
6
7
8
9
10
SELECT p.KoreanName ,g.GenreKorName, COUNT(*) AS cnt
FROM person p
JOIN appear a ON a.PersonID = p.PersonID
JOIN movie m ON m.MovieID = a.MovieID
JOIN moviegenre mg ON mg.MovieID = m.MovieID
JOIN genre g ON g.GenreID = mg.GenreID
WHERE g.GenreKorName = '드라마' AND NOT p.KoreanName = '수상자 없음'
GROUP BY p.PersonID
ORDER BY COUNT(*) DESC
LIMIT 1;
30. 드라마 장르에 출연했지만 호러 영화에 한번도 출연하지 않은 사람을 고르세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
select distinct p.personid, p.koreanname
from person as p
join appear as a on p.personid = a.personid
where a.roleid in (select roleid from role where rolekorname = '배우')
and
a.movieid in (select movieid from moviegenre where genreid in (select genreid from genre where genrename = 'drama'))
and
a.personid not in ( select p.personid
from person as p
join appear as a on p.personid = a.personid
where a.roleid in (select roleid from role where rolekorname = '배우')
and
a.movieid in (select movieid from moviegenre where genreid in (select genreid from genre where genrename = 'horror')));
31. 아카데미 영화제가 가장 많이 열린 장소는 어디인가요?
1
2
3
4
5
select location
from awardyear
group by Location
order by count(location) desc
limit 1;
33. 첫 번째 아카데미 영화제가 열린지 올해 기준으로 몇년이 지났나요?
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.