자동차 대여 기록 별 대여 금액 구하기
- CASE + LEFT JOIN
- 대여 기간을 계산한 뒤
7일 이상 / 30일 이상 / 90일 이상구간으로 변환 - 할인 정책이 없는 7일 미만 대여 기록도 출력해야 하므로
LEFT JOIN - 할인 정책이 없으면
IFNULL(DISCOUNT_RATE, 0)으로 처리 - 범위가 겹치는
CASE는 큰 조건부터 검사
SELECT
H.HISTORY_ID,
FLOOR(
C.DAILY_FEE
* (DATEDIFF(H.END_DATE, H.START_DATE) + 1)
* (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100
) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE
AND P.DURATION_TYPE =
CASE
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 90 THEN '90일 이상'
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 30 THEN '30일 이상'
WHEN DATEDIFF(H.END_DATE, H.START_DATE) + 1 >= 7 THEN '7일 이상'
END
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC;- 핵심 포인트
- 대응하는 데이터가 없어도 원본 행을 살려야 하면
LEFT JOIN - 문자열에서
7, 30, 90을 추출하기보다 실제 기간을CASE로 정책 구간에 매핑 DATEDIFF(END_DATE, START_DATE) + 1로 대여일 계산
취소되지 않은 진료 예약 조회하기
- 실제 관계를 나타내는 키로 JOIN
- 같은 진료과 코드는 여러 의사가 공유할 수 있으므로 의사와 예약을 진료과 코드로 연결하면 안 됨
- 실제 담당 의사를 나타내는
MDDR_ID = DR_ID로 연결
SELECT
A.APNT_NO,
P.PT_NAME,
P.PT_NO,
D.MCDP_CD,
D.DR_NAME,
A.APNT_YMD
FROM APPOINTMENT A
JOIN DOCTOR D
ON A.MDDR_ID = D.DR_ID
JOIN PATIENT P
ON A.PT_NO = P.PT_NO
WHERE A.MCDP_CD = 'CS'
AND A.APNT_CNCL_YN = 'N'
AND A.APNT_YMD >= '2022-04-13'
AND A.APNT_YMD < '2022-04-14'
ORDER BY A.APNT_YMD;- 핵심 포인트
- 공통 컬럼이라고 무조건 JOIN 키가 되는 것은 아님
ON은 테이블 관계,WHERE는 조회 조건으로 나누면 읽기 좋음- TIMESTAMP의 하루 전체 조회는
>= 시작일 AND < 다음날방식이 안전
FrontEnd 개발자 찾기
- 비트마스크 + JOIN
CODE가 2의 거듭제곱으로 저장되어 있으므로 비트 AND 연산으로 스킬 보유 여부 확인- Front End 스킬을 여러 개 가진 개발자는 JOIN 결과가 여러 행이 될 수 있으므로
DISTINCT사용
SELECT DISTINCT
D.ID,
D.EMAIL,
D.FIRST_NAME,
D.LAST_NAME
FROM SKILLCODES S
JOIN DEVELOPERS D
ON (S.CODE & D.SKILL_CODE) != 0
WHERE S.CATEGORY = 'Front End'
ORDER BY D.ID;- 핵심 포인트
(SKILL_CODE & CODE) > 0- 결과가 0이 아니면 해당 스킬 보유
- 한 대상이 JOIN 상대 여러 행과 매칭될 수 있는데 최종 결과는 한 행이어야 하면
DISTINCT고려
상품을 구매한 회원 비율 구하기
- CTE + 스칼라 서브쿼리
- 2021년 가입자를 먼저 CTE로 분리
- 월별 구매 회원 수는
COUNT(DISTINCT USER_ID) - 전체 2021년 가입 회원 수는 모든 월에서 동일하므로 스칼라 서브쿼리로 계산
WITH USER_2021 AS (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
)
SELECT
YEAR(O.SALES_DATE) AS YEAR,
MONTH(O.SALES_DATE) AS MONTH,
COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
ROUND(
COUNT(DISTINCT O.USER_ID)
/ (SELECT COUNT(*) FROM USER_2021),
1
) AS PURCHASED_RATIO
FROM ONLINE_SALE O
JOIN USER_2021 U
ON O.USER_ID = U.USER_ID
GROUP BY
YEAR(O.SALES_DATE),
MONTH(O.SALES_DATE)
ORDER BY YEAR, MONTH;- 다른 풀이 - CTE + CROSS JOIN
WITH USER_2021 AS (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
),
TOTAL AS (
SELECT COUNT(*) AS CNT
FROM USER_2021
)
SELECT
YEAR(O.SALES_DATE) AS YEAR,
MONTH(O.SALES_DATE) AS MONTH,
COUNT(DISTINCT O.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT O.USER_ID) / T.CNT, 1) AS PURCHASED_RATIO
FROM ONLINE_SALE O
JOIN USER_2021 U
ON O.USER_ID = U.USER_ID
CROSS JOIN TOTAL T
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE), T.CNT;- 핵심 포인트
- 값 하나가 필요하면 스칼라 서브쿼리
- 별도로 만든 1행짜리 값을 모든 행에 붙이면 CROSS JOIN
- 분자는 월별 값, 분모는 전체 회원 수라는 상수
업그레이드된 아이템 구하기
- 부모/자식 역할로 같은 테이블 두 번 JOIN
SELECT
CHILD.ITEM_ID,
CHILD.ITEM_NAME,
CHILD.RARITY
FROM ITEM_TREE T
JOIN ITEM_INFO PARENT
ON T.PARENT_ITEM_ID = PARENT.ITEM_ID
JOIN ITEM_INFO CHILD
ON T.ITEM_ID = CHILD.ITEM_ID
WHERE PARENT.RARITY = 'RARE'
ORDER BY CHILD.ITEM_ID DESC;- 다른 풀이 - IN 서브쿼리
SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN (
SELECT T.ITEM_ID
FROM ITEM_INFO I
JOIN ITEM_TREE T
ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE I.RARITY = 'RARE'
)
ORDER BY ITEM_ID DESC;- 핵심 포인트
- 같은 테이블도 역할이 다르면 별칭을 달리해 여러 번 JOIN 가능
PARENT는 부모 조건 확인용,CHILD는 출력 정보 조회용
대장균의 자식의 수 구하기
- SELF JOIN + LEFT JOIN
- 모든 개체를 부모 후보로 유지
- 자식이 없는 개체도 출력해야 하므로
LEFT JOIN
SELECT
PARENT.ID,
COUNT(CHILD.ID) AS CHILD_COUNT
FROM ECOLI_DATA PARENT
LEFT JOIN ECOLI_DATA CHILD
ON PARENT.ID = CHILD.PARENT_ID
GROUP BY PARENT.ID
ORDER BY PARENT.ID;- COUNT(*)와 COUNT(컬럼)의 차이
COUNT(*) -- 행 자체의 개수
COUNT(CHILD.ID) -- CHILD.ID가 NULL이 아닌 행만- 자식이 없더라도
LEFT JOIN은 부모 행 하나를 남김 - 따라서 자식 수를 셀 때
COUNT(*)를 쓰면 1로 나올 수 있음
- 핵심 포인트
LEFT JOIN후 실제 매칭된 행 수를 세려면 오른쪽 테이블의 NOT NULL 컬럼을COUNT
부모의 형질을 모두 가지는 대장균 찾기
- SELF JOIN + 비트 AND
SELECT
CHILD.ID,
CHILD.GENOTYPE,
PARENT.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA PARENT
JOIN ECOLI_DATA CHILD
ON PARENT.ID = CHILD.PARENT_ID
WHERE (CHILD.GENOTYPE & PARENT.GENOTYPE)
= PARENT.GENOTYPE
ORDER BY CHILD.ID;- 핵심 포인트
- 특정 비트 하나라도 보유
(A & B) > 0- B의 모든 비트를 A가 보유
(A & B) = B- AND 연산은 새로운 1비트를 만들 수 없으므로 결과는 B보다 커질 수 없음
대장균의 크기에 따라 분류하기 2
- NTILE 활용
- 크기 순으로 정렬한 뒤 전체 데이터를 4개 그룹으로 균등 분할
WITH RANKED AS (
SELECT
ID,
NTILE(4) OVER (
ORDER BY SIZE_OF_COLONY DESC
) AS GRADE
FROM ECOLI_DATA
)
SELECT
ID,
CASE
WHEN GRADE = 1 THEN 'CRITICAL'
WHEN GRADE = 2 THEN 'HIGH'
WHEN GRADE = 3 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM RANKED
ORDER BY ID;- 다른 풀이 - ROW_NUMBER
WITH RANKED AS (
SELECT
ID,
ROW_NUMBER() OVER (
ORDER BY SIZE_OF_COLONY DESC
) AS RN,
COUNT(*) OVER () AS TOTAL
FROM ECOLI_DATA
)
SELECT
ID,
CASE
WHEN RN <= TOTAL * 0.25 THEN 'CRITICAL'
WHEN RN <= TOTAL * 0.50 THEN 'HIGH'
WHEN RN <= TOTAL * 0.75 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM RANKED
ORDER BY ID;- 핵심 포인트
ROW_NUMBER()= 순번NTILE(N)= 정렬 결과를 N개 그룹으로 균등 분할상위 25%,4등분,사분위→NTILE(4)고려
연도별 대장균 크기의 편차 구하기
- 집계 윈도우 함수
- 연도별 최대 크기는 필요하지만 원본 대장균 행도 유지해야 함
GROUP BY가 아니라MAX() OVER (PARTITION BY ...)사용
SELECT
YEAR(DIFFERENTIATION_DATE) AS YEAR,
MAX(SIZE_OF_COLONY) OVER (
PARTITION BY YEAR(DIFFERENTIATION_DATE)
) - SIZE_OF_COLONY AS YEAR_DEV,
ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV;- 핵심 포인트
GROUP BY→ 그룹당 한 행으로 줄어듦
MAX(...) OVER (PARTITION BY ...)→ 원본 행 수를 유지하면서 그룹별 집계값을 각 행에 붙임
업그레이드 할 수 없는 아이템 구하기
- NOT IN
PARENT_ITEM_ID에 등장하는 아이템은 다음 단계 아이템이 존재- 따라서 부모 ID 목록에 없는 아이템이 최종 아이템
SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID NOT IN (
SELECT PARENT_ITEM_ID
FROM ITEM_TREE
WHERE PARENT_ITEM_ID IS NOT NULL
)
ORDER BY ITEM_ID DESC;- 다른 풀이 - LEFT JOIN
SELECT
I.ITEM_ID,
I.ITEM_NAME,
I.RARITY
FROM ITEM_INFO I
LEFT JOIN ITEM_TREE T
ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE T.ITEM_ID IS NULL
ORDER BY I.ITEM_ID DESC;- 핵심 포인트
- 존재하지 않는 관계 찾기 →
NOT IN,NOT EXISTS,LEFT JOIN ... IS NULL NOT IN사용 시 서브쿼리의NULL제거LEFT JOIN은 왼쪽 테이블을 모두 살리므로 테이블 순서가 중요
특정 세대의 대장균 찾기
- 연속 SELF JOIN
SELECT GRANDCHILD.ID
FROM ECOLI_DATA PARENT
JOIN ECOLI_DATA CHILD
ON PARENT.ID = CHILD.PARENT_ID
JOIN ECOLI_DATA GRANDCHILD
ON CHILD.ID = GRANDCHILD.PARENT_ID
WHERE PARENT.PARENT_ID IS NULL
ORDER BY GRANDCHILD.ID;- 핵심 포인트
PARENT = 1세대
CHILD = 2세대
GRANDCHILD = 3세대PARENT.PARENT_ID IS NULL로 시작점을 실제 1세대로 고정- 시작점을 고정하지 않으면
2세대 → 3세대 → 4세대경로도 포함될 수 있음
언어별 개발자 분류하기
- SKILLCODES를 동적으로 조회
- 문제 예시의 CODE 값을 직접 하드코딩하지 않음
- Python, C# 등은 스칼라 서브쿼리로 실제 CODE를 조회
SELECT CODE
FROM SKILLCODES
WHERE NAME = 'Python';- Front End처럼 여러 CODE 중 하나라도 보유하면 되면 모든 비트를 하나의 마스크로 합침
SELECT SUM(CODE)
FROM SKILLCODES
WHERE CATEGORY = 'Front End';- 풀이
SELECT
CASE
WHEN
(SKILL_CODE & (
SELECT SUM(CODE)
FROM SKILLCODES
WHERE CATEGORY = 'Front End'
)) > 0
AND
(SKILL_CODE & (
SELECT CODE
FROM SKILLCODES
WHERE NAME = 'Python'
)) > 0
THEN 'A'
WHEN
(SKILL_CODE & (
SELECT CODE
FROM SKILLCODES
WHERE NAME = 'C#'
)) > 0
THEN 'B'
ELSE 'C'
END AS GRADE,
ID,
EMAIL
FROM DEVELOPERS
WHERE
(SKILL_CODE & (
SELECT SUM(CODE)
FROM SKILLCODES
WHERE CATEGORY = 'Front End'
)) > 0
OR
(SKILL_CODE & (
SELECT CODE
FROM SKILLCODES
WHERE NAME = 'C#'
)) > 0
ORDER BY GRADE, ID;- 핵심 포인트
- 예시 데이터의 CODE를 고정값이라고 가정하지 않기
- 값 하나가 필요하다고 무조건 JOIN할 필요는 없음
- A가
Front End + Python, C가Front End이므로 더 구체적인 A를 먼저 검사
조건에 맞는 사원 정보 조회하기
- GROUP BY + ORDER BY + LIMIT
- 사원별 평가 점수 합계를 계산
- 총점 내림차순으로 정렬 후 최고점 한 명 조회
SELECT
SUM(G.SCORE) AS SCORE,
E.EMP_NO,
E.EMP_NAME,
E.POSITION,
E.EMAIL
FROM HR_GRADE G
JOIN HR_EMPLOYEES E
ON G.EMP_NO = E.EMP_NO
GROUP BY
E.EMP_NO,
E.EMP_NAME,
E.POSITION,
E.EMAIL
ORDER BY SCORE DESC
LIMIT 1;- 다른 풀이 - MAX 서브쿼리
SELECT
SUM(G.SCORE) AS SCORE,
E.EMP_NO,
E.EMP_NAME,
E.POSITION,
E.EMAIL
FROM HR_GRADE G
JOIN HR_EMPLOYEES E
ON G.EMP_NO = E.EMP_NO
GROUP BY
E.EMP_NO,
E.EMP_NAME,
E.POSITION,
E.EMAIL
HAVING SUM(G.SCORE) = (
SELECT MAX(T.SCORE)
FROM (
SELECT
EMP_NO,
SUM(SCORE) AS SCORE
FROM HR_GRADE
GROUP BY EMP_NO
) T
);- 핵심 포인트
- 최고 한 명 →
ORDER BY ... DESC LIMIT 1 - 최고점 동점자를 모두 조회 →
MAX와 비교하거나RANK() - 집계값
SUM(SCORE)으로 필터링 →WHERE가 아닌HAVING
사원 평가 등급 / 보너스 계산
- 먼저 집계한 뒤 JOIN
- 사원별 평가 평균을 파생 테이블에서 계산
- 직원 정보와 JOIN한 뒤
CASE로 등급과 보너스 계산
SELECT
E.EMP_NO,
E.EMP_NAME,
CASE
WHEN T.SCORE >= 96 THEN 'S'
WHEN T.SCORE >= 90 THEN 'A'
WHEN T.SCORE >= 80 THEN 'B'
ELSE 'C'
END AS GRADE,
CASE
WHEN T.SCORE >= 96 THEN E.SAL * 0.2
WHEN T.SCORE >= 90 THEN E.SAL * 0.15
WHEN T.SCORE >= 80 THEN E.SAL * 0.1
ELSE 0
END AS BONUS
FROM HR_EMPLOYEES E
JOIN (
SELECT
EMP_NO,
AVG(SCORE) AS SCORE
FROM HR_GRADE
GROUP BY EMP_NO
) T
ON E.EMP_NO = T.EMP_NO
ORDER BY E.EMP_NO;- 핵심 포인트
먼저 그룹별 집계 → 집계 결과를 상세 테이블과 JOIN → CASE 계산- 파생 테이블에는 반드시 별칭 필요
헤비 유저가 소유한 장소
- IN + GROUP BY + HAVING
- 장소가 2개 이상인
HOST_ID목록을 먼저 구함 - 바깥 쿼리에서 해당 HOST의 장소 전체 조회
SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID IN (
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(*) >= 2
)
ORDER BY ID;- 핵심 포인트
- 조건을 만족하는 그룹의 ID를 먼저 찾고 원본 상세 행을 다시 조회하는 패턴
COUNT(*)= 행 개수COUNT(HOST_ID)= HOST_ID가 NULL이 아닌 행 개수
우유와 요거트가 담긴 장바구니
- COUNT(DISTINCT)
- 장바구니 단위로 그룹화
- Milk와 Yogurt 중 실제로 서로 다른 종류가 2개 존재하는지 확인
SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) = 2
ORDER BY CART_ID;- 주의
GROUP BY CART_ID, NAME- 이렇게 하면 Milk와 Yogurt가 서로 다른 그룹으로 분리됨
HAVING COUNT(*) >= 2- Milk만 두 개 있어도 조건을 만족할 수 있음
- 핵심 포인트
- 같은 그룹 안에 서로 다른 조건 A와 B가 모두 존재해야 하면
COUNT(DISTINCT ...)활용
꾸준함을 잃지 말자.