프로그래머스 SQL 4단계 문제풀이

 

문제 : 보호소에서 중성화한 동물 이름

 

문제핵심

특정 문자열을 포함하는 데이터 조회하기

 

조건

ANIMAL_INS 테이블의 SEX_UPON_INTAKE 열이 'Intact' 이면서

ANIMAL_OUTS 테이블의 SEX_UPON_OUTCOME 열이 'Neutered' 거나 'Spayed' 인

데이터를 ANIAL_ID 기준 오름차순 정렬

 

WHERE 절에서만 쿼리를 잘 짜면 쉽다

Neutered 와 Spayed 의 조건을 설정할 때, LIKE 를 사용하면 두 줄을 써야하기 때문에

REGEXP 를 사용해서 코드를 조금 짧게 만들었다

SELECT
    i.ANIMAL_ID,
    i.ANIMAL_TYPE,
    i.NAME
FROM
    ANIMAL_INS i INNER JOIN ANIMAL_OUTS o
    ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE
    i.SEX_UPON_INTAKE LIKE 'Intact%'
    AND o.SEX_UPON_OUTCOME REGEXP 'Spayed|Neutered'
ORDER BY
    i.ANIMAL_ID ASC;

 

 

 

문제 :  식품 분류별 가장 비싼 식품의 정보 조회하기

 

문제핵심

그룹 별 최댓값을 가지는 케이스를 어떻게 불러와야하는지

 

조건

카테고리가 과자, 국, 김치, 식용유인 케이스만 조회

CATEGORY 별로 가장 비싼 제품을 조회

가격별로 내림차순 정렬

 

풀이흐름

 

첫번째 코드 :

CTE에서 윈도우 함수로 CATEGORY 별 최대가격을 기록한 열 MAX_PRICE를 생성하고

메인쿼리에서 PRICE 가 MAX_PRICE 인 케이스만 조회하면 될 것이라고 생각했다

간단한 방법이지만 특정한 상황에서는 의도한 것과 다른 결과를 불러올 것 같아서

추천되지 않는다

WITH TBL AS (
    SELECT
        CATEGORY,
        PRODUCT_NAME,
        PRICE,
        MAX(PRICE) OVER (PARTITION BY CATEGORY) AS MAX_PRICE
    FROM
        FOOD_PRODUCT
    WHERE
        CATEGORY IN ('과자', '국', '김치', '식용유')
    )

SELECT
    CATEGORY,
    MAX_PRICE,
    PRODUCT_NAME
FROM
    TBL
WHERE
    PRICE = MAX_PRICE
ORDER BY
    PRICE DESC;

 

두번째 코드 :

WHERE 절에서 서브쿼리를 생성해서 조건에 맞는 CATEGORY와 PRICE 쌍을 불러왔다

그러나 메인쿼리의 FROM 에서 한번, WHERE의 서브쿼리에서 한번 데이터를 스캔하게 되는 것 같아서

데이터의 크기가 크면 조금 느려질 것 같다

SELECT
    CATEGORY,
    PRICE AS MAX_PRICE,
    PRODUCT_NAME
FROM
    FOOD_PRODUCT
WHERE
    (CATEGORY,PRICE) IN (
        SELECT CATEGORY, MAX(PRICE)
        FROM FOOD_PRODUCT
        WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
        GROUP BY CATEGORY
        )
ORDER BY
    PRICE DESC;

 

세번째 코드:

JOIN 을 사용해서 데이터를 두번 스캔하는 일이 없도록 작성했다

JOIN을 사용해도 결국 두개의 테이블을 스캔하긴하지만 이 방법이 더 빠르다고 한다(GPT가 말하길..)

이 방법도 CATEGORY와 PRICE 쌍을 기준으로 불러오는 점에서 두번째 코드와 동일하다

앞으로 이 방법을 애용해야겠다

SELECT
    f.CATEGORY,
    t.MAX_PRICE,
    f.PRODUCT_NAME
