[8일차] MySQL 쿼리 전체

use sakila;

show tables;

#----------------------------------------------------------------------------
#    2022-06-29
#----------------------------------------------------------------------------
#      연습문제 
#----------------------------------------------------------------------------
# 출연 작이 많은 순으로 배우의 id, first, last_name, 작품 수를 출력하세요

# 배우의 id, first, last_name 출력 
select actor_id, first_name, last_name
from actor;

# 출연 작이 많은 순으로 배우의 id, first, last_name, 작품수를 출력하세요
select A.actor_id, A.first_name, A.last_name, count(FA.film_id)
from actor A join film_actor FA 
on A.actor_id = FA.actor_id
group by A.actor_id, A.first_name, A.last_name
order by count(FA.film_id) desc;

# 동명이인 출력하기 
select first_name, last_name, count(*) 
from actor
group by first_name, last_name
having count(*) > 1; # group by에서 조건 

select *
from actor
where first_name = 'SUSAN' and last_name = 'DAVIS';

# MARY KEITEL의 출연작을 영화제목 오름 차순으로 출력하세요. 출력 컬럼은 다음과 같다.
# first_name, last_name, 영화제목, 출시년도, 대여 비용
select first_name, last_name
from actor;

select A.first_name, A.last_name, F.title as '영화 제목', F.release_year as 출시년도,
  F.rental_rate as '대여 비용'
from actor A join film_actor FA 
on A.actor_id = FA.actor_id
join Film F on F.film_id = FA.film_id
where A.first_name='MARY' and last_name='KEITEL'
order by F.title;

# 미국 영화 등급
/*<영화 등급>
G : 모든 연령대 시청가능
PG (Parental Guidance) : 모든 연령대 시청가능하나, 부모의 지도가 필요
PG-13 : 13세 미만의 아동에게 부적절 할 수 있으며, 부모의 주의를 요함
R(Restricted) : 17세 또는 그이상의 성인
NC-17 : 17세 이하 시청 불가 */

# 배우의 'R' 등급 영화 작품 수를 카운트하여, 가장 많은 작품 수를 가지는 배우부터 출력하시오.
# 출력 칼럼은 다음과 같다.
# actor_id, first_name, last_name, 'R' 등급 작품 수
select A.actor_id, A.first_name, A.last_name, count(F.title) as 'R등급 작품 수'
from film F join film_actor FA on F.film_id = FA.film_id
join actor A on A.actor_id = FA.actor_id
where F.Rating = 'R'
group by A.actor_id, A.first_name, A.last_name
order by count(F.title) desc;

#'R' 등급 영화에 출연한 적이 없는 배우의
# actor_id, first_name, last_name, 출연 영화 제목,출시년도를 출시년도 순으로 출력하세요.

# 잘못 쓴 풀이... ㅠ
select A.actor_id as '배우 번호', A.first_name, A.last_name , F.title as '출연영화 제목', F.release_year as 출시년도, rating
from actor A join film_actor FA on A.actor_id = FA.actor_id
join film F on F.film_id = FA.film_id
where F.rating  = 'R' # R등급이 아닌 영화가 다 튀어나온다. 그래서 잘못됨
group by A.actor_id, A.first_name, A.last_name
order by F.release_year;

/* 'R' 등급 영화에 출연한 적이 없는 배우의
# actor_id, first_name, last_name, 출연 영화 제목,출시년도를 출시년도 순으로 출력하세요. */
select A.actor_id as '배우 번호', A.first_name, A.last_name , F.title as '출연 영화 제목', F.release_year as 출시년도, rating
from actor A join film_actor FA on A.actor_id = FA.actor_id
join film F on F.film_id = FA.film_id
where F.rating != 'R' # R등급이 아닌 영화가 다 튀어나온다. 그래서 잘못됨
order by F.release_year;

#/* 'R' 등급 영화에 출연한 적이 없는 배우의
# actor_id, first_name, last_name, 출연 영화 제목,출시년도를 출시년도 순으로 출력하세요. */
#정답 

select A.actor_id as '배우 번호', A.first_name,
 A.last_name , F.title as '출연 영화 제목', F.release_year as 출시년도
from film F join film_actor FA on F.film_id = FA.film_id
join actor A on A.actor_id = FA.actor_id
where A.actor_id not in (select FA.actor_id # not in 집합에 값이 없는 경우
from film F join film_actor FA on F.film_id = FA.film_id
    where F.rating = 'R')
order by F.release_year;

select FA.actor_id # not in 집합에 값이 없는 경우
from film F join film_actor FA on F.film_id = FA.film_id
where F.rating = 'R';

