Query 쿼리실습

-- 1) 영화 정보 중 영문 제목이 없는 데이터 조회하기 --
-- trim(): 공백제거
select *
from movie m 
where eng_title is null or trim(eng_title) = ''
-- where eng_title is null or length(trim(eng_title)) = 0
;

-- 2) 한국영화 중 2001년 개봉한 액션영화 조회 --
-- like: 이 문자를 포함한 (%가 뒤에 있으면 이 문자로 시작하는, 앞에 있으면 끝나는)
select *
from movie m 
where country = '한국' 
	and pub_year = 2001 
	and genre like '%액션%' 
;

-- 3) 싸이더스가 2020년 개봉한 영화의 감독의 출생년도를 조회 --
-- 싸이더스가 2020년 개봉한 영화의 감독 확인
select director 
from movie m  
where production like "%싸이더스%"
	and pub_year = 2020
;
-- 그 이름을 가진 사람의 birth 조회
-- in (  ) 안에 다른 테이블에서 조회하는 쿼리문을 작성하여 사용할 수 있음
select birth
from actor a 
where domain = '감독'
	and name in (
		select director 
		from movie m  
		where production like "%싸이더스%"
			and pub_year = 2020
	)
;

-- 4) 영화인 정보에서 직업을 중복없이 조회 --
select distinct `domain` 
from actor a
where `domain` is not null and trim(`domain`) <> ''
;

-- 5) 영화 감독의 국가가 독일이고 2020년 이후 개봉된 영화의 --
-- 제목, 감독, 개봉일자, 장르를 최근 개봉일자 순으로 조회 --
select m.title , m.director , m.pub_year , m.genre 
from movie m 
	join actor a 
		on (m.director = a.name  and a.`domain` = '감독')
where m.pub_year > 2020
	and a.country = '독일'
order by m.pub_year desc 
;

-- 6) 시카고에서 진행하는 축제 중 영문 제목이 없는 경우 한글 제목으로 보여주고 -- 
-- 장르가 없는 경우 기타로 표시하며, 홈페이지가 없는 경우 홈페이지 없음으로 조회 --
select f.code
	, f.title 
	, 
	case 
		when ifnull(f.eng_title, '') = '' then f.title 
		else f.eng_title 
	end as eng_title 
	, f.continent 
	, f.country 
	, f.city 
	, 
	case 
		when ifnull(f.gerne, '') = '' then '기타'
		else f.gerne 
	end as gerne 
	, f.important_flag 
	,
	case
		when ifnull(f.homepage, '') = '' then '홈페이지 없음'
		else f.homepage
	end as homepage
	, f.first_open_date 
from festival f 
where country = '미국' 
	and city = '시카고'
;

-- 7) 영화 상연관 회사별로 좌석 수가 가장 많은 값을 구하고 이를 순서대로 조회 --
-- row_number() : 순위 번호 메기기
select 
	row_number() over (order by max(seat_count) desc) as ranking
	, biz_name 
	, max(seat_count) as max_seat_count
from screen s 
where biz_name is not null and trim(biz_name) <> ''
group by biz_name
order by max_seat_count desc 
;

-- 8) 국가별 영화 정보의 개수를 조회 --
-- 단, 국가가 없는 경우 국가 미상으로 처리 --
select 
	case when ifnull(country, '') = '' then '국가 미상'
		else country 
	end as country
	, count(*) as movie_count
from movie m 
group by country
order by movie_count desc 
;

-- 9) CGV 극장 중 스크린 수가 가장 많은 극장의 순위 5위까지 조회 --
select
	row_number() over (order by screen_count desc) as ranking
	, s.*
from screen s 
where biz_name = 'CJ올리브네트웍스(주)'
order by screen_count desc 
limit 5
;

-- 10) 스크린 수가 가장 많은 극장의 이름을 조회 --
select *
from screen s
where screen_count in (
	select max(screen_count)
	from screen
)
;

-- 11) 전국 극장의 스크린 수의 평균과 서울에 위치한 극장의 스크린 수의 평균을 구하고 --
-- 이 둘의 차이를 조회 (차이의 평균은 소수점 첫째 자리에서 반올림) --
-- from 다음에는 테이블이 나와야 하는데, select로 만든 것도 테이블이기 때문에 가능 --
select T1.*
	, round(T1.screen_count_all_avg - T1.screen_count_sido_avg, 1) as avg_diff
