DB 쿼리문 연습 (6)

배운 쿼리

• distinct 함수
• join
• union
• sub query 사용하기
• ifnull 함수 : ifnull(체크대상, null 일 때 부여할 값)
• 페이징 처리는?
• group_concat 함수

/*
26. 제주도에 위치한 상영관을 좌석수가 많은 순서로 정렬하는 SQL문을 작성해 보세요.
[관련내용] order by 문
27. 한국에서 개최되는 주요 축제가 개최된 도시의 상영관 수를 각각 조회하는 SQL문을 작성해
주세요.
[관련내용] ifnull() 함수, sum() 함수, concat() 함수, left join 문, group by 문, 서브 쿼리 문
28. 새로운 축제를 등록하기 위해서 code값의 최대값에 + 1을 더한 신규 코드값을 가져오는 SQL
문을 작성해 보세요.
[관련내용] row_number(), max() 함수, group by, order by
29. 한국 배우중에서 이름과 영문이름이 존재하고, 생년월일 데이터가 정확한 배우의 전체
개수와 이름 순으로 20개 단위로 페이징 처리하기 위한 SQL문을 작성해 주세요.
[관련내용] count() 함수, trim() 함수, length() 함수, str_to_date() 함수, row_number() 함수, order
by 구문, limit 구문
30. 영화인이 속한 국가와 축제가 진행되는 국가 모두를 표시하는 SQL문을 작성해 주세요.
[관련내용] distinct, union, is not null 구문, trim() 함수
31. 한국의 감독 중에서 이름, 영문이름이 모두 있고, 생년월일이 정확하며 1990년에 출생한
감독들의 작품중에서 한국에서 개봉한 영화제목을 보여주는 SQL문을 작성해 보세요.(영화가
여러개인 경우 콤마(,)를 이용해서 한 컬럼에 표시해 주세요.)
[관련내용] group_concat() 함수, is not null 구문, trim() 함수, length() 함수, str_to_date() 함수,
year() 함수, order by 구문
32. 영화인 정보중 감독이 '강우석' 이면서 제작한 영화 목록을 년도순으로 조회하는 SQL문을
작성해 주세요.
[관련내용] in 구문, order by 구문
*/

# 26. 제주도에 위치한 상영관을 좌석수가 많은 순서로 정렬하는 SQL문을 작성해 보세요.
# [관련내용] order by 문

select *
from screen
where sido like '%제주%'
order by seat_count desc
;

# 27. 한국에서 개최되는 주요 축제가 개최된 도시의 상영관 수를 각각 조회하는 SQL문을 작성해
# 주세요.
# [관련내용] ifnull() 함수, sum() 함수, concat() 함수, left join 문, group by 문, 서브 쿼리 문


select f1.*,
       sum(f2.screen_count) as total_screen_count
from
(
    select concat(city, '시') as city
    from festival
    where country = '한국' and
        important_flag = '예'
) f1
    join (
    select case
        when sido like '%시' then sido
        when gugun like '%시' then gugun
        else '없는시'
        end as sigu,
    screen_name,
    screen_count,
    seat_count
    from screen
    ) f2
        on f2.sigu = f1.city
group by sigu
;


select *
from screen;
# 28. 새로운 축제를 등록하기 위해서 code값의 최대값에 + 1을 더한 신규 코드값을 가져오는 SQL
# 문을 작성해 보세요.
# [관련내용] row_number(), max() 함수, group by, order by
select max(code) + 1 as max_code
from festival
;

# 29. 한국 배우중에서 이름과 영문이름이 존재하고, 생년월일 데이터가 정확한 배우의 전체
# 개수와 이름 순으로 20개 단위로 페이징 처리하기 위한 SQL문을 작성해 주세요.
# [관련내용] count() 함수, trim() 함수, length() 함수, str_to_date() 함수, row_number() 함수, order
# by 구문, limit 구문

select t2.*
from
(
    select t1.*
    from
    (
        select row_number() over (order by name) as ranking
                 , a.*
            from actor a
            where char_length(trim(name)) <> 0
              and char_length(trim(eng_name)) <> 0
              and length(birth) = 10
              and str_to_date(birth, '%Y-%m-%d') is not null
              and country = '한국'
              and domain = '배우'
            limit 20, 10
    ) t1
    where t1.ranking < 30
) t2
where t2.ranking >= 20
;

# 30. 영화인이 속한 국가와 축제가 진행되는 국가 모두를 표시하는 SQL문을 작성해 주세요.
# [관련내용] distinct, union, is not null 구문, trim() 함수

select distinct (country)
from actor
where country is not null and
      trim(country) <> ''
union

select distinct(country)
from festival
where country is not null and
      trim(country) <> ''
;

# 31. 한국의 감독 중에서 이름, 영문이름이 모두 있고, 생년월일이 정확하며 1990년에 출생한
# 감독들의 작품중에서 한국에서 개봉한 영화제목을 보여주는 SQL문을 작성해 보세요.(영화가
# 여러개인 경우 콤마(,)를 이용해서 한 컬럼에 표시해 주세요.)
# [관련내용] group_concat() 함수, is not null 구문, trim() 함수, length() 함수, str_to_date() 함수,
# year() 함수, order by 구문



select a.*,
       (
        select group_concat(m.title)
        from movie m
        where m.director = a.name and
              m.open_flag = '개봉'
        )  as title
from actor a
where country = '한국' and
      domain = '감독' and
      name is not null and char_length(trim(name)) <> 0 and
      eng_name is not null and char_length(trim(eng_name)) <> 0 and
      year(str_to_date(birth, '%Y-%m-%d')) = 1990
;

# 32. 영화인 정보중 감독이 '강우석' 이면서 제작한 영화 목록을 년도순으로 조회하는 SQL문을
# 작성해 주세요.
# [관련내용] in 구문, order by 구문


select *
from movie
where director in ('강우석')
order by pub_year
;