스파르타 내일배움캠프_본캠프_data11기 김선영_TIL_Day 6

<오늘의 학습>

• SQL 라이브세션 5회차 (CASE, ⭐️서브쿼리, ⭐️CTE)

---

<학습내용 정리>

# SQL 라이브 세션 5회차

1.  CASE

두 가지 케이스 크게 다르지 않음. 표기의 차이일 뿐.

else: 조건 외 다른 변수의 처리 방식을 지정해주는 것. 에러 방지에 중요함!

--(A) 값 비교형(Simple CASE)
SELECT
  student_id,
  student_name,
  region,
CASE region
	WHEN 'SEOUL' THEN '서울'
	WHEN 'BUSAN' THEN '부산'
	ELSE '기타'
	END AS region_kor
FROM basic.students
ORDER BY student_id;

--(B) 조건식형(Searched CASE)
SELECT
  student_id,
  student_name,
  region,
CASE
	WHEN region = 'SEOUL' THEN '서울'
	WHEN region = 'BUSAN' THEN '부산'
	ELSE '기타'
	END AS region_kor
FROM basic.students
ORDER BY student_id;

• case 내 / count 로 null인 행 개수

--SUM으로 Null 개수 카운트
--해당 조건을 만족하는 null 행을 1로 반환, 모든 1을 더하면 행의 개수
SELECT
COUNT(*)AS total_enrollments,
SUM(CASE WHEN coupon_code IS NOT NULL THEN 1 ELSE 0 END)AS coupon_used_cnt,
SUM(CASE WHEN coupon_code IS NULL THEN 1 ELSE 0 END)AS no_coupon_cnt
FROM basic.enrollments;

--0이 반환된 행까지 모두 카운트, count(*)와 동일한 값만 나옴.
SELECT
COUNT(*)AS total_enrollments,
COUNT(CASE WHEN coupon_code IS NOT NULL THEN 1 ELSE 0 END)AS coupon_used_cnt,
COUNT(CASE WHEN coupon_code IS NULL THEN 1 ELSE 0 END)AS no_coupon_cnt
FROM basic.enrollments;

 

2. ⭐️서브쿼리

:다른 SQL 안에 들어있는 SELECT

https://tjsdud7032.tistory.com/34

[개념정리] 서브쿼리(Subquery)

미니실습 4) Scalar Subquery; 값 1개

문제: enrollments에서 final_price가 “전체 평균 final_price”보다 큰 신청만 보기

출력: enrollment_id, final_price

-- 정답
SELECT enrollment_id, final_price
FROM basic.enrollments
WHERE final_price > (
	-- 전체 평균
	SELECT avg(final_price)
	FROM basic.enrollments
	);

미니실습 5) Column Subquery; 열1개, 리스트
IN()

문제: level='beginner'인 강좌를 신청한 enrollments만 출력

출력: enrollment_id, course_id

--정답
SELECT enrollment_id, course_id
FROM basic.enrollments
where course_id IN (
	SELECT course_id
	FROM basic.courses
	WHERE level = 'beginner'
	);

미니실습 6) Table Subquery; / 표
EXISTS / NOT EXISTS

문제: “결제 테이블(payments)에 기록이 한 번도 없는 신청(enrollment)” 찾기
출력: enrollment_id

-- 정답
SELECT e.enrollment_id
FROM basic.enrollments e 
where not exists (
	select *
	from basic.payments p
	where p.enrollment_id=e.enrollment_id
	);

미니실습 7) FROM 서브쿼리(파생 테이블)

문제: 학생별 신청 건수(enroll_cnt)를 students에 붙여서 출력

출력: student_id, student_name, enroll_cnt (신청 없으면 0)

-- 학생별
select s.student_id,
       s.student_name,
       ifnull(enr.enroll_cnt, 0) as enroll_cnt
from basic.students s
	left join
		(-- 학생별 신청건수
		select student_id,
		       count(enrollment_id) as enroll_cnt
		from basic.enrollments
		group by student_id
		)as enr
	on s.student_id=enr.student_id;

미니실습 8) CTE (WITH) : 이름 붙인 파생테이블

문제: is_active = 1인 강좌만 뽑는 CTE(active_courses)를 만들고, 그 CTE를 조회하세요.

출력: course_id, course_name, is_active

with active_course as (
	select course_id, course_name, is_active
	from basic.courses
	where is_active = 1
	)
