DB 쿼리문 연습 (3)

배운 함수

• round 함수, avg 함수
• sub query
• having
• str_to_date 함수
• between A and B
• format 함수, sum 함수

/*
11. 전국 극장의 스크린 수의 평균과 ‘서울시'에 위치한 극장의 스크린 수의 평균을 구하고, 이
둘의 차이를 조회하는 SQL문을 작성해 보세요.(단, 최종 차이의 평균은 소수점 첫째 자리에서
반올림하여 구합니다.)
[관련내용] round() 함수, avg() 함수, sub query
12. 한국에서만 하는 축제 중에서 도시별로 진행하는 축제가 10개 이상인 도시를 가장 많이
진행하는 순서로 출력하는 SQL문을 작성해 보세요.(단, 도시정보가 없는 경우는 제외해 주세요.)
[관련내용]
13. 영화인 정보에서 직업이 배우가 아닌 사람 중에서 1980년 ~ 1990년 사이에 출생자를
조회하는 SQL문을 작성해 주세요.(생년월일 정보가 유효하지 않은 사람은 제외해 주세요.)
[관련내용] in, is not null, length() 함수, trim() 함수, str_to_date()함수, between a and b
14. 헝가리 출신의 영화인중 직업이 배우인 사람의 생일을 출생년도, 월, 일자를 각각 출력하는
SQL문을 작성해 보세요.(단, 생일이 입력되지 않은 사람은 제외해 주세요.)
[관련내용] str_to_date, year()함수, month()함수, day()함수, length()함수, trim()함수, is not null
15. 서울시에 위치한 극장 중에서 강남구가 아닌 극장의 좌석 수의 합을 조회하는 SQL문을
작성해 보세요.(좌석 수는 천단위로 콤마(,)로 표시해 주세요.)
[관련내용] format()함수, sum()함수, not in
*/

# 11. 전국 극장의 스크린 수의 평균과 ‘서울시'에 위치한 극장의 스크린 수의 평균을 구하고, 이
# 둘의 차이를 조회하는 SQL문을 작성해 보세요.(단, 최종 차이의 평균은 소수점 첫째 자리에서
# 반올림하여 구합니다.)
# [관련내용] round() 함수, avg() 함수, sub query

select T1.*,
       round((T1.avg_all - T1.avg_seoul), 1) as diff
from
(
    select
        (select avg(screen_count) from screen where sido = '서울시') as avg_seoul,
        (select avg(screen_count) from screen) as avg_all
) T1
;


# 12. 한국에서만 하는 축제 중에서 도시별로 진행하는 축제가 10개 이상인 도시를 가장 많이
# 진행하는 순서로 출력하는 SQL문을 작성해 보세요.(단, 도시정보가 없는 경우는 제외해 주세요.)

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


# 13. 영화인 정보에서 직업이 배우가 아닌 사람 중에서 1980년 ~ 1990년 사이에 출생자를
# 조회하는 SQL문을 작성해 주세요.(생년월일 정보가 유효하지 않은 사람은 제외해 주세요.)
# [관련내용] in, is not null, length() 함수, trim() 함수, str_to_date()함수, between a and b


select a.code,
       a.name,
       a.eng_name,
        case when length(birth) = 4 then str_to_date(birth, '%Y-%m-%d')
        else birth
        end as birth,
        a.country,
        a.domain,
        a.pilmo,
        a.reg_dt
from actor as a
where domain not in ('배우')
    and birth is not null
    and str_to_date(birth, '%Y-%m-%d') is not null
    and year(str_to_date(birth, '%Y-%m-%d')) between 1980 and 1990
;

# 14. 헝가리 출신의 영화인중 직업이 배우인 사람의 생일을 출생년도, 월, 일자를 각각 출력하는
# SQL문을 작성해 보세요.(단, 생일이 입력되지 않은 사람은 제외해 주세요.)
# [관련내용] str_to_date, year()함수, month()함수, day()함수, length()함수, trim()함수, is not null

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

# 15. 서울시에 위치한 극장 중에서 강남구가 아닌 극장의 좌석 수의 합을 조회하는 SQL문을
# 작성해 보세요.(좌석 수는 천단위로 콤마(,)로 표시해 주세요.)
# [관련내용] format()함수, sum()함수, not in

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