36일차 - SQL 코딩테스트 연습(sakila data)

Date : 2023-08-08

Topic : 배운 문법들을 활용하여 sakila 데이터로, 실전처럼 SQL 코딩테스트 문제 난이도별 풀어보기 (같은 문제도 여러 방법 사용해보기)

---

Note

SQL 코딩테스트 연습

• 기본 문제

/* category 테이블에서 "Comedy", "Sports", "Family" 카테고리의 category_id 알아내기
(category_id 와 카테고리명 출력하기) */ -- 실제 코테는 이런 조건 꼭 정확히 숙지하고 쿼리 작성!!
SELECT category_id, name FROM category
WHERE name IN ('Comedy', 'Sports', 'Family'); -- OR 연산자 사용 시, tab 활용(기니까)

# film_category 테이블에서 영화 아이디(film_id)가 2 인 영화의 카테고리 ID 알아내기
SELECT category_id FROM film_category
WHERE film_id=2;

# film_category 테이블에서 카테고리 ID별 영화 수 알아내기
SELECT category_id, COUNT(*) AS cnt_film
FROM film_category
GROUP BY category_id;

/* 카테고리가 Comedy 인 영화 수 알아내기
(category 테이블에는 카테고리 이름과 category_id, film_category 테이블에는 category_id와 각 영화 id가 있음) */
# JOIN 사용
SELECT COUNT(*) AS cnt_comedy
FROM category c
INNER JOIN film_category fc
ON c.category_id=fc.category_id
WHERE name='Comedy';

# 서브쿼리 사용
SELECT COUNT(*) FROM film_category
WHERE category_id = (				 -- WHERE IN(또는 =) 구문으로 연관 있는 컬럼끼리 연결(중요)
	SELECT category_id FROM category
	WHERE name = 'Comedy'
	); 

/* Comedy, Sports, Family 각각의 카테고리별 영화 수 알아내기 (JOIN 사용하기)
(category 테이블에는 카테고리 이름과 category_id, film_category 테이블에는 category_id와 각 영화 id가 있음) */
SELECT c.name, COUNT(*) FROM film_category fc
JOIN category c ON c.category_id = fc.category_id
WHERE name IN ('Comedy', 'Sports', 'Family')
GROUP BY c.name

# 카테고리에 포함되는 각각의 영화 수가 70 이상인 카테고리 출력하기
SELECT c.name, COUNT(*) FROM category c
JOIN film_category fc ON fc.category_id = c.category_id
GROUP BY c.name
HAVING COUNT(*) >= 70

/* 각 카테고리에 포함된 영화들의 렌탈 횟수 구해보기
(각 카테고리별에 포함된 영화들의 총 렌탈 횟수와 각 카테고리명을 출력하는 것이 최종 목표)
- rental 테이블에 렌탈 기록이 있음
- inventory 테이블에 물품현황과 해당 물품(DVD)에 들어 있는 영화 아이디가 있음
- film_category 테이블에 영화 아이디에 매칭되는 카테고리 아이디가 있음
- category 테이블에 카테고리 아이디에 매칭되는 카테고리명이 있음 */
SELECT c.name, COUNT(*)	-- 각 카테고리별 카운트 = 렌탈 횟수
FROM rental r
JOIN inventory i ON i.inventory_id = i.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.category_id;

/* "Comedy", "Sports", "Family" 카테고리에 포함되는 영화들의 렌탈 횟수 출력하기 (카테고리명, 렌탈 횟수) */
SELECT c.name, COUNT(*)	-- 각 카테고리별 카운트 = 렌탈 횟수
FROM rental r
JOIN inventory i ON i.inventory_id = i.inventory_id
JOIN film_category fc ON fc.film_id = i.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name IN ('Comedy', 'Sports', 'Family')	-- 구하고자 하는 카테고리 조건 걸기
/*WHERE category.name = 'Comedy' OR
	category.name = 'Sports' OR
        category.name = 'Family' */
GROUP BY c.category_id;

