[비트교육센터] MySql 22일차 ERD 실습 추가문제

1. ERD(feat. sakila 데이터를 이용한 실습예제)

https://julian5383.tistory.com/187

[비트교육센터] MySql 21일차 DDL, ERD

- 해당 게시물의 마지막 부분부터 이어진다.

1) 실습문제2

use sakila;

- sakila 데이터베이스를 이용한다.

[1] 

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

-- 나
select a.first_name, a.last_name, f.title, f.release_year, f.rental_rate
from film_actor fa
-- erd 구조를 보면 actor, film 테이블이 film_actor 테이블을 공동 매개체로 삼는다.

join actor as a on a.actor_id = fa.actor_id
join film as f on f.film_id = fa.film_id
-- film_actor 테이블을 기준으로 film, film_actor 테이블을 조인한다.

where a.first_name = 'mary' and a.last_name = 'keitel' 
-- mary keitel 이름을 조건절로 잡는다.

order by f.title;
-- 영화 제목순으로 오름차순으로 나열한다.

-- 선생님
select AC.first_name, AC.last_name, FL.title, FL.release_year, FL.rental_rate
from actor AC join film_actor FA on AC.actor_id = FA.actor_id
join film FL on FA.film_id = FL.film_id
where AC.first_name = 'MARY' and AC.last_name = 'KEITEL'
order by FL.title;

 

--> 결과(나)

--> 결과(선생님)

[2] 

-- 배우의 'R' 등급 영화 작품 수를 카운트하여, 가장 많은 작품수를 가지는 배우부터 출력하시오, 출력 컬럼은 다음과 같다.
-- actor_id, first_name, last_name, 'R'등급 작품 수

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

 

-- 나
select A.actor_id, A.first_name, A.last_name, count(*)
from actor A
-- 테이블 actor에서 데이터를 가져온다.

join film_actor FA on A.actor_id = FA.actor_id
join film FL on FA.film_id = FL.film_id
-- film_actor, film 테이블을 조인한다.

where FL.rating = 'R'
-- where 조건은 R등급 작품이다.

group by A.actor_id, A.first_name, A.last_name
-- 집계함수 count를 쓰기 때문에 group by를 해준다.

order by count(*) desc;
-- 가장 많은 작품수를 가지는 배우부터 출력한다.

-- 선생님
select AC.actor_id, AC.first_name, AC.last_name, count(FL.title)
from film FL join film_actor FA on FL.film_id = FA.film_id
join actor AC on AC.actor_id = FA.actor_id
where FL.rating = 'R'
group by AC.actor_id, AC.first_name, AC.last_name
order by count(FL.title) desc;

--> 결과(나)

--> 결과(선생님)

[3] 

-- 'R' 등급 영화에 출연한 적이 없는 배우의

-- first_name, last_name, 출연영화제목, 출시년도를 순으로 출력하시오

-- 나
select AC.first_name, AC.last_name, FL.title, FL.release_year, FL.rating
from film FL
-- 테이블 film에서 값을 가져온다.
join film_actor FA on FL.film_id = FA.film_id
join actor AC on AC.actor_id = FA.actor_id
-- film_actor, actor테이블을 조인한다.
where not FL.rating = 'R';
-- R등급이 아닌 영화를 조건으로 한다.

-- 선생님
select AC.first_name, AC.last_name, FL.title, FL.release_year
from film FL
-- film 테이블에서 값을 가져온다.
join film_actor FA on FL.film_id = FA.film_id
join actor AC on AC.actor_id = FA.actor_id
-- film_actor, actor 테이블과 조인한다.
where AC.actor_id not in (select FA.actor_id
	from film FL
    join film_actor FA on FL.film_id = FA.film_id
	where FL.rating = 'R')
-- 서브쿼리를 선언해 R등급을 찍었던 배우목록을 가져온다.
order by FL.release_year;
-- 출연연도 순으로 정렬한다.

--> 결과(나)

- 이번 문제는 서브쿼리를 제대로 이해하지 못해서 틀렸다.

--> 결과(선생님)

[4]

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

-- 나
select fl.title, st.store_id, st.first_name, st.last_name, ad.address, ad.district,c.city,co.country,count(*)-- 여담으로 address2에는 아무것도 없다.
from address AD
join staff ST on AD.address_id = ST.address_id
join city C on C.city_id = AD.city_id
join country CO on C.country_id = CO.country_id
join store S on S.store_id = ST.store_id
join inventory I on I.store_id=S.store_id
join film FL on FL.film_id = I.film_id
-- 각 테이블끼리 참조된 키를 기준으로 묶는다.

