Chapter 7-38. Mini Project : 06.Sakila SQL(2/2)

 

📌 Sakila SQL 실전 분석 – 집계, 서브쿼리, 데이터 수정까지 총정리!

MySQL에서 제공하는 Sakila 샘플 데이터베이스를 활용해 다양한 실전 SQL 문제를 풀어봅니다. 이 글에서는 크게 세 가지 영역으로 나누어 정리했습니다.

1. 📊 집계 및 그룹화 쿼리
2. 🧠 서브쿼리 및 고급 기능
3. 🛠 데이터 수정 및 관리

---

📊 1. 집계 및 그룹화

GROUP BY + 집계 함수 (COUNT, AVG, SUM 등) 를 사용해 데이터를 그룹화하여 인사이트를 도출하는 기본이자 핵심 SQL 영역입니다.

✅ 가장 많이 대여된 영화 조회

• film → inventory → rental의 흐름을 따라 각 영화별 대여 횟수를 계산합니다.
• GROUP BY를 통해 영화 제목별로 그룹화하고, COUNT()로 대여 횟수를 계산합니다.

SELECT f.title, COUNT(r.rental_id) as rental_count
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.title
ORDER BY rental_count DESC
LIMIT 1;

✅ 각 카테고리별 평균 대여 요금 조회

• category → film_category → film의 흐름으로 조인합니다.
• AVG(f.rental_rate)로 카테고리별 평균 요금을 구합니다.

SELECT c.name, AVG(f.rental_rate) as average_rental_rate
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name;

✅ 월별 총 매출 조회

• payment 테이블에서 날짜별 데이터를 추출하고, 연도와 월로 그룹화합니다.
• SUM(amount)로 매출 합산.

SELECT YEAR(p.payment_date) as year, MONTH(p.payment_date) as month, SUM(p.amount) as total_sales
FROM payment p
GROUP BY YEAR(p.payment_date), MONTH(p.payment_date);

✅ 각 배우별 출연한 영화 수 조회

• actor → film_actor 조인으로 배우별 영화 수를 카운트합니다.

SELECT a.first_name, a.last_name, COUNT(fa.film_id) as number_of_films
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.first_name, a.last_name;

---

🧠 2. 서브쿼리 및 고급 기능

복잡한 조건을 처리하거나 계산된 결과를 바탕으로 데이터를 추출할 때 사용합니다.

✅ 가장 수익이 많은 영화 조회

• film → inventory → rental → payment 흐름으로 매출을 집계합니다.

SELECT f.title, SUM(p.amount) AS total_revenue
FROM film f
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY f.title
ORDER BY total_revenue DESC
LIMIT 1;

✅ 평균 대여 요금보다 높은 요금의 영화 조회

• film 테이블에서 AVG()를 서브쿼리로 구한 후, 필터 조건으로 사용합니다.

SELECT f.title, f.rental_rate
FROM film f
WHERE f.rental_rate > (SELECT AVG(rental_rate) FROM film);

✅ 가장 활동적인 고객 조회

• rental → customer 기준으로 고객별 대여 횟수를 집계하고, 최다 대여 고객을 추출합니다.

SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY rental_count DESC
LIMIT 1;

✅ 특정 배우가 출연한 영화 중 가장 인기 있는 영화 조회

• actor → film_actor → film → inventory → rental로 연결하여 배우 기준 대여 횟수를 계산합니다.

SELECT f.title, COUNT(r.rental_id) AS rental_count
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
JOIN inventory i ON f.film_id = i.film_id
JOIN rental r ON i.inventory_id = r.inventory_id
WHERE a.first_name = 'PENELOPE' AND a.last_name = 'GUINESS'
GROUP BY f.title
ORDER BY rental_count DESC
LIMIT 1;

---

🛠 3. 데이터 수정 및 관리

INSERT / UPDATE / DELETE로 실제 데이터를 수정하는 실무에 필수적인 SQL 명령어입니다.

✅ 새로운 영화 추가

• film 테이블에 새로운 레코드를 추가하는 기본 INSERT 문.

INSERT INTO film (
  title, description, release_year, language_id,
  rental_duration, rental_rate, length, replacement_cost,
  rating, special_features
)
VALUES (
  'New Adventure Movie', 'A thrilling adventure of the unknown',
  2023, 1, 3, 4.99, 120, 19.99, 'PG', 'Trailers,Commentaries'
);

✅ 고객 정보 업데이트

• 고객 테이블에서 특정 ID를 가진 사용자의 주소를 업데이트.

UPDATE customer
SET address_id = (
  SELECT address_id FROM address WHERE address = '123 New Address, New City'
)
WHERE customer_id = 5;

✅ 영화 대여 상태 변경

• rental 테이블에서 특정 rental_id의 반납일을 현재 시각으로 설정.

UPDATE rental
SET return_date = NOW()
WHERE rental_id = 200;

✅ 배우 정보 삭제

• actor_id로 특정 배우를 삭제. (관련된 film_actor 정리 선행 필요)

DELETE FROM actor
WHERE actor_id = 10;

---

📌 마무리 요약

• Sakila DB는 집계, 조인, 서브쿼리, 데이터 수정까지 실전 SQL을 학습하기에 최적의 샘플입니다.
• 문제를 직접 풀면서 JOIN 흐름, 조건 필터링, 집계 함수, 데이터 조작 등 전반적인 SQL 실력을 높일 수 있어요.
• 실무 SQL의 기본기를 탄탄히 다지고 싶다면 Sakila DB는 필수 코스입니다! 🚀

💡 더 나아가려면?

• HAVING, CASE, WITH(CTE), 윈도우 함수 도 연습해보세요!
• 다음은 트리거, 뷰, 인덱스, 성능 튜닝 관련 실습으로 확장 가능합니다 🙌