💁‍♂️ SQL - SELECT 33 문항

SELECT

1. 조건에 부합하는 중고거래 댓글 조회하기(Level 1)

SELECT UGB.title AS title,
       UGB.board_id AS board_id,
       UGR.reply_id AS reply_id,
       UGR.writer_id AS writer_id,
       UGR.contents AS contents,
       DATE_FORMAT(UGR.created_date, '%Y-%m-%d') AS created_date
FROM USED_GOODS_BOARD UGB JOIN USED_GOODS_REPLY UGR
ON UGB.board_id = UGR.board_id
WHERE DATE_FORMAT(UGB.created_date, '%Y-%m') = '2022-10'
ORDER BY UGR.created_date, UGB.title;

• https://school.programmers.co.kr/learn/courses/30/lessons/164673
• 기억해야 할 부분은 다음과 같다.

1. DATE_FORMAT() 함수 사용법
2. 실무에서는 = 조건보다는 범위 조건으로 조회해야 함
   • UGB.created_date에 인덱스 있을 경우 범위 스캔으로 동작하기 때문에 범위 조건으로 작성해야 훨씬 효율적임

2. 과일로 만든 아이스크림 고르기(Level 1)

SELECT FH.flavor AS flavor
FROM FIRST_HALF FH JOIN ICECREAM_INFO II
ON FH.flavor = II.flavor
WHERE FH.total_order > 3000
AND II.ingredient_type = 'fruit_based'
ORDER BY FH.total_order DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/133025
• 기억해야 할 부분은 다음과 같다.

1. flavor 컬럼에 인덱스가 있으면 조인 성능이 좋아진다.
   • 문제에서 해당 열은 FH의 기본 키이므로 II.flavor에 인덱스를 걸어주는 것이 좋다.
2. 값 분포에 따라 FH.total_order, II.ingredient_type 인덱스를 걸면 효율적이다.
3. FH.total_order의 경우 인덱스에 의해 정렬 비용이 개선 여부는 실행 계획을 확인해야 한다.

3. 흉부외과 또는 일반외과 의사 목록 출력하기(Level 1)

SELECT dr_name,
       dr_id,
       mcdp_cd,
       DATE_FORMAT(hire_ymd, '%Y-%m-%d') AS hire_ymd
FROM DOCTOR
WHERE mcdp_cd = 'CS' OR mcdp_cd = 'GS'
ORDER BY hire_ymd DESC, dr_name ASC;

• https://school.programmers.co.kr/learn/courses/30/lessons/132203
• 기억해야 할 부분은 다음과 같다.

1. OR 연산자는 인덱스를 잘 못 타는 경우가 있음
   • IN ('CS', 'GS')와 같이 개선 가능
2. 정렬 연산 비용을 줄이기 위해 (hire_ymd, dr_name) 복합 인덱스 고려할 수 있음
   • 단 대용량 데이터거나, 잦은 쿼리 등 정렬 최적화가 필요한 상황일 때만 해당됨

4. 12세 이하인 여자 환자 목록 출력하기(Level 1)

SELECT pt_name,
       pt_no,
       gend_cd,
       age,
       IFNULL(tlno, 'NONE') AS tlno
FROM PATIENT
WHERE age <= 12 
AND gend_cd = 'W'
ORDER BY age DESC, pt_name ASC;

• https://school.programmers.co.kr/learn/courses/30/lessons/132201
• 대용량 테이블이라면 gend_cd, age 기준으로 복합 인덱스를 고려할 수 있다.

5. 인기있는 아이스크림(Level 1)

SELECT flavor
FROM FIRST_HALF
ORDER BY total_order DESC, shipment_id ASC;

• https://school.programmers.co.kr/learn/courses/30/lessons/133024

6. 조건에 맞는 도서 리스트 출력하기(Level 1)

SELECT book_id, 
       DATE_FORMAT(published_date, '%Y-%m-%d') AS published_date
FROM BOOK
WHERE YEAR(published_date) = '2021'
AND category = '인문'
ORDER BY published_date;

• https://school.programmers.co.kr/learn/courses/30/lessons/144853

7. 평균 일일 대여 요금 구하기(Level 1)

SELECT ROUND(AVG(daily_fee), 0) AS average_fee
FROM CAR_RENTAL_COMPANY_CAR
WHERE car_type = 'SUV'
GROUP BY car_type;

• https://school.programmers.co.kr/learn/courses/30/lessons/151136
• 기억해야 할 부분은 다음과 같다.