where fl.title='AGENT TRUMAN'
-- 이중 영화 agent truman을 보유한 조건을 선언한다.

group by fl.title, st.store_id, st.first_name, st.last_name, ad.address, ad.district, c.city, co.country;
-- 집계함수(sum)를 선언해서 group by를 선언한다.

-- 선생님
select FL.title, ST.store_id, SF.first_name, SF.last_name, AD.address, 
AD.district, CT.city, CU.country, count(FL.title) as "보유수량"
from film FL join inventory IV on FL.film_id = IV.film_id

join store ST on IV.store_id = ST.store_id
join staff SF on ST.manager_staff_id = SF.staff_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 CT.country_id = CU.country_id

where FL.title = 'AGENT TRUMAN'

group by FL.title, ST.store_id, SF.first_name, SF.last_name, 
AD.address, AD.district, CT.city, CU.country;

- 해당 문제는 많은 테이블을 조인해야 했다. 그 과정에서 외래키를 잘못 읽어 잘못된 join을 해서 틀렸다.

select*from information_schema.key_column_usage where table_name = 'store';

- 스키마에 대한 정보를 가지고 있는 내장 데이터베이스이다.

- 내장 데이터베이스에서 key_column_usage를 이용했다.

- 이 코드로 어떤 데이터베이스에 어떤 제약조건이 걸려있고, 어떤 테이블과 칼럼에 어떤 제약조건이 걸려있고, 상대편의 테이블과 칼럼이 무엇인지를 알려준다.

 

--> 결과(나)

--> 결과(선생님)

[5]

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

-- 나
select fl.title,s.store_id,i.inventory_id,ad.address, ad.district, c.city, co.country,
r.rental_date, r.return_date, cu.first_name, cu.last_name
from address ad
join city c on ad.city_id = c.city_id
join country co on c.country_id = co.country_id
join store s on s.address_id = ad.address_id
join inventory i on i.store_id = s.store_id
join film fl on fl.film_id = i.film_id
join rental r on r.inventory_id = i.inventory_id
join customer cu on cu.customer_id = r.customer_id
where fl.title='agent truman';

-- 선생님
select FL.title, ST.store_id, IV.inventory_id, AD.address, AD.district, CT.city, CU.country, RT.rental_date, RT.return_date, CS.first_name, CS.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 AD.city_id = CT.city_id
join country CU on CT.country_id = CU.country_id
left join rental RT on IV.inventory_id = RT.inventory_id
-- 한번도 빌려간적이 없을 수 있어서 
-- NULL 이 나올 수 있어 left join을 했다.
join customer CS on RT.customer_id = CS.customer_id
where FL.title = 'AGENT TRUMAN';

--> 결과(나)

--> 결과(선생님)

[6]

-- 대여된 영화 타이틀과 대여회수를 출력하시오(대여 회수 내림차순)
-- 제목, 건수

-- 나
select f.title, count(*)
from rental rn
join inventory i on i.inventory_id = rn.inventory_id
join film f on f.film_id = i.film_id
group by title
order by count(*) desc;

-- 선생님
select FL.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 FL on IV.film_id = FL.film_id
group by FL.title
order by sum(rental_info.rental_cnt) desc;

--> 결과(나)

--> 결과(선생님)

- 서브쿼리를 선언해 inventory_id별로 rental_id의 개수를 구하고 메인쿼리에서 제목에 따라서 rental_id를 합하였다.

[7]

-- 고객의 지불정보를 총지불금액 내림차순, 다음과 같이 출력하시오.
-- 고객의 customer_id, first_name, last_name, 총지불금액, 고객의 주소 address, district, city, country

-- 나
select cu.customer_id, cu.first_name, cu.last_name, sum(p.amount) as "총지불 금액", ad.address, ad.district, c.city, co.country
from customer cu
join payment p on cu.customer_id = p.customer_id
join address ad on ad.address_id = cu.address_id
join city c on c.city_id = ad.city_id
join country co on co.country_id = c.country_id
group by cu.customer_id, cu.first_name, cu.last_name,ad.address, ad.district, c.city, co.country
order by sum(p.amount) desc;

-- 선생님
select CS.customer_id, CS.first_name, CS.last_name, 
	sum(PM.amount), AD.address, AD.district
	, CT.city, CU.country
from payment PM join customer CS 
	on PM.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