# 카테고리가 Comedy 인 데이터의 렌탈 횟수 출력하기 (join으로 푸는게 더 효율적이지만 연습용으로 서브쿼리 사용)
SELECT COUNT(*) FROM rental r	-- 렌탈 횟수 카운트
WHERE inventory_id IN	-- 카테고리ID가 'Comedy'인 film_id를 인벤ID로 join해서
	(SELECT inventory_id FROM inventory WHERE film_id IN	-- 'Comedy' 카테고리 film_id가 인벤토리ID
		(SELECT film_id FROM film_category WHERE category_id IN	-- 카테고리ID가 'Comedy'인 film_id
			(SELECT category_id FROM category WHERE name = 'Comedy') -- 카테고리ID가 'Comedy'
		)
	);

# 카테고리가 Comedy 인 데이터의 영화 갯수 출력하기 (JOIN 문법으로 작성해보세요)
SELECT COUNT(*) FROM category c
INNER JOIN film_category fc
ON c.category_id = fc.category_id
WHERE name = 'Comedy';

# 카테고리가 Comedy 인 데이터의 영화 갯수 출력하기 (서브쿼리 문법으로 작성해보세요)
SELECT COUNT(*) FROM film_category
WHERE category_id IN (
    SELECT category_id FROM category
    WHERE name = 'Comedy'
    );

/* address 테이블에는 address_id 가 있지만, customer 테이블에는 없는 데이터의 갯수 출력 (RIGHT JOIN 사용)
OUTER JOIN 사용 시, 출력값에 맞게 기준이 테이블을 정하고 해당하는 조건을 설정할 때 주의(별칭이나 컬럼)*/
SELECT COUNT(*) FROM customer c
RIGHT JOIN address a
ON c.address_id = a.address_id
WHERE c.customer_id IS NULL; -- c테이블에 customer_id가 없는 조건
# `customer` 테이블에 대응되는 정보가 없는 `address` 테이블의 레코드 수를 세는 것

---

• 실전 문제 (난이도 up)

/* 캐나다 고객에게 이메일 마케팅 캠페인을 진행하고자 합니다.
캐나다 고객의 이름과, email 주소 리스트를 뽑아주세요 -- 딱 현업에서 이 느낌 */
SELECT first_name, last_name, email
FROM customer cu
INNER JOIN address ad ON cu.address_id = ad.address_id
INNER JOIN city ct ON ct.city_id = ad.city_id
INNER JOIN country ctr ON ctr.country_id = ct.country_id
WHERE country = "Canada" ;

# 젊은 가족 사이에서 매출이 저조해서, 모든 가족 영화를 홍보 대상으로 삼으려고 합니다. 가족 영화로 분류된 모든 영화 리스트를 뽑아주세요
SELECT title FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category ca ON ca.category_id = fc.category_id
WHERE ca.name = 'Family';

/* subquery + join
SELECT title
FROM film_category fc
JOIN film f ON fc.film_id = f.film_id
WHERE category_id = (
	SELECT category_id FROM category c
	WHERE name = 'Family'
    ); */

/* 가장 자주 대여하는 영화 리스트를 참고로 보고 싶습니다. 가장 자주 대여하는 영화 순으로 100개만 뽑아주세요
title (영화제목) 과 Rentals (렌탈 횟수) 로 보고 싶습니다. */
SELECT title, COUNT(*) AS Rentals
FROM rental r
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN film f ON f.film_id = i.film_id
GROUP BY title
ORDER BY Rentals DESC
LIMIT 100;

/* 각 스토어별로 매출을 확인하고 싶습니다. 관련 데이터를 출력해주세요
1) 스토어가 위치한 '도시, 국가' 를 Store 항목으로, 2) 스토어 ID 를 Store ID 로,
3) 각 스토어별 총 매출을 'Total Sales' 항목으로 출력해주세요 */ -- 다시 풀기
SELECT 
    CONCAT(CI.city, ', ', CO.country) AS 'Store', 
    ST.store_id AS 'Store ID', 
    SUM(PA.amount) AS 'Total Sales'
FROM payment PA
JOIN rental RE ON RE.rental_id = PA.rental_id
JOIN inventory INV ON INV.inventory_id = RE.inventory_id
JOIN store ST ON ST.store_id = INV.store_id
JOIN address AD ON AD.address_id = ST.address_id
JOIN city CI ON CI.city_id = AD.city_id
JOIN country CO ON CO.country_id = CI.country_id
GROUP BY ST.store_id;