select *
from active_course;

 

 

  서브쿼리의 용도를 잘 체크!  

  하나의 값과 비교 = Scalar  

  여러 값과 비교 = Column(IN())  

  있냐, 없냐 확인 = Table(exists, not exists)  

  중간결과를 만들고 싶을 때 = FROM  

 

3. 종합실습
ㅁㅊ.. 맞췄다 ㅁㅊㅁㅊㅁㅊ (근데 40분 걸림ㅋ)

✅ 최종 미션: “학생 한 줄 요약 리포트”
     우리가 만들 결과(학생 1명 = 1행)
출력:
- `student_id, student_name, region, segment`
- `enroll_cnt` : 신청 건수 
- `coupon_used_cnt` : 쿠폰 쓴 신청 건수
- `coupon_user_flag` : CASE로 라벨
    - coupon_used_cnt = 0 → `no_coupon_user`
    - 1 이상 → `coupon_user`

-- 학생별 신청건수 enrollments
WITH
enr_by_stu AS (
	SELECT student_id,
       count(*) AS enroll_cnt
	FROM basic.enrollments
	GROUP BY student_id
	),
-- 학생별 쿠폰 쓴 신청 건수 enrollments
cp_cnt_per_stu AS (
	SELECT student_id,
	       SUM(CASE WHEN coupon_code IS NULL THEN 0
	       		    ELSE 1 end) AS coupon_cnt
	FROM basic.enrollments e
	GROUP BY student_id
	)
-- 최종출력
SELECT s.student_id,
	   s.student_name,
	   s.region,
	   s.segment,
	   IFNULL(e.enroll_cnt, 0) AS enroll_cnt, -- 신청건수
	   IFNULL(c.coupon_cnt, 0) AS coupon_used_cnt, -- 쿠폰 사용 수
	   CASE WHEN c.coupon_cnt = 0 THEN 'no_coupon_user'
	        ELSE 'coupon_user'
	        END AS coupon_user_flag
FROM basic.students s
	LEFT JOIN enr_by_stu e
	ON s.student_id = e.student_id
	LEFT JOIN cp_cnt_per_stu c
	ON s.student_id = c.student_id
ORDER BY s.student_id;

---

SQL 데일리 퀘스트
# 과제

과제1) CASE로 “쿠폰 사용 여부 + 가격 구간” 라벨 만들기

상황: 운영팀이 `enrollments`를 볼 때, 쿠폰 사용 여부와 결제금액 구간이 한눈에 보이길 원합니다.

 

목표: `enrollment_status`가 `active`, `completed`인 신청만 대상으로 아래 컬럼을 출력하세요.

 

출력 컬럼

- `enrollment_id`

- `student_id`

- `final_price`

- `coupon_flag`

      - 쿠폰 미사용(`coupon_code IS NULL`) → `'no_coupon'`

      - 쿠폰 사용 → `'coupon_used'`

- `price_bucket`

     - `final_price < 50000` → `'low'`

     - `50000 <= final_price < 90000` → `'mid'`

     - `final_price >= 90000` → `'high'`

 

정렬

`enrollment_id` 오름차순

-- 📌오답
SELECT enrollment_id,
       student_id,
       final_price,
       -- 쿠폰 사용 여부
       CASE WHEN coupon_code IS NULL THEN 'no_coupon'
            ELSE 'coupon_used'
           END AS coupon_flag,
       -- price_bucket
       CASE WHEN final_price < 50000 THEN 'low'
            WHEN 50000 <= final_price < 90000 THEN 'mid'
            WHEN final_price >= 90000 THEN 'high'
           END price_bucket
FROM basic.enrollments
WHERE enrollment_status IN ('active', 'completed')
ORDER BY enrollment_id ASC;

-- 💡CASE WHEN 높->낮
SELECT enrollment_id,
       student_id,
       final_price,
       -- 쿠폰 사용 여부
       CASE WHEN coupon_code IS NULL THEN 'no_coupon'
            ELSE 'coupon_used'
           END AS coupon_flag,
       -- price_bucket
       CASE WHEN final_price >= 90000 THEN 'high'
            WHEN 50000 <= final_price < 90000 THEN 'mid'
	        WHEN final_price < 50000 THEN 'low'
           END AS price_bucket
FROM basic.enrollments
WHERE enrollment_status IN ('active', 'completed')
ORDER BY enrollment_id ASC;

