Sakila DB를 활용한 연습

Sakila DB

Sakila DatdBase는 MySQL에서 제공하는 샘플 DB로, 영화 대여 가게를 모델링한 DB.

강의 시간에 이 Sakila DB로 총 20문제 정도를 풀었고 그에 대한 일부 기록을 남겨본다!
왜 일부냐면 이제 강의실에 갈 수 없어서...(강의실 컴퓨터로 수업을 들었다 ㅜㅜ)

1. 고객_id, 해당 고객이 지불한 총 금액을 출력.

select customer_id, sum(amount)
from payment
group by customer_id;

2. 위 커리에서 고객ID대신 고객의 이름과 성을 출력하시오.

select first_name, last_name, sum(amount)
from payment PM join customer CS on PM.customer_id = CS.customer_id
group by first_name, last_name;

2-1. 위 문제에서 추가하여 이름, 금액, 성, 이름도 출력하시오.

select concat(CS.last_name, ',', CS.first_name) as 이름,
sum (PM.amount)as 금액
from payment PM join customer CS on PM.customer_id = CS.customer_id
group by concat(CS.last_name,',', CS.first_name);

3. 고객 ID, 총 렌탈 건(렌탈 건이 많은 고객부터 출력) 출력.

select customer_id, count(*)
from rental
group by customer_id
order by count(*) desc;

4. 고객 ID대신 이름으로 출력.

select concat(CS.last_name, ',', CS.first_name) as 이름,
count(*) as 건수
from rental PM join customer CS on PM.customer_id = CS.customer_id
group by concat(CS.last_name,',', CS.first_name)
order by count(*) desc;

5. 장르가 'action'인 영화의 장르를 추출하시오.

• 필름번호. 제목, 설명, 장르명, 출시년도, 언어명

select * from film_category;
select FL.film_id as 필름번호, FL_title as 제목, FL.description as 설명,
FL.relase_year as 발매년도, LG.name as 언어
from film as FL join film_category as FC on FL.film_id = FC.film_id
join category as CG on FC.category_id = CG.category_id
join language as LG on FL.language_id = LG.language_id
where CG.name = 'action';

6. 출연작이 많은 순으로 배우의 first_name, last_name, 작품수 출력(작품 수가 많은 건 부터 출력).

select * from actor_id;
select A.last_name, A.first_name, count(FA.film_id) as '작품명'
from film_actor FA join actor A
on FA.actor_id = A.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(actor_id)
from actor
group by first_name, last_name
having count(actor_id) >1;

7. MARY MEITEL의 출연작을 영화제목 오름차순으로 출력하시오.

• 출력 칼럼은 다음과 같다.
• first_name, last_name, 영화제목, 출시년도, 대여 비용.

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 fa.film_id = f.film_id
WHERE a.first_name = 'MARY' AND a.last_name = 'KEITEL'
ORDER BY f.title desc;

8. 배우의 'R' 등급 영화 작품 수를 카운트하여 가장 많은 작품수를 가진 배우부터 출력하시오.

• 출력 칼럼은 다음과 같습니다. actor_id, first_name, last_name, 'R'등급 작품 수.

SELECT a.actor_id,
       a.first_name,
       a.last_name,
       COUNT(f.film_id) AS R등급작품수
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.rating = 'R'
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY R등급작품수 DESC;

9. 'R' 등급 영화에 출연한 적이 없는 배우의 first_name, last_name. 출연영화제목, 출시년도를 출력하시오.
   -- 단, 출시년도는 오름차순으로 출력하시오.

SELECT
    a.first_name,
    a.last_name,
    f.title AS 영화제목,
    f.release_year AS 출시년도
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
WHERE a.actor_id NOT IN (
        SELECT a.actor_id
        FROM actor a
        JOIN film_actor fa ON a.actor_id = fa.actor_id
        JOIN film f ON fa.film_id = f.film_id
        WHERE f.rating = 'R'
    )
ORDER BY f.release_year ASC;

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

🗝️ 1번 풀이
SELECT
    f.title AS 영화제목,
    s.store_id AS 매장ID,
    mgr.first_name AS 매니저_first_name,
    mgr.last_name AS 매니저_last_name,
    a.address AS 매장_address,
    a.district AS 매장_district,
    c.city AS 매장_city,
    co.country AS 매장_country,
    COUNT(i.inventory_id) AS 보유수량
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN store s ON i.store_id = s.store_id
JOIN staff mgr ON s.manager_staff_id = mgr.staff_id
JOIN address a ON s.address_id = a.address_id
JOIN city c ON a.city_id = c.city_id
JOIN country co ON c.country_id = co.country_id
WHERE f.title = 'AGENT TRUMAN'
GROUP BY f.title, s.store_id, mgr.first_name, mgr.last_name, a.address, a.district, c.city, co.country
ORDER BY s.store_id;

🗝️ 2번 풀이
DROP PROCEDURE if exists SP_GET_STORE;
	delimiter $$
	
	CREATE PROCEDURE SP_GET_STORE (
		IN IN_TITLE TEXT
	)
	BEGIN
			select 
            	FL.title, 
                ST.store_id, 
                SF.first_name, 
                SF.last_name,
				concat(AD.address, ' ', AD.district, ' ', CT.city, ' ', CU.country) address,
				count(FL.title) cnt
	        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.store_id = SF.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
			where FL.title = IN_TITLE
			group by FL.title, ST.store_id, SF.first_name, SF.last_name, AD.address
			, AD.district, CT.city, CU.country;
	END $$
	delimiter ;
    
    call SP_SET_STORE('AGENT TRUMAN');

11. 영화 'ALI FOREVER'을 보유하고 있는 매장의 정보와 해당 타이틀의 대여 정보를(대여 정보가 없는 경우 관련 컬럼은 NULL 처리 함.) 아래와 같이 출력함.

• 영화제목, 매장ID, 인벤토리ID, 매장의 address, district, city, country, 대여 일자, 회수 일자, 대여고객의 first_name과 last_name.

SELECT
    f.title AS 영화제목,
    s.store_id AS 매장ID,
    i.inventory_id AS 인벤토리ID,
    a.address AS 매장_address,
    a.district AS 매장_district,
    c.city AS 매장_city,
    co.country AS 매장_country,
    r.rental_date AS 대여_일자,
    r.return_date AS 회수_일자,
    cu.first_name AS 대여고객_first_name,
    cu.last_name AS 대여고객_last_name
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN store s ON i.store_id = s.store_id
JOIN address a ON s.address_id = a.address_id
JOIN city c ON a.city_id = c.city_id
JOIN country co ON c.country_id = co.country_id
LEFT JOIN rental r ON i.inventory_id = r.inventory_id
LEFT JOIN customer cu ON r.customer_id = cu.customer_id
WHERE f.title = 'ALI FOREVER'
ORDER BY s.store_id, i.inventory_id, r.rental_date;

12. 대여된 영화 타이틀과 대여회수를 출력하시오(대여 회수 내림차순)

SELECT
    f.title AS 영화제목,
    COUNT(r.rental_id) AS 대여회수
FROM rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY f.title
ORDER BY 대여회수 DESC;

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

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(p.amount) AS total_amount,
    a.address,
    a.district,
    ci.city,
    co.country
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name,
    a.address,
    a.district,
    ci.city,
    co.country
ORDER BY total_amount DESC;