FROM
    FOOD_PRODUCT f
    INNER JOIN (
        SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
        FROM FOOD_PRODUCT
        WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
        GROUP BY CATEGORY
        ) AS t
    ON f.CATEGORY = t.CATEGORY
    AND f.PRICE = t.MAX_PRICE
ORDER BY
    PRICE DESC;

 

 

문제 : 5월 식품들의 총매출 조회하기

 

문제핵심

적절히 그룹별 연산 활용하기

 

조건

2022년 5월 데이터만 조회

제품 별 총 매출 열을 생성

총매출 기준 내림차순, 제품ID 기준 오름차순 정렬

 

풀이흐름

두 개의 테이블을 내부조인한다

WHERE 절에서 2022년 5월 데이터로 제한한다

제품 별로 그룹화해서 가격과 판매량을 곱해서 합한 열을 생성한다

문제의 나머지 조건에 맞춰서 정렬한다

SELECT
    o.PRODUCT_ID,
    p.PRODUCT_NAME,
    SUM(o.AMOUNT * p.PRICE) AS TOTAL_SALES
FROM
    FOOD_PRODUCT p INNER JOIN FOOD_ORDER o
    ON p.PRODUCT_ID = o.PRODUCT_ID
WHERE
    o.PRODUCE_DATE REGEXP '2022-05'
GROUP BY
    o.PRODUCT_ID
ORDER BY
    TOTAL_SALES DESC,
    PRODUCT_ID ASC;

 

 

 

문제 : 취소되지 않은 진료 예약 조회하기

 

문제핵심

문제 잘 읽고 조건 잘 보기

 

조건

흉부외과(CS) 진료만 조회

2022-04-13 일 예약만 조회

취소되지 않은 예약만 조회

진료예약날짜 기준 오름차순 정렬

 

풀이흐름

SELECT 하라는 열도 많고 조건이 좀 많아보이지만

서브쿼리도, 그룹화 같은 것도 필요없다

문제에 써있는대로만 조건 추가하고 조회하면된다

4단계스럽지 않은 문제

SELECT
    a.APNT_NO,
    p.PT_NAME,
    p.PT_NO,
    a.MCDP_CD,
    d.DR_NAME,
    a.APNT_YMD
FROM
    APPOINTMENT a
    INNER JOIN DOCTOR d ON a.MDDR_ID = d.DR_ID
    INNER JOIN PATIENT p ON a.PT_NO = p.PT_NO
WHERE
    a.APNT_CNCL_YN = 'N' AND
    a.APNT_YMD REGEXP '2022-04-13' AND
    a.MCDP_CD REGEXP 'CS'
ORDER BY
    a.APNT_YMD ASC;

 

 

문제 : 서울에 위치한 식당 목록 출력하기

 

문제핵심

정확하게 조건 명시하기, 조회할 열 파악하기?

 

조건

서울에 있는 가게만 조회

가게 별 평균 리뷰점수 열 생성

평균리뷰점수 열 소수점 세째자리에서 반올림

평균점수 기준 내림차순, 즐겨찾기 기준 내림차순 정렬

 

문제흐름

선택해야할 열들을 자세히 보면 그룹화를 어떻게 해야할지 감이 잡힌다

평균 리뷰점수 열을 제외하면

REST_ID 별로 NAME,TYPE,FAVORITES,ADDRESS는 하나밖에 없으니

GROUP BY에서 REST_ID로 그룹화를 하면 된다

WHERE 절에서는 '서울' 로 시작하는 주소들을 필터링 해준다

(REGEXP '서울') 을 사용하거나 (LIKE '%서울%') 을 사용하면 오답이다

서울의 가게가 아닌데 '서울' 이라는 글자가 주소 중간에 들어갈 수 있기 때문이다

그리고 문제의 나머지 조건에 맞춰서 정렬 및 열 선택을 한다