-- 💡CASE WHEN 'mid' 부분 나눠서 쓰기
SELECT enrollment_id,
       student_id,
       final_price,
       -- 쿠폰 사용 여부
       CASE WHEN coupon_code IS NULL THEN 'no_coupon'
            ELSE 'coupon_used'
           END AS coupon_flag,
       -- price_bucket
       CASE WHEN final_price < 50000 THEN 'low'
            WHEN final_price >= 50000 AND final_price < 90000 THEN 'mid'
	        WHEN final_price >= 90000 THEN 'high'
           END AS price_bucket
FROM basic.enrollments
WHERE enrollment_status IN ('active', 'completed')
ORDER BY enrollment_id ASC;

<오답노트>
- CASE WHEN 범위조건 적을 때, 낮은것부터 높은 것까지 쓰고 중간 범위를 묶어서 (1<x<=10) 쓰면 값에 오류가 날 수 있다.
   왜? x의 조건을 맨위 조건부터 훑을 때, 첫 줄 조건에 만족하면 아래 조건은 검사조차 안 됨.
방법1) 값이 높은 조건을 맨 위로 -> 낮은 값 조건
방법2) 중간범위 조건을 묶어서 쓰지 말고 나눠서 (WHEN x>1 AND x<=10)

 

과제2) IN 서브쿼리로 “SQL 카테고리 강좌를 신청한 학생” 찾기

상황: 마케팅팀이 **SQL 카테고리** 강좌를 신청한 학생에게만 안내 메시지를 보내려 합니다.

목표: `courses.category = 'sql'`인 강좌를 **한 번이라도 신청한** 학생 목록을 출력하세요.
(신청 상태는 `active`, `completed`만 포함 / `cancelled` 제외)

출력 컬럼:
- `student_id`
- `student_name`

정렬:

- `student_id` 오름차순

-- 📌오답 / 접근방식 틀렸음.
SELECT student_id,
       student_name
FROM basic.students
WHERE student_id IN (
	SELECT e.student_id
	FROM basic.enrollments e
	WHERE e.enrollment_status IN ('active', 'completed')
	)
ORDER BY student_id;

-- 💡조건 두 개 join으로 합치기
SELECT s.student_id, s.student_name 
FROM basic.students s
WHERE s.student_id IN (
	SELECT e.student_id
	FROM basic.enrollments e
		LEFT JOIN basic.courses c
		ON e.course_id=c.course_id
	WHERE c.category = 'sql'
	AND e.enrollment_status IN ('active', 'completed')
	)
ORDER BY s.student_id ASC;

-- 💡답지 정답
SELECT s.student_id,
       s.student_name
FROM basic.students s
WHERE s.student_id IN (
  SELECT e.student_id
  FROM basic.enrollments e
  -- inrollment 조건 달기
  WHERE e.enrollment_status IN ('active', 'completed')
    -- and로 추가 조건 달기
    AND e.course_id IN (
      SELECT c.course_id
      FROM basic.courses c
      WHERE c.category = 'sql'
    )
)
ORDER BY s.student_id;

 

<오답노트>
- 서브쿼리(IN/EXISTS) 안에는 ‘ID를 고르는 데 필요한 모든 조건’이 들어가야 한다.
   두 조건이 각각 다른 테이블에 있으면 JOIN 사용
   courses 조건, enrollments 조건을 join으로 합침
   이것을 WHERE IN() 서브쿼리로 사용
1️⃣ 먼저 ‘어떤 ID가 대상인지’를 정의한다
2️⃣ 그 ID를 바깥에서 출력한다

과제3) NOT EXISTS로 “쿠폰을 한 번도 사용하지 않은 학생” 찾기

상황
쿠폰을 한 번도 사용하지 않은 학생에게 “첫 쿠폰 혜택”을 주려고 합니다.

목표
아래 조건을 만족하는 학생만 출력하세요.
- 해당 학생의 enrollments 중에서
- `coupon_code IS NOT NULL`인 행이 **단 한 건도 없는 학생**
> 포인트: “없다”를 찾는 가장 대표 패턴이 NOT EXISTS 입니다.

출력 컬럼
- `student_id`
- `student_name`

정렬
- `student_id` 오름차순