1. round() 함수의 첫 번째 인자에는 대상 값이 들어가고, 두 번째 인자에는 반올림할 자릿수에 대한 값이 들어간다.
2. GROUP BY 키워드를 통해 특정 열의 값들을 집계 처리할 수 있다.

8. 3월에 태어난 여성 회원 목록 출력하기(Level 2)

SELECT member_id,
       member_name,
       gender,
       DATE_FORMAT(date_of_birth, '%Y-%m-%d') AS date_of_birth
FROM MEMBER_PROFILE
WHERE MONTH(date_of_birth) = '03'
AND gender = 'W'
AND tlno IS NOT NULL
ORDER BY member_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/131120
• 기억해야 할 부분은 다음과 같다.

1. MONTH() 함수를 쓰면 인덱스를 타지 못하고, 범위 조건 방식을 고려해야 한다.
2. gender 조건과 같이 카디널리티가 낮은 값(M, W 두 개뿐)이면 단독 인덱스 효용이 거의 없고, 다른 컬럼과 복합 인덱스를 고려할 수 있다.
3. IS NOT NULL 조건은 보통 인덱스 효율성이 낮다.
4. ORDER BY 절에 기본 키가 들어간다면 정렬 비용이 거의 없고, 그렇지 않다면 인덱스 설계를 고려할 수 있다.

9. 강원도에 위치한 생산공장 목록 출력하기(Level 1)

SELECT factory_id,
       factory_name,
       address
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY factory_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/131112
• 기억해야 할 부분은 다음과 같다.

1. %가 뒤에만 있는 경우는 인덱스 활용이 가능하다.
2. 정렬을 고려하여 (address, factory_id) 복합 인덱스를 두는 것이 가장 이상적이다.

10. 서울에 위치한 식당 목록 출력하기(Level 4)

SELECT RI.rest_id AS rest_id,
       RI.rest_name AS rest_name,
       RI.food_type AS food_type,
       RI.favorites AS favorites,
       RI.address AS address,
       ROUND(AVG(RR.review_score), 2) AS score
FROM REST_INFO RI JOIN REST_REVIEW RR
ON RI.rest_id = RR.rest_id
WHERE address LIKE '서울%'
GROUP BY RI.rest_id
ORDER BY score DESC, favorites DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/131118
• 의문이 들었던 부분은 LEFT JOIN을 처리해야 리뷰가 없는 식당 목록도 조회할 수 있을텐데 왜 해당 처리를 요구하지 않는지 궁금했고, 문제에도 이 부분을 따로 언급하지 않은 것이 아쉬웠다.
• 기억해야 할 부분은 다음과 같다.

1. address열에서 서울에 위치하는 경우 "서울특별시%" 또는 "서울시%" 값을 갖고 있었기 때문에 항상 데이터가 표준화되어 있는지 여부를 확인해야 한다.
2. 만약 리뷰가 없는 식당 또한 처리한다면, 평균 계산 시 NULL일 때의 처리도 추가해야 한다.

11. 재구매가 일어난 상품과 회원 리스트 구하기(Level 2)

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;

• https://school.programmers.co.kr/learn/courses/30/lessons/131536
• HAVING 키워드는 GROUP BY에 대한 조건을 적용할 수 있다.

12. 모든 레코드 조회하기(Level 1)

SELECT *
FROM ANIMAL_INS
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59034

13. 역순 정렬하기(Level 1)

SELECT name, 
       datetime
FROM ANIMAL_INS
ORDER BY animal_id DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/59035

14. 오프라인/온라인 판매 데이터 통합하기(Level 4)

SELECT *
FROM (
    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'
    
    UNION
    
    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'
) A
ORDER BY A.sales_date, A.product_id, A.user_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/131537
• 서브 쿼리 시 최종 결과에 정렬을 하려면 Alias를 써야 한다.

15. 아픈 동물 찾기(Level 1)

SELECT animal_id,
       name
FROM ANIMAL_INS
WHERE intake_condition = 'Sick'
ORDER BY animal_id;

https://school.programmers.co.kr/learn/courses/30/lessons/59036

16. 어린 동물 찾기(Level 1)

SELECT animal_id,
       name
FROM ANIMAL_INS
WHERE intake_condition != 'Aged'
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59037
• 의문이 드는 부분은 나이가 들고 아픈 동물의 경우 intake_condition 값이 'Aged', 'Sick' 두 값을 가질 수 있을텐데 열을 따로 분리해야 하지 않을까 싶다.

17. 동물의 아이디와 이름(Level 1)

SELECT animal_id, 
       name
FROM ANIMAL_INS
ORDER BY animal_id;