#검색해서 찾은 거
SELECT a.first_name, a.last_name
FROM actor a
WHERE NOT EXISTS (SELECT 1
  FROM film_actor fa
   INNER JOIN film f
ON fa.film_id = f.film_id
    WHERE fa.actor_id = a.actor_id
    AND f.rating = 'R');


select *
from film;



# 10. 영화 'AGENT TRUMAN'를 보유하고 있는 매장의 정보를 아래와 같이 출력함.
# 영화 제목, 매장ID, 매장staff first_name, 매장 staff last_name, 매장의 address, district, city, country, 해당 타이틀 "보유 수량"

# 매장ID, 매장staff first_name, 매장 staff last_name, 매장의 address, district, city, country
select S.store_id, S.first_name, S.last_name, AD.address, AD.district, C.city ,CO.country
from staff S join address AD on S.address_id = AD.address_id
join city C on C.city_id = AD.city_id
join country CO on CO.country_id = C.country_id;

# 10. 영화 'AGENT TRUMAN'를 보유하고 있는 매장의 정보를 아래와 같이 출력함.
# 영화 제목, 매장ID, 매장staff first_name, 매장 staff last_name, 매장의 address, district, city, country, 해당 타이틀 "보유 수량"
select F.title as 영화제목, S.store_id as 매장ID, S.first_name as "매장 staff first_name", 
S.last_name as "매장 staff last_name", AD.address as "매장의 주소",
 AD.district, C.city , CO.country, count(F.title) as "보유수량"
from staff S join address AD on S.address_id = AD.address_id
join city C on C.city_id = AD.city_id
join country CO on CO.country_id = C.country_id
join store ST on ST.store_id = S.store_id
join inventory I on I.store_id = S.store_id
join film F on F.film_id = I.film_id
where title = "AGENT TRUMAN"
group by F.title, ST.store_id, S.first_name, S.last_name,
AD.address, AD.district, C.city, CO.country;

/*11. 영화 'AGENT TRUMAN'를 보유하고 있는 매장의 정보와 해당 타이틀의 
대여 정보를(대여 정가 없을 경우에는 관련 컬럼은 null 처리) 아래와 같이 출력함.
영화제목, 매장ID, 회수일자, 대여 고객의 first_name, last_name
매장ID, 매장staff first_name, 매장 staff last_name, 매장의 address, district, city, country
*/

# 내 답안
select  F.title as 영화제목, I.store_id as 매장ID, R.rental_date as 회수일자,
C.first_name as "대여 고객의 first_name", C.last_name as "대여 고객의 last_name"
from customer C join Rental R on C.customer_id = R.customer_id
join inventory I on I.inventory_id = R.inventory_id
join film F on F.film_id = I.film_id
where title = "AGENT TRUMAN";

select FL.title, ST.store_id, IV.inventory_id, AD.address, AD.district, CT.city,CU.country,
RT.rental_date as 회수일자, RT.return_date, CS.first_name as "대여 고객의 first_name", 
CS.last_name  as "대여 고객의 last_name"
from film FL join inventory IV on FL.film_id = IV.film_id
join store ST on IV.store_id = ST.store_id
join address AD on ST.address_id = AD.address_id
join city CT on CT.city_id = AD.city_id
join country CU on CU.country_id = CT.country_id
left join rental RT on IV.inventory_id = RT.inventory_id
join customer CS on RT.customer_id = CS.customer_id
where FL.title = "AGENT TRUMAN";

# 12. 대여된 영화 타이틀과 대여 횟수를 출력하시오.
# 함수를 만들어서 서브쿼리 실행
select F.title, sum(rental_info.rental_cnt)
from inventory IV join (select inventory_id, count(rental_id) as rental_cnt
from rental
group by inventory_id) as rental_info
on IV.inventory_id = rental_info.inventory_id
join film F on IV.film_id = F.film_id
group by F.title
order by sum(rental_info.rental_cnt) desc;

# 13. 고객의 지불 정보를 총지불금액 내림차순, 다음과 같이 출력하세요.
# 고객의 customer_id, first_name, last_name,
# 총 지불금액, 고객의 주소 address, district, city, country
select CS.customer_id, CS.first_name, CS.last_name, sum(P.amount),
 AD.address, AD.district, CT.city, CU.country
from payment P join customer CS on P.customer_id = CS.customer_id
join address AD on CS.address_id = AD.address_id
join city CT on AD.city_id = CT.city_id
join country CU on CU.country_id = CT.country_id
group by CS.customer_id, CS.first_name, CS.last_name,
 AD.address, AD.district, CT.city, CU.country
