# 2005년 8월에 각 스태프 멤버가 올린 매출을 스태프 멤버는 Staff Member 항목으로, 매출은 Total Amount 항목으로 출력
SELECT
s.staff_id AS 'Staff Member',
SUM(amount) AS 'Total Amount' -- 스태프 이름 쓰기
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE payment_date LIKE '%2005-08%'
GROUP BY s.staff_id;
# 각 카테고리의 평균 영화 러닝타임이 전체 평균 러닝타임보다 큰 카테고리들과 카테고리명과 해당 카테고리의 평균 러닝타임을 출력
SELECT c.name, AVG(length)
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
HAVING AVG(length) > -- 각 카테고리의 평균 영화 러닝타임
(SELECT AVG(length) FROM film); -- 전체 평균 러닝타임
# 각 카테고리별 평균 영화 대여 시간을 출력하세요
SELECT
c.name,
AVG(TIMESTAMPDIFF(HOUR, r.rental_date, r.return_date)) AS rent_duration -- ⭐평균 영화 대여 시간
FROM rental r
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name;
/* DATE TYPE FORMAT
TIMESTAMPDIFF(unit, datatime_expr1, datetime_expr2)
unit: YEAR, MONTH, DAY, HOUR, MINUTE -- 모든 시간 타입을 사용할 수 있다!
*/
/* 새로운 임원이 부임했습니다. 총 매출액 상위 5개 장르의 매출액을 수시로 확인하고자 합니다.
Total Sales (각 장르별 총 매출액) 과 Genre (각 장르 이름) 으로 해당 데이터를 수시로 확인할 수 있는 view 를
top5_genres 로 만들고, 현재까지의 상위 5 장르의 매출액을 출력해주세요 */ -- 현업에서 많은 형태, (보고용) 태블로로 시각화도 많이 한다
CREATE VIEW top5_genres AS -- 뷰 생성
SELECT
c.name AS Genre,
SUM(p.amount) AS 'Total Sales'
FROM payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film_category fc ON fc.film_id = i.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY SUM(p.amount) DESC
LIMIT 5;
SELECT * FROM top5_genres; -- 생성된 뷰 출력
/* 뷰를 삭제할 땐
DROP VIEW top5_genres;
*/
# 2005년 5월에 가장 많이 대여된 영화를 찾으세요. 영화 제목과 대여 횟수를 보여주세요.
SELECT f.title, COUNT(*) AS cnt_rental
FROM rental r
JOIN inventory i ON i.inventory_id = r.inventory_id -- 오답 이유: 테이블 별칭 제대로 써주기
JOIN film f ON f.film_id = i.film_id -- ON절에 중복하거나 오타 주의
WHERE rental_date LIKE '2005-05%'
GROUP BY f.title
ORDER BY cnt_rental DESC
LIMIT 1;
# 대여된 적이 없는 영화를 찾으세요. -- outer join에 대한 이해 필요
SELECT f.title FROM film f
LEFT OUTER JOIN inventory i ON i.film_id = f.film_id -- 각 영화와 해당 영화의 재고 정보가 연결
LEFT OUTER JOIN rental r ON r.inventory_id = i.inventory_id -- 각 영화 복사본과 대여 기록이 연결
WHERE r.rental_id IS NULL; -- rental 테이블에 해당 영화 복사본에 대한 대여 기록이 없는 경우
# 평균 고객 지출보다 더 많이 지출한 고객을 찾으세요. 그들의 이름과 그들이 지출한 금액을 보여주세요. -- 다시 풀고 완벽히 이해! 특히 HAVING절
SELECT
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
SUM(p.amount) AS tot_spent
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY customer_name
HAVING tot_spent > ( -- 평균 고객 지출
SELECT AVG(sum_amount) -- 각 고객별 총지출의 평균 (112.531820)
FROM (
SELECT SUM(amount) as sum_amount
FROM payment
GROUP BY customer_id
) as sub_query -- 고객ID별 총지출을 하나의 테이블로 FROM절에서 사용
);
# 가장 많은 결제를 처리한 직원이 누구인지 찾으세요.
SELECT
CONCAT(s.first_name, ' ', s.last_name) AS staff_name,
COUNT(*) AS cnt_pay
FROM staff s
JOIN payment p ON s.staff_id = p.staff_id
GROUP BY s.staff_id -- 직원별로 그룹핑
ORDER BY cnt_pay DESC -- 직원별 결제 카운트를 내림차순 정렬
LIMIT 1;
# '액션' 카테고리에서 가장 높은 등급을 받은 상위 5개의 영화를 보여주세요.
SELECT title, rating -- NC-17이 가장 높은 등급
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
ORDER BY rating DESC -- 도메인 지식
LIMIT 5;
# 각 영화 영상등급별의 영화 대여기간(film.rental_duration)의 평균을 찾으세요.
SELECT rating, ROUND(AVG(rental_duration), 2) AS avg_rent
FROM film
GROUP BY rating;
# 매장 ID별 총 매출을 보여주는 뷰를 생성하세요.
CREATE VIEW total_revenue AS
SELECT st.store_id, SUM(amount) AS tot_revenue
FROM staff s
JOIN payment p ON p.staff_id = s.staff_id
JOIN store st ON st.store_id = s.store_id
GROUP BY st.store_id;
SELECT * FROM total_revenue;
/* DROP VIEW total_revenue */
# 가장 많은 고객이 있는 상위 5개 국가를 보여주세요.
SELECT country, COUNT(*) AS cnt_customer
FROM country c
JOIN city ct ON ct.country_id = c.country_id
JOIN address a ON a.city_id = ct.city_id
JOIN customer cu ON cu.address_id = a.address_id
GROUP BY country -- 국가별로
ORDER BY cnt_customer DESC -- 고객수대로 정렬
LIMIT 5;