-- 정답
SELECT s.student_id, s.student_name 
FROM basic.students s
WHERE NOT EXISTS (
	SELECT *
	FROM basic.enrollments e
	WHERE e.student_id=s.student_id
	AND e.coupon_code IS NOT NULL 
	)
ORDER BY s.student_id;

과제4) CTE로 “학생별 신청/쿠폰 요약 + 라벨( CASE )” 만들기

상황
운영팀이 학생별로 “신청 수 / 쿠폰 사용 수 / 쿠폰 유저 여부 / 신청 유저 여부”를 한 번에 보고 싶어합니다.

목표
CTE 2개로 요약 테이블을 만든 뒤 `students`에 LEFT JOIN해서 아래를 출력하세요.
단, **신청 상태는 `active`, `completed`만 포함** (취소 제외)

출력 컬럼
- `student_id`
- `student_name`
- `enroll_cnt` (신청 수)
- `coupon_used_cnt` (쿠폰 사용 신청 수)
- `enroll_flag`
    - `enroll_cnt = 0` → `'no_enroll'`
    - 그 외 → `'has_enroll'`
- `coupon_user_flag`
    - `coupon_used_cnt = 0` → `'no_coupon_user'`
    - 그 외 → `'coupon_user'`
- `coupon_usage_rate_pct`
    - `enroll_cnt = 0`이면 `0`
    - 그 외 `ROUND(coupon_used_cnt / enroll_cnt * 100, 1)`

정렬
- `student_id` 오름차순

-- 📌오답
WITH 
	-- 신청 수
	enroll_cnt AS ( -- CTE의 별칭은 서브쿼리와 다르게 배정하는 게 좋음.
		SELECT e.student_id
			   , COUNT(*) -- 별칭을 꼭 적어줘야 함.
		FROM basic.enrollments e
		WHERE e.enrollment_status IN ('active', 'completed')
		GROUP BY e.student_id
		)
	-- 쿠폰 사용 수
	cp_cnt AS (
		SELECT e.student_id
			   , SUM(CASE WHEN e.coupon_code IS NOT NULL THEN 1 ELSE 0 END)AS coupon_cnt
		FROM basic.enrollments e
		GROUP BY e.student_id
		)
-- 최종출력(학생별)
SELECT s.student_id
	   , s.student_name
	   , enroll_cnt
       , ifnull(e.enroll_cnt, 0) AS enroll_cnt
	   , ifnull(c.coupon_cnt, 0) AS coupon_cnt
	   -- enroll_flag
       , CASE WHEN enroll_cnt=0 THEN 'no_enroll' -- select에서 사용한 별칭은 select 내에서 재사용 불가!!!
          ELSE 'has_enroll'
          END enroll_flag
	   -- coupon_user_flag
       , CASE WHEN ifnull(c.coupon_cnt, 0)=0 THEN 'no_coupon_user'
          ELSE 'coupon_user'
          END coupon_user_flag
	   -- coupon_usage_rate_pct -- 분모가 0일 때 오류 발생 가능성 있음. 분모가 0일 때는 그냥 0을 반환하도록.
FROM basic.students s
	LEFT JOIN enroll_cnt e
	ON s.student_id=e.student_id
    
-- 💡해설
WITH 
	-- 신청 수
	enr_cnt AS (
		SELECT e.student_id
			   , COUNT(*) AS enroll_cnt
		FROM basic.enrollments e
		WHERE e.enrollment_status IN ('active', 'completed')
		GROUP BY e.student_id
		),
	-- 쿠폰 사용 수
	cp_cnt AS (
		SELECT e.student_id
			   , SUM(CASE WHEN e.coupon_code IS NOT NULL THEN 1 ELSE 0 END)AS coupon_cnt
		FROM basic.enrollments e
		GROUP BY e.student_id
		)
-- 최종출력(학생별)
SELECT s.student_id
	   , s.student_name
	   , ifnull(e.enroll_cnt, 0) AS enroll_cnt
	   , ifnull(c.coupon_cnt, 0) AS coupon_cnt
	   -- enroll_flag
	   , CASE WHEN ifnull(e.enroll_cnt, 0)=0 THEN 'no_enroll'
	   		  ELSE 'has_enroll'
	   		  END enroll_flag
	   -- coupon_user_flag
	   , CASE WHEN ifnull(c.coupon_cnt, 0)=0 THEN 'no_coupon_user'
	   		  ELSE 'coupon_user'
	   		  END coupon_user_flag
	   -- coupon_usage_rate_pct
	   , CASE WHEN ifnull(e.enroll_cnt, 0)=0 THEN 0
	   		  ELSE round(ifnull(c.coupon_cnt, 0)/ifnull(e.enroll_cnt, 0)*100, 1)
	   		  END AS coupon_usage_rate_pct