order by sum(P.amount) desc;

#14. 총 지불 금액 별 고객 등급을 출력하고자 한다.
# 등급 구분과 출력 컬럼은 다음과 같다.
# A : 총 지불금액이 200이상
# B : 총 지불금액이 200미만 100이상
# C : 총 지불 금액이 100미만 고객
# 고객의 cutomer_id, first_name, last_name, 총 지불 금액, 등급
#출력 순서는 총 지불 금액이 많은 고객부터 출력
select CS.customer_id, CS.first_name, CS.last_name, sum(P.amount), 
CASE
WHEN (sum(P.amount) > 200) THEN'A'
WHEN (sum(P.amount) >= 100) THEN 'B'
ELSE 'C'
END AS customer_grade
from payment P join customer CS on P.customer_id = CS.customer_id
group by CS.customer_id, CS.first_name, CS.last_name
order by sum(P.amount) desc;

#15. DVD 대여 후 아직 반납하지 않은 고객정보를 다음의 정보로 출력한다.
#영화타이틀, 인벤토리ID, 매장ID, 고객의 first_name, last_name, 대여일자
# return data is null 로 표기
select FL.title, IV.inventory_id, ST.store_id, CS.first_name as "고객의 first_name",
CS.last_name  as "고객의 last_name", RT.rental_date as 대여일자
from rental RT join customer CS on RT.customer_id = CS.customer_id
join inventory IV on RT.inventory_id = IV.inventory_id
join store ST on IV.store_id = ST.store_id
join film FL on IV.film_id = FL.film_id
where RT.return_date is null;

# 16. '2005-08-01' 부터 '2005-08-15' 사이, Canada(country)
# Alberta(district) 주에서 대여한 영화의 타이틀 정보를 아래와 같이 출력하세요.
# 대여일, 영화 타이틀, 인벤토리ID, 매장ID, 메일 전체 주소 출력
select RT.rental_date as 대여일, FL.title as "영화 타이틀", IV.inventory_id, 
ST.store_id
from rental RT join inventory IV on RT.inventory_id = IV.inventory_id
join store ST on IV.store_id = ST.store_id
join film FL on IV.film_id = FL.film_id
join address AD on ST.address_id = AD.address_id
join city CT on AD.city_id = CT.city_id
join country CU on CU.country_id = CT.country_id
where rental_date between '2005-08-01' and '2005-08-15'
and CU.country = 'Canada' 
and  AD.district = 'Alberta';

# 17. 도시별 'Horror' 영화 대여정보를 알고자한다.
# 도시와 대여수를 출력하라. 대여수 내림차순, 도시명 오름차순으로 정렬하시오.
select CT.city, count(FL.title)
from film FL join film_category FC on FL.film_id = FC.film_id
join inventory IV on FL.film_id = IV.film_id
join rental RT on IV.inventory_id = RT.inventory_id
join customer CS on RT.customer_id = CS.customer_id
join address AD on CS.address_id = AD.address_id
join city CT on AD.city_id = CT.city_id
join country CU on CT.country_id = CU.country_id
join category CA on FC.category_id = CA.category_id
where CA.name = 'Horror'  
group by CT.city # 제발 까먹지 말기,,,, group by.. 도시별...
order by count(FL.title) desc, CT.city;

# 18. 각 store 별 총 대여금액을 출력하세요 
select ST.store_id, sum(PM.amount)
from payment PM join rental RT on RT.rental_id = PM.rental_id
join inventory IV on RT.inventory_id = IV.inventory_id
join store ST on IV.store_id = ST.store_id
group by ST.store_id;

select count(*) from rental; # 전체 rental 수

/* 19. 대여된 영화 중에서 대여기간이 연체된 건을 다음의 정보로 조회하시오.
영화 타이틀, inventory_id, 대여일, 반납일, 기준대여기간, 실제대여기간
** 아직 반납이 되지 않은 경우 (이미 기준 대여기간을 넘고), 실제 대여기간 컬럼에 'Unknown' 출력
** 미반납일 경우 현재까지 기준으로 연체 판단 */
select FL.title, IV.inventory_id, RT.rental_date as 대여일, RT.return_date as 반납일, FL.rental_duration,
IFNULL(DATEDIFF(RT.return_date, RT.rental_date), 'Unknown') as '실 대여기간'
from rental RT join inventory IV on IV.inventory_id = RT.inventory_id
join film FL on IV.film_id = FL.film_id
where DATEDIFF(IFNULL(RT.return_date, curdate()), RT.rental_date) > FL.rental_duration;