SELECT
    i.REST_ID,
    i.REST_NAME,
    i.FOOD_TYPE,
    i.FAVORITES,
    i.ADDRESS,
    ROUND(AVG(r.REVIEW_SCORE), 2) AS SCORE
FROM
    REST_INFO i INNER JOIN REST_REVIEW r
    ON i.REST_ID = r.REST_ID
WHERE
    i.ADDRESS LIKE '서울%'
GROUP BY
    i.REST_ID
ORDER BY
    SCORE DESC,
    FAVORITES DESC;

 

 

문제 : 저자 별 카테고리 별 매출액 집계하기

 

문제핵심

그룹 별 연산 정확하게 하기

 

조건

2022년 1월 데이터만 조회

저자 별, 카테고리 별 매출액을 기록한 열 생성

작가ID 오름차순, 카테고리 내림차순 정렬

 

풀이흐름

먼저 최종 SELECT 에 필요한 열을 확인하고 테이블들을 조인한다

최종 조회할 열과 그룹화할 열들을 잘 살펴보면 거의 동일하다

그냥 GROUP BY로 ID와 CATEGOY 별로 그룹화 한다

판매가 x 판매량을 모두 합해서 새로운 열 TOTAL_SALES 을 생성

WHERE 에서 판매 날짜를 2022년 1월로 제한

문제의 나머지 조건에 맞춰서 정렬 및 열 선택

SELECT
    author.AUTHOR_ID,
    author.AUTHOR_NAME,
    book.CATEGORY,
    SUM(sales.SALES * book.PRICE) AS TOTAL_SALES
FROM
    BOOK book
    INNER JOIN AUTHOR author ON book.AUTHOR_ID = author.AUTHOR_ID
    INNER JOIN BOOK_SALES sales ON book.BOOK_ID = sales.BOOK_ID
WHERE
    sales.SALES_DATE LIKE '2022-01%'
GROUP BY
    author.AUTHOR_ID,
    book.CATEGORY
ORDER BY
    author.AUTHOR_ID ASC,
    book.CATEGORY DESC;

 

 

 

문제 : 년, 월, 성별 별 상품 구매 회원 수 구하기

 

문제핵심

문제 제대로 읽기..

 

조건

연, 월, 성별 기준으로 그룹화해서 회원 수를 기록한 열 생성

연,월, 성별 기준 오름차순 정렬

 

풀이흐름

최종적으로 필요한 열을 확인하고 테이블을 조인한다

연,월을 추출할때는 YEAR() 와 MONTH() 함수를 사용하면 된다

그리고 연,월,성별 기준으로 그룹화한 뒤에 COUNT() 를 사용하면 된다

그러나 여기서 같은 연,월에 여러번 구매한 회원이 있다면 이 케이스들도 모두 조회되기 때문에

COUNT() 안에 DISTINCT를 사용해서 중복된 회원은 집계하지 않도록 한다

문제의 나머지 조건에 맞춰서 정렬 및 열 선택을 한다

 

SELECT
    YEAR(sales.SALES_DATE) AS YEAR,
    MONTH(sales.SALES_DATE) AS MONTH,
    info.GENDER,
    COUNT(DISTINCT sales.USER_ID) AS USERS
FROM
    ONLINE_SALE sales
    INNER JOIN USER_INFO info ON sales.USER_ID = info.USER_ID
WHERE
    info.GENDER IS NOT NULL
GROUP BY
    YEAR(sales.SALES_DATE),
    MONTH(sales.SALES_DATE),
    info.GENDER
ORDER BY
    YEAR(sales.SALES_DATE) ASC,
    MONTH(sales.SALES_DATE) ASC,
    info.GENDER ASC;

 

 

 

문제 : 우유와 요거트가 담긴 장바구니

 

문제핵심

그룹 집계 및 불필요한 데이터 제외

 

조건

milk와 yogurt가 모두 담긴 장바구니 조회

