DB - 영화 정보 데이터 실습 -2

문제

Q11. 전국 극장의 스크린 수의 평균과 '서울시'에 위치한 극장의 스크린 수의 평균을 구하고 둘의 차이점 조회

select T1.* , round((T1.screen_count_all_avg - T1.screen_count_soeul_avg), 1) as avg_diff
from
    (
    select
    (select avg(screen_count) from screen) as screen_count_all_avg
       , (select avg(screen_count) from screen where sido = '서울시') as screen_count_soeul_avg
    ) T1;

Q12. 한국에서만 하는 축제중에서 도시별로 진행하는 축제가 10개 이상인 도시를 가장 많이 진행하는 순서로 조회

select city, count(*) as festival_count
    from festival where country = '한국' 
                    and city is not null and trim(city) <> '' group by city
having festival_count >= 10 order by festival_count desc;

Q13. 영화인 정보에서 직업이 배우가 아닌 사람 중에서 1980~1990년 사이 출생자 조회 (생년월일 정보가 없는 사람은 제외)

select * from actor where domain not in ('배우')
                      and birth is not null
                      and length(trim(birth)) = 10
                      and str_to_date(birth, '%Y-%m-%d') is not null
                      and year(str_to_date(birth, '%Y-%m-%d')) between 1980 and 1990
order by str_to_date(birth, '%Y-%m-%d');

Q14. 헝가리 출신의 영화인중 직업이 배우인 사람의 생일을 출생년도, 월, 일자를 각각 출력

select name, domain,
        year(str_to_date(birth, '%Y-%m-%d')) as birth_year,
        month(str_to_date(birth, '%Y-%m-%d')) as birth_month,
        day(str_to_date(birth, '%Y-%m-%d')) as birth_day
from actor where country = '헝가리'
             and domain = '배우'
             and birth is not null
             and length(trim(birth)) = 10
             and str_to_date(birth, '%Y-%m-%d') is not null;

Q15. 서울시에 위치한 극장 중에서 강남구가 아닌 극장의 좌석 수의 합 조회

select format(sum(seat_count), 0) as total_sum
    from screen where sido = '서울시' and gugun not in ('강남구');

Q16. 전국의 CGV 극장의 이름과 규모를 조회하되 규모는 스크린 수가 5보다 작으면 '소' 5보다 크거나 같고 10보다 작으면 '중' 10보다 크면 '대'로 표시

select s.sido, s.gugun, s.screen_name, screen_count,
       case
           when s.screen_count < 5 then '소'
           when s.screen_count < 10 then '중'
           else '대'
        end as screen_scale
    from screen s where screen_name like 'CGV%';

Q17. 출신지가 프랑스나 이탈리아 이면서 직업이 촬영이거나 편집인 영화인 조회

select * from actor where country in ('프랑스','이탈리아')
                    and domain in ('촬영', '편집')

Q18. 2010~2020년 기간 중 연도별로 상영된 영화의 수 조회

select pub_year, format(count(*), 0) as pub_year_count
    from movie where pub_year between 2010 and 2020
                group by pub_year order by pub_year; 

Q19. 한국인 중에서 직업이 배우인 사람의 이름을 조회하되, 이름 중간에 '*' 처리

select name,
       case
           when char_length(trim(name)) = 2 then concat(substring(trim(name), 1, 1), '*')
           when char_length(trim(name)) = 3 then concat(substring(trim(name), 1, 1), '*', substring(trim(name), 3 ))
           when char_length(trim(name)) = 4 then concat(substring(trim(name), 1, 1), '**', substring(trim(name), 4))
           else
               rpad(substring(trim(name), 1, 1), char_length(trim(name)), '*')
       end as name_mask
from actor where country = '한국'
             and domain in ('배우')
             and char_length(trim(name)) > 1

Q20. '배'씨 성을 가진 사람 중에 '준'으로 끝나는 이름을 가진 영화인 조회

select * from actor where country in ('한국') and name like '배_준';