• https://school.programmers.co.kr/learn/courses/30/lessons/59403

18. 여러 기준으로 정렬하기(Level 1)

SELECT animal_id,
       name,
       datetime
FROM ANIMAL_INS
ORDER BY name ASC, datetime DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/59404

19. 상위 n개 레코드(Level 1)

SELECT name
FROM ANIMAL_INS
ORDER BY datetime
LIMIT 1;

• https://school.programmers.co.kr/learn/courses/30/lessons/59405
• LIMIT 키워드를 기억하자.

20. 조건에 맞는 회원수 구하기(Level 1)

SELECT COUNT(*) AS users
FROM USER_INFO 
WHERE YEAR(joined) = '2021' 
AND age >= 20 AND age <= 29;

• https://school.programmers.co.kr/learn/courses/30/lessons/131535

21. 업그레이드 된 아이템 구하기(Level 2)

SELECT CI.item_id,
       CI.item_name,
       CI.rarity
FROM ITEM_INFO PI 
JOIN ITEM_TREE IT ON PI.item_id = IT.parent_item_id
JOIN ITEM_INFO CI ON IT.item_id = CI.item_id
WHERE PI.rarity = 'RARE'
ORDER BY item_id DESC;

• https://school.programmers.co.kr/learn/courses/30/lessons/273711
• 문제 제대로 안 읽고 부모가 업그레이드 된 아이템이라고 생각했는데, 그래서 문제 푸는데 어려웠음
• 기억해야 할 부분은 다음과 같다.

1. 조인 조건에 쓰이는 열의 경우 인덱스 고려 가능
2. 자주 조회할 경우 WHERE 조건에 쓰이는 열 인덱스 고려 가능

22. Python 개발자 찾기(Level 1)

SELECT id,
       email,
       first_name,
       last_name
FROM DEVELOPER_INFOS
WHERE 'Python' IN (skill_1, skill_2, skill_3)
ORDER BY id;

• https://school.programmers.co.kr/learn/courses/30/lessons/276013
• IN 키워드 복수 열에 사용 가능한 사실 기억하기

23. 조건에 맞는 개발자 찾기(Level 2)

-- 실패한 코드
SELECT D.id,
       D.email,
       D.first_name,
       D.last_name
FROM DEVELOPERS D
JOIN SKILLCODES S
ON (D.skill_code & S.code) != 0
WHERE S.name IN ('Python', 'C#')
ORDER BY D.id;

-- 통과한 코드 1
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;

-- 통과한 코드 2
SELECT DISTINCT D.id,
                D.email,
                D.first_name,
                D.last_name
FROM DEVELOPERS D JOIN SKILLCODES S
ON (D.skill_code & S.code) != 0
WHERE S.name IN ('Python', 'C#')
ORDER BY D.id;

• https://school.programmers.co.kr/learn/courses/30/lessons/276034
• 문제를 풀면서 이런 식으로 테이블을 설계하는 것이 적절한 것인지는 의문이 들었다.
• 실패한 코드의 문제점은 개발자가 Python과 C#을 동시에 갖고 있을 경우 중복된 행이 발생한다는 점이다.
• 기억해야 할 부분은 다음과 같다.

1. 비트 연산자를 통한 코드 관리
2. EXISTS 키워드 활용법
3. JOIN 시 중복 행 발생 여부 판단 필요

24. 잔챙이 잡은 수 구하기(Level 1)

SELECT COUNT(*) AS fish_count
FROM FISH_INFO
WHERE length IS NULL;

• https://school.programmers.co.kr/learn/courses/30/lessons/293258

25. 가장 큰 물고기 10마리 구하기(Level 1)

SELECT id,
       length
FROM FISH_INFO
ORDER BY length DESC, id ASC
LIMIT 10;

• https://school.programmers.co.kr/learn/courses/30/lessons/298517

26. 특정 물고기를 잡은 총 수 구하기(Level 2)

SELECT COUNT(*) AS fish_count
FROM FISH_INFO FI JOIN FISH_NAME_INFO FNI
ON FI.fish_type = FNI.fish_type
WHERE FNI.fish_name IN ('BASS', 'SNAPPER');

• https://school.programmers.co.kr/learn/courses/30/lessons/298518

27. 대장균들의 자식의 수 구하기(Level 3)

SELECT PED.id,
       COUNT(CED.parent_id) AS child_count
FROM ECOLI_DATA PED LEFT JOIN ECOLI_DATA CED
ON PED.id = CED.parent_id
GROUP BY PED.id
ORDER BY PED.id;