/* 각 스토어의 스토어 ID, 도시, 및 국가를 알고싶습니다. 관련 데이터를 출력해주세요
store_id, city, country 로 보고 싶습니다. */

SELECT store_id, city, country
FROM store st
JOIN address a ON a.address_id = st.address_id
JOIN city ct ON ct.city_id = a.city_id
JOIN country c ON c.country_id = ct.country_id;

/* 가장 렌탈비용을 많이 지불한 상위 10명에게 선물을 배송하고자 합니다
가장 렌탈비용을 많이 지불한 상위 10명의 주소(address)와 이메일, 그리고 각 고객당 총 지불 비용을 출력해주세요 */
SELECT
	CONCAT(cu.first_name, ' ', cu.last_name) AS customer_name,
	a.address,
	cu.email,
	SUM(p.amount) AS tot_spent
FROM customer cu
JOIN payment p ON cu.customer_id = p.customer_id
JOIN rental r ON r.rental_id = p.rental_id
JOIN address a ON a.address_id = cu.address_id
GROUP BY cu.customer_id
ORDER BY tot_spent DESC
LIMIT 10;

/* 그룹화할 열이나 집계 함수를 정하지 않으면 데이터베이스가 그룹화할 방법을 알 수 없기 때문에 오류가 발생
따라서 GROUP BY절에서 'customer_name' 사용X, cu.customer_id라는 고객 아이디로 분류해주어야 한다. */

# actor 테이블의 배우 이름을 first name 과 last name 을 대문자로 Actor Name 항목으로 출력해주세요
SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS "Actor Name"
FROM actor;

/* 언어가 영어인 영화 중, 영화 타이틀이 K 와 Q 로 시작하는 영화의 타이틀만 출력*/
# JOIN 사용
SELECT title FROM language l
JOIN film f ON f.language_id = l.language_id
WHERE
	l.name = 'English' AND
	f.title LIKE 'K%' OR f.title LIKE 'Q%';
   
# SUBSTRING 활용
SELECT title FROM language l
JOIN film f ON f.language_id = l.language_id
WHERE
    l.name = 'English' AND
    (SUBSTRING(f.title, 1, 1) = 'K' OR SUBSTRING(f.title, 1, 1) = 'Q'); -- K나 Q로 시작되는 제목
# SUBSTRING(string, start_position, length)

# 서브쿼리 사용
SELECT title FROM film
WHERE language_id IN
    (SELECT language_id FROM language WHERE name = 'English')
    AND
    (title LIKE 'K%' OR title LIKE 'Q%');

/* Alone Trip 에 나오는 배우 이름을 모두 출력하세요
(배우 이름은 actor_name 항목으로 출력해주세요. 서브쿼리를 사용해보세요) */
# JOIN 사용
SELECT CONCAT(a.first_name, ' ', a.last_name) AS actor_name
FROM actor a
JOIN film_actor fa ON fa.actor_id = a.actor_id
JOIN film f ON f.film_id = fa.film_id
WHERE f.title = 'Alone Trip';

# 서브쿼리 사용
SELECT CONCAT(first_name, ' ', last_name) AS actor_name
FROM actor
WHERE actor_id IN
	(SELECT actor_id FROM film_actor
	WHERE film_id IN
		(SELECT film_id FROM film
		WHERE title = 'Alone Trip'))

---

8/8 수업 내용 문제를 2회독 해보니, 여러 형태 문제를 많이 풀어보는 것도 좋지만 복습하면서 같은 문제라도 반복적으로 풀어 보는 것이 개념 숙지 및 쿼리 작성 스킬 향상에 도움 되는 것이 눈에 띄게 체감된다. 특히 한 문제를 JOIN과 SUBQUERY를 모두 사용해보고 정답을 비교하며 쿼리 구조를 더 잘 이해할 수 있다. 확실히 각각 장단점이 있지만 여러 테이블을 참고해야 할 땐 조인을 사용하는 것이 훨씬 효율적이란 것을 자주 느꼈다. 그러다보니 무의식적으로 계속 조인만 사용하려고 하는데 배운 문법을 활용하면서 최대한 성능이 효율적이고 보기 좋은 쿼리를 작성할 수 있도록 많은 연습이 필요할 것 같다.

#가보자고