group by CS.customer_id, CS.first_name, CS.last_name, 
	AD.address, AD.district, CT.city, CU.country
order by sum(PM.amount) desc;

--> 결과(나)

--> 결과(선생님)

[8]

-- 총 지불 금액별 고객 등급을 출력하고자 한다. 등급 구분과 출력 컬럼은 다음과 같다.
--  'A' : 총 지불금액이 200 이상
--  'B' : 총 지불금액이 200 미만 100 이상
--  'C' : 총 지불금액이 100 미만 고객
--  고객의 customer_id, first_name, last_name, 총 지불금액, 등급
-- 출력 순서는 총 지불금액이 많은 고객부터 출력

-- 나
select cu.customer_id, cu.first_name, cu.last_name, sum(p.amount) as "총지불 금액",
		case
        when sum(p.amount)>=200 then "A"
		when sum(p.amount)<200 and sum(p.amount)>=100 then "B"
		when sum(p.amount) <100 then "C"
		end "등급"
-- 총 지불금액에 따른 등급을 매긴다.
from customer cu
join payment p on cu.customer_id = p.customer_id
-- customer테이블과 payment 테이블을 조인해준다.
group by cu.customer_id, cu.first_name, cu.last_name
-- 집계함수 sum()이 있기 때문에 집계함수 이외의 칼럼을 group by해준다.
order by sum(p.amount) desc;
-- 총지불 금액에 따라 내림차순으로 정렬해준다.

-- 선생님
select CS.customer_id, CS.first_name, CS.last_name, 
	sum(PM.amount), 
        case when (sum(PM.amount) >= 200) then 'A'
	when (sum(PM.amount) >= 100) then 'B'
	else 'C' 
        end as customer_grade
from payment PM join customer CS 
on PM.customer_id = CS.customer_id
group by CS.customer_id, CS.first_name, CS.last_name
order by sum(PM.amount) desc;

--> 결과(나)

--> 결과(선생님)

[9]

-- DVD 대여 후 아직 반납하지 않은 고객정보를 다음의 정보로 출력한다.
-- 영화타이틀, 인벤토리ID, 매장ID, 고객의 first_name, last_name, 대여일자

-- 나
select fl.title, i.inventory_id, s.store_id, cu.first_name, cu.last_name, re.rental_date
from customer cu
join rental re on re.customer_id = cu.customer_id
join store s on cu.store_id = s.store_id
join inventory i on i.inventory_id = re.inventory_id
-- 이곳 조인에서 헷갈렸다.
-- inventory하고 rental을 조인해야 한다. 초반에 inventory와 store을 조인했다.
join film fl on fl.film_id = i.film_id
where re.return_date is null;
-- 대여일자가 NULL일때를 조건으로 한다.

-- 선생님
select FL.title, IV.inventory_id, IV.store_id, CS.first_name, CS.last_name
, RT.rental_date
from rental RT join customer CS on RT.customer_id = CS.customer_id
join inventory IV on RT.inventory_id = IV.inventory_id
join film FL on IV.film_id = FL.film_id
join store ST on IV.store_id = ST.store_id
where RT.return_date is null;

--> 결과(나)

--> 결과(선생님)

[10]

-- '2005-08-01' 부터 '2005-08-15' 사이, Canada(country) Alberta(district) 주에서 대여한 영화의 타이틀 정보를 아래와 같이 출력하시오
-- 대여일, 영화 타이틀, 인벤토리ID, 매장ID, 매장 전체 주소

-- 나
select rt.rental_date, fl.title, iv.inventory_id, s.store_id, ad.address
from rental rt
-- rental 테이블에서 값을 가져온다.
join inventory iv on iv.inventory_id = rt.inventory_id
join store s on s.store_id = iv.store_id
join film fl on fl.film_id = iv.film_id
join address ad on ad.address_id = s.address_id
join city c on ad.city_id = c.city_id
join country co on co.country_id = c.country_id
-- 다른 테이블과 조인한다.
where rt.rental_date between '2005-08-01' and '2005-08-15' and co.country='Canada' and ad.district = 'Alberta';
-- between and를 선언해 기간범위를 설정하고 나라와 주를 설정해주면 된다.

-- 선생님
select RT.rental_date, FL.title, IV.inventory_id, IV.store_id, AD.address, AD.district, CT.city, CU.country
from rental RT join inventory IV on RT.inventory_id = IV.inventory_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 AD.city_id = CT.city_id
join country CU on CT.country_id = CU.country_id
join film FL on IV.film_id = FL.film_id
where rental_date between '2005-08-01' and '2005-08-15'
and AD.district = 'Alberta'
and CU.country = 'Canada';