장바구니 ID  오름차순 정렬

 

풀이흐름

먼저 WHERE에서 milk 나 yogurt를 구매한 카트 ID 만 필터링한다

GROUP BY로 CART_ID 별로 그룹화 한다

HAVING 에서 DISTINCT를 사용해서 그룹별 NAME의 고유 카운트 수가 2개인 케이스만 조회한다

그러면 milk 와 yogurt를 모두 구매한 카트만 남게된다

SELECT
    CART_ID
FROM
    CART_PRODUCTS
WHERE
    NAME REGEXP 'Milk|Yogurt'
GROUP BY
    CART_ID
HAVING
    COUNT(DISTINCT NAME) >= 2

 

 

문제 : 주문량이 많은 아이스크림들 조회하기

 

문제핵심

목표에 맞게 정확하게 테이블 합치기

 

조건

7월 , 상반기 테이블의 맛 별 총판매량 열을 생성,

판매량 많은 순서대로 3개의 케이스만 조회

 

풀이흐름

FLAVOR 기준으로 조인해서 두 테이블을 조인하는게 좋을수도 있다

그러나 조인을 하게 되면 7월이나 상반기에 테이블이 하나라도 없으면

조인이 되지 않아 결과에서 제외되기 때문에

UNION ALL 을 사용했다

FLAVOR가 중복된 것을 피하기 위해 FLAVOR 별 TOTAL_ORDER 합을 구해놓고

UNION ALL 로 합친 뒤에

합친 TOTAL_ORDER 순으로 내림차순 정렬 했다

WITH TBL AS (

    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM FIRST_HALF
    GROUP BY FLAVOR

UNION ALL

    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
)

SELECT FLAVOR
FROM TBL
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

 

 

 

 

문제 : 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

 

문제핵심

GROUP BY 잘 이해하기?

 

조건

회원 별 평균점수를 구간별로 나눈 열 생성

외원 별 평균점수를 구간으로 나누어 보너스 열 생성

회원 ID 오름차순 정렬

 

풀이흐름

회원별로 평균 점수를 구하고 평균점수에 따라 등급과 보너스 열을 생성할 것

CASE 에서 매 조건마다 AVG(SCORE) >= n 처럼 작성하면

평균계산을 여러번 하기 때문에 뭔가 비효율적이다

그래서 CTE 로 회원별 평균 점수 AVG_SCORE를 기록한 테이블을 생성한 뒤에

메인 쿼리에서 조인한 뒤에 CASE 에서 사용해서 등급과 보너스 열을 생성했다

문제의 나머지 조건에 맞춰서 정렬 및 열 선택을 하면된다

WITH TBL AS (
    SELECT EMP_NO, AVG(SCORE) AS AVG_SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
    )

SELECT
    e.EMP_NO,
    e.EMP_NAME,
    CASE
        WHEN t.AVG_SCORE >= 96 THEN 'S'
        WHEN t.AVG_SCORE >= 90 THEN 'A'
        WHEN t.AVG_SCORE >= 80 THEN 'B'
        ELSE 'C'
        END AS GRADE,
    CASE
        WHEN t.AVG_SCORE >= 96 THEN e.SAL * 0.2
        WHEN t.AVG_SCORE >= 90 THEN e.SAL * 0.15
        WHEN t.AVG_SCORE >= 80 THEN e.SAL * 0.1
        ELSE 0
    END AS BONUS
FROM
    HR_EMPLOYEES e 
    INNER JOIN  TBL t ON e.EMP_NO = t.EMP_NO
GROUP BY
    t.EMP_NO

 

 

 

 

 

문제 : 그룹별 조건에 맞는 식당 목록 출력하기

 

문제핵심

그룹 별 최댓값을 가진 케이스들 조회하기

 

조건

리뷰를 가장 많이 작성한 회원들의 정보 조회

작성일 기준 오름차순, 작성 텍스트 기준 오름차순 정렬

 