from 
(
	select 
		(select avg(screen_count) from screen s) as screen_count_all_avg
		, (select avg(screen_count) from screen s where sido = '서울시') as screen_count_sido_avg
) T1
;

-- 12) 한국에서만 하는 축제 중 도시별로 진행하는 축제가 10개 이상인 도시를 가장 많이 진행하는 순서로 조회 --
select city 
	, count(*) as festival_count
from festival f 
where country = '한국'
	and city is not null and trim(city) <> ''
group by city 
having festival_count >= 10
order by festival_count desc
;

-- 13) 영화인 정보에서 직업이 배우가 아닌 사람 중 1980~1990년 사이 출생자를 조회 --
-- 단, 생년월일 입력되지 않은 사람 제외 --
select *
from actor a 
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')
;

-- 14) 헝가리 출신 영화인 중 직업이 배우인 사람의 생일을 출생년도, 월, 일자를 조회 --
-- 단, 생년월일 입력되지 않은 사람 제외 --
select a.name , a.eng_name , a.`domain` 
	, birth 
	, 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 a 
where `domain` = '배우'
	and birth is not null
	and length(trim(birth)) = 10
	and str_to_date(birth, '%Y-%m-%d') is not null 
;

-- 15) 서울시에 위치한 극장 중에서 강남구가 아닌 극장의 좌석 수의 합을 조회 --
-- 좌석수는 천 단위 콤마로 표시 --
select format(sum(seat_count), 0) as total_sum
from screen s where sido = '서울시'
	and gugun not in ('강남구')
;

-- 16) 전국의 CGV 극장 이름과 규모를 조회 --
-- 스크린 수가 5보다 작으면 '소', 10보다 작으면 '중', 10보다 크거나 같으면 '대'로 표시 --
select
	s.code 
	, s.sido 
	, s.gugun 
	, s.screen_name 
	, s.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%'
order by screen_scale desc 
;

-- 17) 출신지가 프랑스나 이탈리아이면서 직업이 촬영이거나 편집인 영화인을 조회 --
select *
from actor a 
where country in ('프랑스', '이탈리아')
	and `domain` in ('촬영', '편집')
;

-- 18) 2010 ~ 2020년 기간 중 연도별로 상영된 영화의 수를 조회 --
select pub_year , count(*) as pub_year_count
from movie m
where pub_year >= 2010 and pub_year <= 2020
group by pub_year
order by pub_year 
;

-- 19) 한국인 중에서 직업이 배우인 사람의 이름 조회 --
-- 개인정보 보호를 위해 이름 중간에 * 처리 
-- (이름이 2개 이상의 문자이고 외자인 경우 성만 표시, 4자면 성과 맨 마지막,
-- 그 이상이면 첫 성만 보여주고 나머지는 * 처리)
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 a 
where country = '한국'
	and `domain` = '배우'
	and length(trim(name)) > 1
;

-- 20) 한국인이면서 '배'씨 성을 가진 사람중에 '준'으로 끝나는 이름값을 가진 영화인 조회 --
-- %는 사이 개수 상관없음 / _는 문자 개수만큼
select *
from actor a 
where country = '한국'
	and name like '배_준'
;

-- 21) 한국인 이면서 '원'씨 성을 가진 배우를 조회 --
select *
from actor a 
where name like '원%'
	and country = '한국'
	and `domain` = '배우'
;

-- 22) 대전시에 있는 상영관 중 좌석수가 500 ~ 1000 사이인 극장의 이름 조회 --
select screen_name , seat_count 
from screen s 
where sido = '대전시'
	and seat_count between 500 and 1000
;

-- 23) 시도별 상영관의 전체 좌석 수, 최소 좌석 수, 평균 좌석 수 내용을 조회 --
-- 단, 좌석 수가 0인 상영관 제외, 평균 좌석수는 소수점 올림 처리 --
select sido
	, sum(seat_count) as seat_count_sum
	, max(seat_count) as seat_count_max
	, min(seat_count) as seat_count_min
	, ceil(avg(seat_count)) as seat_count_avg