--> 결과(나)

--> 결과(선생님)

[11]

-- 도시별 'Horror' 영화 대여정보를 알고자 한다. 도시와 대여수를 출력하라. 대여수 내림차순, 도시명 오름차순으로 정렬하시오.

-- 나
select ci.city, count(rt.rental_id)
from rental rt
join customer c on c.customer_id = rt.customer_id
join address ad on ad.address_id = c.address_id
join city ci on ci.city_id = ad.city_id

join inventory i on i.inventory_id = rt.inventory_id
join film fl on fl.film_id = i.film_id
join film_category fc on fc.film_id = fl.film_id
join category ct on ct.category_id = fc.category_id
-- 테이블을 조인해준다.

where ct.name = 'Horror'
-- 'Horror'영화 조건으로 설정해준다.
group by ci.city
-- 집계함수 count를 이용해서 city를 group by해준다.
order by count(rt.rental_id) desc, ci.city;
-- 대여수는 내림차순, 도시명은 오름차순으로 정렬한다.
-- 반드시 순서를 지켜야한다.

-- 선생님
select CT.city, count(FL.title)
from film FL join film_category FC on FL.film_id = FC.film_id
join category CG on FC.category_id = CG.category_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
where CG.name = 'Horror'
group by CT.city
order by count(FL.title) desc, CT.city;

--> 결과(나)

- 여담으로 order by ct.city asc and count(fl.title)라고 적어서 문제를 틀렸다.

- 문제에 나온대로 order by의 순서를 지켜야한다.

 

--> 결과(선생님)

[11]

-- 각 store 별 총 대여금액을 출력하시오.

-- 나
select ad.address, sum(pm.amount)
from address ad
join store st on st.address_id = ad.address_id
join inventory iv on iv.store_id = st.store_id
join rental rn on rn.inventory_id = iv.inventory_id
join payment pm on pm.rental_id = rn.rental_id
group by address;

-- 선생님
select ST.store_id, sum(amount)
from rental RT join payment PM 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;

--> 결과(나)

- 선생님께서는 store테이블에서 store_id값을 이용하셨지만 필자는 address테이블에서 address 칼럼을 이용했다.

 

--> 결과(선생님)

[12]

-- 대여된 영화 중 대여기간이 연체된 건을 다음의 정보로 조회하시오.
-- 영화타이틀. inventory_id, 대여일, 반납일, 기준대여기간, 실제대여기간
-- ** 아직 반납이 되지 않은 경우(이미 기준대여기간을 넘고), 실제대여기간 컬럼에 'Unknown' 출력
-- ** 미반납일 경우 현재까지를 기준으로 연체 판단

 

1) mysql rows 개수 제한 해제하는 방법

- Edit -> Preferences로 들어간다.

- SQL Execution에 들어가서 Limit Rows를 해제시키고 OK를 누른다.

- 그 다음 mysql workbench를 재접한다.

 

2)

-- 나
select fl.title, rt.inventory_id, rt.rental_date, rt.return_date, fl.rental_duration, ifnull(datediff(rt.return_date,rt.rental_date)+1,'Unknown')
-- datediff를 선언해 반납일에 대여일을 빼서 실제대여기간을 계산해준다.
-- 반납을 하지 않을경우 return_date에 null로 입력되기 때문에 ifnull을 선언해 null일경우 'Unknown'을 출력한다.
from film fl
join inventory iv on fl.film_id = iv.film_id
join rental rt on rt.inventory_id = iv.inventory_id
-- 필요한 테이블을 조인한다.
where datediff(ifnull(rt.return_date,curdate()),rt.rental_date) > rental_duration;
-- 조건절에는 연체일 경우를 입력한다. 실제 대여기간이 기존대여시간보다 길면 연체이다.
-- datediff를 선언해 두 날짜의 차이를 계산하면 된다.
-- 그러나 return_date에 null값이 있으므로 ifnull을 이용해 만약 null을 있을경우 오늘날짜(curdate())로 바꾸어 계산한다.


-- 선생님
select FL.title, IV.inventory_id, RT.rental_date, RT.return_date, FL.rental_duration, IFNULL(DATEDIFF(RT.return_date, RT.rental_date), 'Unknown') as '실 대여기간'
from rental RT join inventory IV on RT.inventory_id = IV.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;

--> 결과(나)

--> 결과(선생님)