• https://school.programmers.co.kr/learn/courses/30/lessons/299305
• COUNT를 포함한 집계 함수는 NULL을 포함하지 않는다는 점을 기억해야 한다.

28. 대장균의 크기에 따라 분류하기 1(Level 3)

SELECT id,
	CASE 
		WHEN size_of_colony <= 100  THEN 'LOW'
		WHEN size_of_colony <= 1000 THEN 'MEDIUM'
		ELSE 'HIGH'
	END AS size
FROM ECOLI_DATA
ORDER BY id;

• https://school.programmers.co.kr/learn/courses/30/lessons/299307

29. 특정 형질을 가지는 대장균 찾기(Level 1)

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

• https://school.programmers.co.kr/learn/courses/30/lessons/301646

30. 부모의 형질을 모두 가지는 대장균 찾기(Level 2)

SELECT C.id,
       C.genotype,
       P.genotype AS parent_genotype
FROM ECOLI_DATA C JOIN ECOLI_DATA P 
ON C.parent_id = P.id
WHERE (C.genotype & P.genotype) = P.genotype
ORDER BY C.id;

• https://school.programmers.co.kr/learn/courses/30/lessons/301647

31. 대장균의 크기에 따라 분류하기 2(Level 3)

-- NTILE() 함수 사용
SELECT
    id,
    CASE quartile
        WHEN 1 THEN 'CRITICAL'
        WHEN 2 THEN 'HIGH'
        WHEN 3 THEN 'MEDIUM'
        WHEN 4 THEN 'LOW'
    END AS colony_name
FROM (
    SELECT id,
           NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS quartile
    FROM ECOLI_DATA
) A
ORDER BY id;

-- WITH 키워드 사용
WITH ranked AS (
    SELECT
        id,
        ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS rn,
        COUNT(*) OVER () AS total_cnt
    FROM ECOLI_DATA
)
SELECT
    id,
    CASE
        WHEN rn <= total_cnt * 0.25 THEN 'CRITICAL'
        WHEN rn <= total_cnt * 0.50 THEN 'HIGH'
        WHEN rn <= total_cnt * 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS colony_name
FROM ranked
ORDER BY id;

• https://school.programmers.co.kr/learn/courses/30/lessons/301649
• NTILE()은 데이터 수가 4의 배수일 땐 깔끔히 25%씩, 그 외에는 균등하게 쪼갠다.
• 만약 25%씩 균등하게 나누는 것이 아니라, 비율을 달리 해야 한다면 WITH 키워드를 사용하면 된다.
• 여기서 WITH 키워드는 임시 뷰(일반 CTE)를 정의하는 문법이다.

32. 특정 세대의 대장균 찾기(Level 4)

WITH RECURSIVE ecoli_tree AS (
    SELECT id, 
           parent_id, 
           1 AS depth
    FROM ECOLI_DATA
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT e.id, 
           e.parent_id, 
           et.depth + 1
    FROM ECOLI_DATA e JOIN ecoli_tree et 
    ON e.parent_id = et.id
)
SELECT id
FROM ecoli_tree T
WHERE T.depth = 3
ORDER BY id;

• https://school.programmers.co.kr/learn/courses/30/lessons/301650
• WITH RECURSIVE는 재귀 CTE를 만들 때 사용하는데, 일반적으로 위 코드처럼 UNION ALL을 통해 루트와 자식 노드에 대한 쿼리를 합친다.

33. 멸종위기의 대장균 찾기(Level 5)

WITH RECURSIVE ecoli_tree AS (
    SELECT 
        id,
        parent_id,
        1 AS generation
    FROM ECOLI_DATA
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        ED.id,
        ED.parent_id,
        ET.generation + 1
    FROM ECOLI_DATA ED JOIN ecoli_tree ET
    ON ED.parent_id = ET.id
)
SELECT 
    COUNT(*) AS count,
    generation
FROM ecoli_tree T
WHERE NOT EXISTS (
    SELECT 1
    FROM ECOLI_DATA C
    WHERE C.parent_id = T.id
)
GROUP BY generation
ORDER BY generation;

• https://school.programmers.co.kr/learn/courses/30/lessons/301651
• WHERE NOT EXISTS 절에서는 외부 쿼리 T의 각 행마다 내부 테이블 C 전체를 스캔하고, 이러한 구조를 상관 서브쿼리라고 한다.
• parent_id 열에 인덱스가 있으면, C.parent_id = T.id 조건을 빠르게 검색하여 전체 스캔을 피할 수 있다.
• 그렇지 않으면, 데이터가 많을 경우 비용이 커질 수 있다.