문제로 정리하는 SQL(SELECT 편)

Select 문제

• 헷갈리거나 자주 사용할 것 같은 문법 sql을 메모

조건에 맞는 개발자 찾기

select d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
from DEVELOPERS d
where exists (select 1 from SKILLCODES s where s.NAME in ('Python', 'C#') and (d.SKILL_CODE & s.CODE) > 0)
order by d.ID asc

exsits 내 조건이 true일 경우 해당 행을 출력할 수 있는 것 같다.

조건에 부합하는 중고거래 댓글 조회하기

SELECT b.TITLE, b.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS, DATE_FORMAT(r.CREATED_DATE, "%Y-%m-%d") AS CREATED_DATE
FROM USED_GOODS_BOARD b
JOIN USED_GOODS_REPLY r ON b.BOARD_ID = r.BOARD_ID
WHERE DATE_FORMAT(b.CREATED_DATE, "%Y-%m") = "2022-10"
ORDER BY r.CREATED_DATE ASC, b.TITLE ASC

DATE_FORMAT 문법은 날짜 구할 때, 자주 사용하는 것 같으니 외워두자

과일로 만든 아이스크림 고르기

SELECT b.FLAVOR
FROM FIRST_HALF a
JOIN ICECREAM_INFO b ON a.FLAVOR = b.FLAVOR
WHERE b.INGREDIENT_TYPE = "fruit_based" AND a.TOTAL_ORDER > 3000
ORDER BY a.TOTAL_ORDER DESC

평균 일일 대여 요금 구하기

SELECT ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = "SUV"

ROUND(값, n번째에서 반올림)

재구매가 일어난 상품과 회원 리스트 구하기

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID ASC, PRODUCT_ID DESC

GROUP BY USER_ID, PRODUCT_ID → 같은 유저가 같은 상품을 산 기록들을 묶는다.
COUNT(*) > 1 → 묶인 그룹 안에 2개 이상(즉, 재구매)인 경우만 남긴다.
ORDER BY → 결과를 유저 오름차순, 같은 유저 안에서는 상품 ID 내림차순으로 정렬한다.

상위 n개 레코드

SELECT NAME
FROM ANIMAL_INS 
ORDER BY DATETIME ASC
LIMIT 1

limit은 개수 제한

업그레이드 된 아이템 구하기

select a.ITEM_ID, a.ITEM_NAME, a.RARITY
from ITEM_INFO as a
where exists (
    select 1
    from ITEM_TREE t
    join ITEM_INFO p on p.ITEM_ID = t.PARENT_ITEM_ID
    where t.ITEM_ID = a.ITEM_ID
        and p.RARITY = "RARE"
)
order by a.ITEM_ID desc

EXISTS는 실제 값을 반환하지 않고, “조건을 만족하는 행이 있냐?”만 체크한다.
SELECT 1, SELECT * 어떤 걸 써도 상관없이 TRUE/FALSE 판정만 한다.
부모 → 자식 관계를 JOIN으로 연결해 조건을 줄 수 있다.

Python 개발자 찾기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE "Python" IN (SKILL_1, SKILL_2, SKILL_3)
ORDER BY ID ASC

where [조건] in (컬럼1, 컬럼2, 컬럼3) 으로 해당 컬럼들안에 조건이 들어있는지 확인 가능하다.

특정 형징을 가지는 대장균 찾기

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 1 != 0 OR GENOTYPE & 4 != 0)

&랑 | 로 비트 연산이 가능하다.

대장균들의 자식의 수

SELECT A.ID, (SELECT COUNT(*) FROM ECOLI_DATA B WHERE B.PARENT_ID = A.ID) AS CHILD_COUNT
FROM ECOLI_DATA A
ORDER BY A.ID;

select 서브쿼리 예제

대장균의 크기에 따라 분류하기1

SELECT ID, 
CASE 
    WHEN SIZE_OF_COLONY <= 100 THEN 'LOW' 
    WHEN (SIZE_OF_COLONY > 100 AND SIZE_OF_COLONY <= 1000) THEN 'MEDIUM' 
    ELSE 'HIGH' 
        END AS SIZE
FROM ECOLI_DATA

조건문
case ~ when [조건] then [출력]
else [출력]
end as [컬럼명 별칭]

대장균에 크기에 따라 분류하기2

윈도우 함수를 사용하지 않은 경우

SELECT e1.ID,
    CASE 
        WHEN e1.RN > total.CNT * 0.75 THEN 'CRITICAL'
        WHEN e1.RN > total.CNT * 0.50 THEN 'HIGH'
        WHEN e1.RN > total.CNT * 0.25 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM (
  SELECT x.ID,
         (SELECT COUNT(*) FROM ECOLI_DATA y
          WHERE y.SIZE_OF_COLONY <= x.SIZE_OF_COLONY) AS RN
  FROM ECOLI_DATA x
) AS e1
CROSS JOIN (
  SELECT COUNT(*) AS CNT
  FROM ECOLI_DATA
) AS total
ORDER BY e1.ID ASC;

• 서브쿼리 e1: 각 행(x)에 대해 SIZE_OF_COLONY보다 작거나 같은 값이 몇 개인지 세어 순위(RN) 를 흉내냄
• 또 다른 서브쿼리 total: 전체 행 수 CNT 계산
• CASE문으로 RN 기반 등급 분류
• 마찬가지로 ID 기준 오름차순 정렬

순위 함수 사용

SELECT A.ID, 
    CASE
        WHEN A.RN >= 0.75 THEN 'CRITICAL'
        WHEN A.RN >= 0.5 THEN 'HIGH'
        WHEN A.RN >= 0.25 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM (
    SELECT ID, 
    PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY) AS RN
    FROM ECOLI_DATA) A
ORDER BY A.ID ASC;

RANK() OVER : 순위 부여, 동일한 값일경우 같은값 부여 + 이후는 건너뜀 (1,1,3,4...)
DENSE_RANK() OVER : 순위 부여, 같은값 부여 + 안건너뜀 (1,1,2,3,3,4...)
PERCENT_RANK() OVER : 백분율 순위 계산

오프라인/온라인 판매 데이터 통합하기

세로로 합치기(합집합)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'

UNION ALL

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') as SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

UNION ALL: 중복 허용, 컬럼의 개수를 맞춰주어야 합치기 가능
NULL AS 속성: 없는 값은 NULL로 표기 하겠다.

특정 세대의 대장균 찾기

행비교 IN 함수

SELECT C.ID
FROM ECOLI_DATA C
WHERE C.PARENT_ID
    IN (SELECT B.ID
        FROM ECOLI_DATA B
        WHERE B.PARENT_ID 
            IN (SELECT A.ID
            FROM ECOLI_DATA A
            WHERE A.PARENT_ID IS NULL))

null이면 1세대, parent_id가 1세대인것이 2세대, parent_id가 2세대인 것이 3세대로 찾았다.

멸종위기의 대장균 찾기

재귀문

WITH RECURSIVE GENERATION AS (
    SELECT ID, PARENT_ID, 1 AS GEN
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    
    SELECT E.ID, E.PARENT_ID, GEN + 1 AS GEN
    FROM ECOLI_DATA E
    JOIN GENERATION G ON E.PARENT_ID = G.ID
)
SELECT COUNT(*) AS COUNT, GEN AS GENERATION
FROM GENERATION G
WHERE NOT EXISTS (SELECT PARENT_ID FROM ECOLI_DATA C WHERE C.PARENT_ID = G.ID)
GROUP BY GEN
ORDER BY GEN