from screen s 
where seat_count > 0
group by sido
order by seat_count_sum desc 
;

-- 24) 시도별 평균 좌석수가 900 이상인 상영관을 조회 --
-- 시도별 평균 좌석수는 소수점 2자리까지 반올림 --
select sido
	, round(avg(seat_count), 2) as seat_count_avg
from screen s 
group by sido 
having seat_count_avg >= 900
order by seat_count_avg desc 
;

-- 25) 영화인 중 생일을 알고, 3월에 태어난 사람 중 직업이 감독인 사람의 한글, 영문이름 조회 --
-- 출신국가가 한국이면 '국내', 아니면 '국외'로 표시 --
select a.name , a.eng_name 
	, 
	case 
		when country = '한국' then '국내'
		else '국외'
	end as country_comment
from actor a 
where domain = '감독'
	and length(trim(birth)) = 10
	and str_to_date(birth, '%Y-%m-%d') is not null 
	and month(str_to_date(birth, '%Y-%m-%d')) = 3
;

-- 26) 제주도에 위치한 상영관을 좌석 수가 많은 순서대로 정렬 --
select *
from screen s 
where sido = '제주도'
order by seat_count desc
;

-- 27) 한국에서 개최되는 주요 축제가 개최된 도시의 상영관 수를 각각 조회 --

select T2.*
	, ifnull(s.screen_count_sum, 0) as screen_count_sum 
from 
(
	select concat(city, '시') as city 
	from festival f 
	where country = '한국'
		and important_flag = '예'
) T2
	left join 
	(
		select sido 
			, sum(screen_count) as screen_count_sum
		from screen s 
		group by sido
	) s on s.sido = T2.city
;


-- 28) 새로운 축제를 등록하기 위해서 code값의 최대값에 + 1을 더한 신규 코드값을 조회 --
select ifnull(max(code), 0) + 1 as new_code
from festival f 
;

-- 29) 한국 배우중에서 이름과 영문이름이 존재하고, 생년월일 데이터가 정확한 배우의 전체 개수와 이름 순으로
-- 20개 단위로 페이징 조회
-- 페이징 처리는 1) 전체개수, 2) 현재 페이지 목록을 알아야 함 --
select row_number() over (order by a.name) as ranking
	, a.*
from actor a 
where a.country = '한국'
	and a.`domain` = '배우'
	and a.name is not null and trim(a.name) <> ''
	and a.eng_name is not null and trim(a.eng_name) <> '' 
	and str_to_date(a.birth, '%Y-%m-%d') is not null 
	and length(trim(a.birth)) = 10
order by a.name 
limit 30, 10
;

-- 30) 영화인이 속한 국가와 축제가 진행되는 국가 모두를 조회 --
-- union / union all
select 10
union
select 20
union
select 30
union
select 20
union all
select 30
;

select country
from actor a
where country is not null and trim(country) <> ''
group by country 

union 

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


-- 31) 한국 감독 중 이름, 영문이름이 모두 있고 생년월일이 정확하며
-- 1990년 출생 감독들 중 한국에서 개봉한 영화제목 정보 조회
-- 영화가 여러개인 경우 , 이용하여 한 컬럼에 표시

select a.*
	, (
	-- group_concat은 컬럼이 여러개 있을 때 하나로 만들어주고 중간에 ,로 구분해줌
		select group_concat(title)
		from movie m 
		where country = '한국'
			and open_flag = '개봉'
			and m.director = a.name
			-- and m.title is not null
	) as movie_title
from actor a
where country = '한국'
	and `domain` = '감독'
	and name is not null and trim(name) <> ''
	and eng_name is not null and trim(eng_name) <> ''
	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')) = 1990
;


-- 32) 영화인 정보 중 감독이 '강우석'이면서 재작한 영화 목록을 년도순으로 조회 --
select *
from movie m 
	where director in (
		select name 
		from actor a 
		where `domain` = '감독'
			and name = '강우석'
	)
order by pub_year desc 
;

---

마지막 수정일시: 2022-10-26 11:06