FROM basic.students s
	LEFT JOIN enr_cnt e
	ON s.student_id=e.student_id
	LEFT JOIN cp_cnt c
	ON s.student_id=c.student_id
ORDER BY student_id ASC;

<오답노트>
- SELECT 내 별칭은 SELECT에서 재사용 불가능

CASE WHEN ifnull(e.enroll_cnt, 0)=0 THEN 0
           ELSE ROUND(ifnull(c.coupon_cnt, 0) / ifnull(e.enroll_cnt, 0) * 100, 1) END AS coupon_usage_rate_pct
- 나누기의 분모가 0이면 계산 불가 → 오류
- 분모가 0이면 그대로 0을 반환하도록 함 ▶ 계산 자체를 건너뜀.
- ELSE: 분모가 0이 아닌 모든 경우는 이렇게 계산해라.

WITH coupon_by_student AS (
          SELECT student_id,
                          COUNT(*) AS coupon_used_cnt
          FROM basic.enrollments
          WHERE enrollment_status IN ('active', 'completed')
          AND coupon_code IS NOT NULL
          GROUP BY student_id
          )
- coupon_code가 NULL인 것을 제외한 개수를 세야할 때 SUM도 가능하지만,
   애초에 WHERE에서 거르는 게 더 간단!!

# 퀘스트

Q1. CTE(WITH)에 대한 설명으로 가장 올바른 것은?
A. CTE는 실행하면 DB에 영구 테이블로 저장된다
B. CTE는 단일 statement 범위에서만 존재하며, 그 statement 안에서 여러 번 참조할 수 있다
C. CTE는 GROUP BY가 반드시 포함되어야 한다
D. CTE는 MySQL에서 지원되지 않는다

Q2. MySQL에서 아래처럼 FROM 절에 서브쿼리를 넣을 때 반드시 필요한 것은?

SELECT *
FROM (SELECT course_id FROM basic.courses);

A. WHERE 절
B. ORDER BY 절
C. 파생 테이블(derived table)의 별칭(alias)
D. LIMIT 절

 

Q3. `EXISTS` 서브쿼리에 대한 설명으로 올바른 것은?
A. 서브쿼리가 0행을 반환하면 EXISTS는 TRUE이다
B. 서브쿼리가 1행 이상을 반환하면 EXISTS는 TRUE이다
C. EXISTS는 서브쿼리의 SELECT 컬럼 목록을 반드시 실제로 사용해서 계산한다
D. EXISTS는 반드시 GROUP BY와 함께 써야 한다

Q4. `CASE WHEN`(CASE 표현식) 동작에 대한 설명으로 가장 올바른 것은?
A. 모든 WHEN 조건을 끝까지 평가한 뒤, TRUE인 THEN 결과를 모두 합쳐 반환한다
B. 위에서부터 조건을 평가하며 **처음 TRUE가 된 THEN의 값**을 반환하고, ELSE가 없고 조건이 모두 FALSE면 NULL을 반환할 수 있다
C. CASE는 WHERE 절에서만 사용할 수 있다
D. CASE는 숫자 결과만 반환할 수 있다

Q5. `IFNULL(expr1, expr2)`의 동작으로 올바른 것은?
A. expr1이 NULL이면 expr1을 반환한다
B. IFNULL은 인자를 3개 이상 받을 수 있다
C. expr1이 NULL이 아니면 expr1을, NULL이면 expr2를 반환한다
D. IFNULL은 NULL 값을 다른 값으로 대체할 수 없다

---

# 자기주도학습을 위한 AI활용법 세션 1회차

그렇다면 앞으로 중요한 것이 무엇일까?

1. 결과를 검증해 낼 수 있어야 한다.

---

<내일의 학습>

• 라이브 세션
  • SQL 라이브세션 6회차
  • 자기주도학습을 위한 AI활용법 2회차
• 개인학습
  • SQL 코드카타 30일, 31일 분량
  • 팀 아티클: 데이터 분석가가 갖춰야할 9가지 역량
  • Sub_사캠 SQL 달리기반 문제풀이 (4번~)