풀이흐름

먼저 ID 별 리뷰 수를 기록한 CTE1을 생성한다

CTE1를 활용해서 가장 많은 리뷰수가 기록된 CTE2를 생성한다

메인쿼리에서 필요한 테이블과 CTE 들을 조인한다

문제의 나머지 조건에 맞게 정렬 및 열 선택을 한다

 

WITH CTE1 AS (
    SELECT MEMBER_ID, COUNT(*) AS REVIEW_COUNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID),

    CTE2 AS (
        SELECT MAX(REVIEW_COUNT) AS MAX_COUNT
        FROM CTE1)
        
SELECT
    member.MEMBER_NAME,
    review.REVIEW_TEXT,
    DATE_FORMAT(review.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM
    CTE1
    INNER JOIN CTE2 ON CTE1.REVIEW_COUNT = CTE2.MAX_COUNT
    LEFT JOIN REST_REVIEW review ON CTE1.MEMBER_ID = review.MEMBER_ID
    LEFT JOIN MEMBER_PROFILE member ON CTE1.MEMBER_ID = member.MEMBER_ID
ORDER BY
    REVIEW_DATE ASC,
    review.REVIEW_TEXT ASC;

 

 

 

문제 : 입양 시각 구하기(2)

 

문제핵심

재귀함수 사용하기

 

조건

시간 별로 입양 수 열 생성

입양수가 0이어도 시간은 나타나야함

시간대 순 정렬

 

풀이흐름

재귀 CTE를 사용해서 모든 시간대가 기록된 행을 만든다

메인쿼리에서 시간별 입양수를 기록한 서브쿼리와 CTE를 조인한다

COALESCE로 결측치는 0으로 바꿔준다

문제의 조건에 맞춰 정렬 및 열을 선택한다

WITH RECURSIVE NUM AS (
    SELECT 0 AS HOUR

    UNION ALL

    SELECT HOUR + 1 FROM NUM
    WHERE HOUR < 23
    )
    
SELECT
    n.HOUR,
    COALESCE(t.DATE_COUNT,0) AS COUNT
FROM 
    NUM n
    LEFT JOIN (
        SELECT HOUR(DATETIME) AS HOUR, COUNT(*) DATE_COUNT
        FROM ANIMAL_OUTS
        GROUP BY HOUR(DATETIME)) t
    ON n.HOUR = t.HOUR
ORDER BY
    HOUR ASC;

 

 

 

문제 : 특정 세대의 대장균 찾기

 

문제 핵심

조인에 대한 적당한 이해

 

조건

3번째 세대의 대장균만 조회

ID 오름차순 정렬

 

풀이흐름

 

첫번째 코드 :

세대가 기록된 열을 만들고 3세대만 조회할 예정,

재귀 CTE로 첫세대(부모세대가 NULL)들을 선택하고

1 값을 가지는 열 GENERATION을 만든다(1세대기 때문)

그 다음으로 다음 세대에 대한 케이스를 추가하기 위해 UNION ALL을 해준다

재귀 CTE로 추가 되는 행은 

CTE의 ID와 ECOLI_DATA 테이블의 PARENT_ID를 INNER JOIN 을 하면

ECOLI_DATA의 ID는 2세대 열이 된다.

동시에 GENERATION + 1을 해서 2세대임으로 알려준다

재귀 CTE의 종료 조건을 명시할 필요는 없다 

새로운 데이터가 생성되지 않으면 자동으로 종료되기 때문이다

이후 메인쿼리에서 CTE를 불러와서 3세대만 선택한다

WITH RECURSIVE TBL AS (
    SELECT
        ID,
        PARENT_ID,
        1 AS GENERATION
    FROM
        ECOLI_DATA
    WHERE
        PARENT_ID IS NULL
    
    UNION ALL

    SELECT
        e.ID,
        e.PARENT_ID,
        GENERATION + 1
    FROM
        ECOLI_DATA e INNER JOIN TBL t
        ON e.PARENT_ID = t.ID
)

SELECT ID
FROM TBL
WHERE GENERATION = 3
ORDER BY ID ASC;

 

 

두번째 코드 :

ECOLI_DATA 테이블에서 부모세대가 NULL인 케이스의 ID와

셀프 조인할 테이블의 PARENT_ID를 조인하면

기존 테이블의 ID는 1세대, 셀프조인한 테이블의 ID는 2세대가 된다

이런 방식으로 2번 셀프 조인을 하면 마지막으로 조인한 테이블의 ID가

3세대가 된다

SELECT
    c.ID
FROM (
    SELECT ID,PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL) a
    INNER JOIN ECOLI_DATA b ON a.ID = b.PARENT_ID 
    INNER JOIN ECOLI_DATA c ON b.ID = c.PARENT_ID
ORDER BY
    c.ID ASC;

 

 

 

문제 : 자동차 대여 기록 별 대여 금액 구하기

 

문제 핵심

적당한 조인 기준 열 생성하기?

 

조건

트럭 데이터만 조회,

대여금, 대여기록, 할인타입에 맞춰 최종 대여금 계산,

대여금, 대여기록 내림차순 정렬

 

풀이흐름

1. CTE로 HISTORY 테이블에서 다음의 열들을 불러온다
HISTORY_ID    (식별 열)
CAR_ID           (CAR 테이블과 조인용)
 대여기간           (대여금 계산용)
 할인타입           (DISCOUNT 테이블과 조인용)

2. CTE로 DISCOUNT 테이블에서 트럭 종류의 케이스만 불러온다
나중에 할인타입 열을 기준으로 조인을 하게 되는데
트럭외에 다른 자동차 종류가 있으면
중복 열이 생기게 된다

3. 메인 쿼리에서 조인한다
대여기록 테이블, 자동차정보 테이블 LEFT JOIN (정렬 기준 CAR_ID)
대여기록 테이블, 할인정보 테이블 LEFT JOIN (조인 기준 DURATION_TYPE)

4. SELECT 에서 (대여금 * 할인율 * 대여기간) 계산
CASE로 대여기간이 7일 미만이라서 '할인없음' 에 해당하는 케이스는
(대여금 * 대여기간) 만 계산해준다

5. 문제의 나머지 조건에 맞춰서 정렬

WITH history AS (
    SELECT 
        HISTORY_ID, CAR_ID, (DATEDIFF(END_DATE, START_DATE) + 1) AS DATE_DIFF,
        CASE
            WHEN (DATEDIFF(END_DATE, START_DATE) + 1) >= 90 THEN '90일 이상'
            WHEN (DATEDIFF(END_DATE, START_DATE) + 1) >= 30 THEN '30일 이상'
            WHEN (DATEDIFF(END_DATE, START_DATE) + 1) >= 7 THEN '7일 이상'
            ELSE '할인없음'
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY),
    
    discount AS (
    SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭')
    
SELECT
    h.HISTORY_ID,
    CASE
        WHEN h.DURATION_TYPE = '할인없음'
        THEN FLOOR(c.DAILY_FEE  * h.DATE_DIFF)
        ELSE FLOOR((c.DAILY_FEE * (1 - 0.01 * d.DISCOUNT_RATE) * h.DATE_DIFF))
    END AS FEE    
FROM
    history h
    LEFT JOIN CAR_RENTAL_COMPANY_CAR c
    ON h.CAR_ID = c.CAR_ID
    LEFT JOIN discount d
    ON h.DURATION_TYPE = d.DURATION_TYPE
WHERE
    c.CAR_TYPE = '트럭'
ORDER BY
    FEE DESC,
    h.HISTORY_ID DESC;

 

 

 

문제 : Front End 개발자 찾기

 

문제핵심

& 연산자 사용법을 알고 있는지

 

조건 

 Front End 개발자의 정보만 조회

ID 오름차순 정렬

 

풀이흐름

이 문제는 & 연산자를 모르면 코드가 참 아쉬워진다

& 연산자는 두 정수를 이진수로 만든 뒤에 비교해서

같은 자리에  둘다 1일때만 1을, 아니면 0을 반환한다

그래서 Front End 분야의 스킬 코드를 모두 합하고

개발자들의 스킬코드와 & 연산자로 비교했을때

0이 아닌 양수이면 해당 케이스는 Front End 개발자인 것이다

CTE나 서브쿼리를 통해 Front End 개발자의 스킬 코드 합을 계산해주고

개발자들의 Skill Code 와 &로 비교하면 된다

 

CTE 사용 풀이

WITH TBL AS (
    SELECT SUM(CODE) AS SUM_CODE
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End')
        
SELECT
    d.ID,
    d.EMAIL,
    d.FIRST_NAME,
    d.LAST_NAME
FROM
    DEVELOPERS d INNER JOIN TBL t
    ON d.SKILL_CODE & t.SUM_CODE > 0
ORDER BY
     d.ID ASC;

CTE 없이 풀이

SELECT
    ID,
    EMAIL,
    FIRST_NAME,
    LAST_NAME
FROM
    DEVELOPERS
WHERE
    SKILL_CODE & (
        SELECT SUM(CODE)
        FROM SKILLCODES
        WHERE CATEGORY = 'Front End'
    ) > 0
ORDER BY
    ID ASC;

 

 

 

 

문제 : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

 

문제핵심

반례 없이 조건 만족하기

 

조건

'세단', 'SUV' 차량만 조회

2022년 11월에 대여가능한 차량만 조회

30일 대여금액이 50~200만 사이인 차량만 조회

할인적용된 30일 대여금 열 생성

대여금액 내림차순, 차종류 오름차순, 차ID 내림차순 정렬

 

풀이흐름

 

1.차종류 제한 조건 충족
메인쿼리에서 사용할 CTE를 생성한다 (CTE를 사용하지 않고 FROM 그대로 작성해도 된다)
차량정보 테이블과 할인정보 테이블을 CAR TYPE 기준 조인을 한다
이 때 CAR TYPE 별로 여러가지 할인이 있기 때문에
중복 케이스가 생성된다 하지만 문제에서는 
30일 기준 할인율만 구하면 되기 때문에 할인종류를 '30일 이상' 으로 제한한다
또한 차 종류도 문제의 조건에 맞게 '세단', 'SUV'로 제한한다
그러면 이 CTE는 [차량ID, 차종류, 대여금, 30일 할인율] 열을 가진
테이블이 된다 (즉, 필수적인 차량 정보만 가진 CTE 생성)

 

 

2. 30일 대여금이 50~200만 이고 11월에 대여가능한 차량 조건 충족
메인쿼리의 WHERE 에서 2가지 조건을 설정한다

- WHERE 절에서 (대여금 x 할인율 x 30일) 이
50만~200만 구간 안에 있는 케이스만 조회하는 조건을 설정

- 서브쿼리를 생성하여 11월에 대여가능한 차량의 ID로 제한
여기서 '11월에 대여가능한 차량' 을 추출하기보단
'11월에 대여중인 차량' 을 제외하는 것이 좋다
나는 NOT IN 을 사용했다

 

 

3. 메인쿼리에서 나머지 조건 충족
FROM 에서 만들어둔 CTE를 가져온다
SELECT 에서 문제가 요구한 열과
30일 대여금을 계산한 열을 선택한다
ORDER BY 에서 문제의 조건에 맞게 정렬한다

 

WITH CAR_INFO AS (
    SELECT
        car.CAR_ID,
        car.CAR_TYPE,
        car.DAILY_FEE,
        discount.DISCOUNT_RATE
    FROM
        CAR_RENTAL_COMPANY_CAR car
        INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN discount
        ON car.CAR_TYPE = discount.CAR_TYPE
    WHERE
        discount.DURATION_TYPE = '30일 이상' AND car.CAR_TYPE IN ('세단','SUV')
    )
    
SELECT
    c.CAR_ID,
    c.CAR_TYPE,
    FLOOR(c.DAILY_FEE * (1 - (c.DISCOUNT_RATE/100)) * 30) AS FEE
FROM
    CAR_INFO c
WHERE
    c.DAILY_FEE * (1 - (c.DISCOUNT_RATE/100)) * 30 BETWEEN 500000 AND 2000000
    AND
    c.CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE NOT (START_DATE > '2022-11-30' OR END_DATE < '2022-11-01'))
ORDER BY
    FEE DESC,
    c.CAR_TYPE ASC,
    c.CAR_ID DESC;

 

 

 

문제 : 언어별 개발자 분류하기

 

문제핵심

비트연산자 활용과 CASE의 적절한 활용

 

조건

개발자의 스킬코드 별로

Front End 스킬 중하나와 Python 스킬을 가지고 있으면 A

C# 스킬을 가지고 있으면 B

Front End 스킬 중 하나를 가지고 있으면 C

로 GRADE를 만들어서

GRADE가 있는 개발자들을 조회

GRADE, ID 오름차순 정렬

 

풀이흐름

CASE를 사용해서 GRADE열을 생성해야한다

그러기 위해선 GRADE 별 조건에 맞는 스킬코드 합을 계산하고

& 연산자로 비교해서 GRADE를 부여하는 것이 좋아보인다

GRADE가 없는 개발자는 NULL을 지정해준다

 

NULL을 나중에 제외해야 하는데 

메인쿼리의 CASE 에서 GRADE를 지정하고

WHERE 에서 NULL 인 데이터를 제외하려고 하면

쿼리 작동 순서에 맞지 않아서 오류가 발생한다 ( 작동순서 WHERE -> SELECT)

그래서 서브쿼리나 CTE 를 사용해서 GRADE를 지정해놓고

메인쿼리에서 NULL을 제외하는 방법을 사용했다

(여기서는 CTE 사용함)

 

또한 CASE 의 조건마다 서브쿼리를 사용하면 연상량이 많아질 것으로 예상되므로

CTE를 하나 더 작성해서 GRADE 별 조건에 맞는 스킬코드 합을 미리 계산해두는 것이 좋아보인다

 

이후 문제의 나머지 조건에 맞춰 정렬 및 열을 선택해서 조회한다

 

(스킬 코드합을 미리 계산한 코드)

WITH CODE_SUM AS (
    SELECT
        (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') AS FRONT_END,
        (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'Python') AS PYTHON,
        (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'C#') AS CSHARP
),
CTE AS (
    SELECT
        CASE
            WHEN SKILL_CODE & c.FRONT_END > 0 AND SKILL_CODE & c.PYTHON > 0 THEN 'A'
            WHEN SKILL_CODE & c.CSHARP > 0 THEN 'B'
            WHEN SKILL_CODE & c.FRONT_END > 0 THEN 'C'
        END AS GRADE,
        ID, EMAIL
    FROM DEVELOPERS, CODE_SUM c
)
SELECT *
FROM CTE
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID;

(스킬 코드합을 미리 계산해두지 않은 코드)

WITH CTE AS (
    SELECT
        CASE
            WHEN SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') > 0 
                AND SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'Python') > 0
            THEN 'A'
            WHEN SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE NAME = 'C#') > 0
            THEN 'B'
            WHEN SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End') > 0
            THEN 'C'
            ELSE NULL
        END AS GRADE,
        ID, EMAIL
    FROM
        DEVELOPERS
    )
    
SELECT *
FROM CTE
WHERE GRADE IS NOT NULL
ORDER BY GRADE ASC, ID ASC;