: SQL에서 주어진 값의 상대적인 위치를 백분율로 나타내는 함수

• 특정 값이 전체 데이터에서 어느 정도 위치에 있는지 알 수 있다.
• 범위를 0에서 1 사이의 값으로 표현한다.
• 주로 데이터의 상위/하위 백분위 계산이 필요할 때 사용한다.

PERCENT_RANK() 함수의 동작 원리

PERCENT_RANK()는 특정 행의 순위를 다른 행들과 비교하여 백분위로 반환한다. 백분율 값은 0에서 1 사이로 주어지며, 해당 값이 전체 데이터에서 몇 %에 위치하는지를 의미한다. 예를 들어:

• 0에 가까운 값은 가장 상위에 위치한다는 의미
• 1에 가까운 값은 가장 하위에 위치한다는 의미

계산 방법:

PERCENT_RANK()는 다음과 같은 공식으로 계산:

• Rank of the row: 해당 행의 순위를 의미
• Total number of rows: 전체 데이터의 행 개수를 의미

=> 가장 첫 번째로 정렬된 값(가장 큰 값)은 0에 가까운 값을 갖고, 가장 마지막에 정렬된 값(가장 작은 값)은 1에 가까운 값을 갖는다.

대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

[풀이]

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

[코드리뷰]

1. 서브쿼리 이 서브쿼리는 대장균의 크기를 기준으로 각 객체에 대해 백분위 순위를 계산한다.

   SELECT ID,
    PERCENT_RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS PER
   FROM ECOLI_DATA

• PERCENT_RANK()함수:
  • 각 대장균 개체의 크기를 내림차순으로 정렬한 후, 각 개체가 전체에서 어느 정도 상위에 위치하는지 백분위(퍼센트)로 표시한다.
  • 결과는 0과 1 사이의 값으로 출력된다. -> 0: 가장 큰 값, 1: 가장 작은 값
  • OVER(ORDER BY SIZE_IF_COLONY DESC) : 크기를 기준으로 내림차순 정렬하여 가장 큰 대장균이 가장 높은 순위를 갖도록 함

2. CASE문 : 백분위로 계산된 PER 값에 따라 개체를 네 가지 등급으로 분류

   CASE 
    WHEN A.PER <= 0.25 THEN 'CRITICAL'
    WHEN A.PER <= 0.5 THEN 'HIGH'
    WHEN A.PER <= 0.75 THEN 'MEDIUM'
    ELSE 'LOW'
   END AS COLONY_NAME

• 적절한 구간에 해당하는 등급 이름을 반환함

3. ORDER BY A.ID : ID를 기준으로 오름차순 정렬

기억해!

PERCENT_RANK()함수가 있는지도 몰랐다..

PERCENT_RANK()함수 : SQL에서 주어진 값의 상대적인 위치를 백분율로 나타내는 함수

• 특정 값이 전체 데이터에서 어느 정도 위치에 있는지 알 수 있다.
• 범위를 0에서 1 사이의 값으로 표현한다.
• 이 함수는 주로 데이터의 상위/하위 백분률 계산이 필요할 때 사용한다.

OVER()의 역할? : PERCENT_RANK()윈도우 함수에 OVER()절을 사용해 특정 기준에 따라 계산을 수행한다. -> 어떤 정렬 순서로 백분위 순위를 매길 것인지 지정할 수 있다. -> 해당 함수가 어떻게 이루어질지 결정 => 정렬 기준을 제공하는 역할을 함

GROUP BY를 사용할 때,

• SELECT에 있는 모든 컬럼은 GROUP BY 절에 포함되거나 집계 함수로 처리되어야 한다.
• 집계 함수를 사용하지 않은 경우, GROUP BY 절에서 명시적으로 그룹화된 컬럼만 SELECT할 수 있다.

이를 위반할 경우, 오류가 발생한다.

1. 오류 발생 예시 (잘못된 쿼리)

   SELECT ID, FISH_TYPE, MAX(LENGTH)
   FROM FISH_INFO
   GROUP BY FISH_TYPE;

이 쿼리는 FISH_TYPE별로 그룹화하면서 ID와 LENGTH도 선택하고 있다. 하지만 ID는 GROUP BY 절에 포함되지 않고, MAX(LENGTH)는 집계 함수로 처리되었으므로 ID가 어떤 값을 선택해야 하는지 MySQL이 모호하게 생각한다. ONLY_FULL_GROUP_BY 모드에서는 이 쿼리가 오류를 일으킨다.

2. 수정된 쿼리 (올바른 쿼리)

   SELECT FISH_TYPE, MAX(LENGTH)
   FROM FISH_INFO
   GROUP BY FISH_TYPE;

   여기서는 FISH_TYPE으로 그룹화하고, MAX(LENGTH)로 가장 큰 길이만 집계하므로 오류가 발생하지 않는다. 이 쿼리는 명확한 그룹화와 집계를 사용했기 때문에 ONLY_FULL_GROUP_BY 모드에서도 정상적으로 작동한다.

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.

물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요. 결과는 물고기의 ID에 대해 오름차순 정렬해주세요. 단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

[내가 푼 풀이(오답)]

SELECT
    I.ID, -- 가장 큰 물고기의 ID
    N.FISH_NAME, -- 물고기 이름
    I.LENGTH -- 길이
FROM FISH_INFO I
INNER JOIN FISH_NAME_INFO N
ON I.FISH_TYPE = N.FISH_TYPE
GROUP BY I.FISH_TYPE
HAVING MAX(I.LENGTH)
ORDER BY I.ID
;

-- 이 코드가 실행되지 않는 이유는 SELECT문에 FISH_TYPE컬럼이 없기 때문이다.
-- 

• 이 코드가 아래와 같은 오류가 나는 이유는 :
  • SELECT문에 FISH_TYPE컬럼이 없기 때문이다.
  • GROUP BY 절에 포함되지 않거나 집계함수로 처리되지 않은 모든 컬럼이 명시적으로 그룹필 되어야 한다.

[내가 푼 풀이(정답)]

-- 물고기 종류 별 가장 큰 물고기
SELECT
    I.ID, -- 가장 큰 물고기의 ID
    N.FISH_NAME, -- 물고기 이름
    I.LENGTH -- 길이
FROM FISH_INFO I
INNER JOIN FISH_NAME_INFO N
ON I.FISH_TYPE = N.FISH_TYPE
WHERE I.LENGTH = (
    SELECT MAX(LENGTH)
    FROM FISH_INFO
    WHERE FISH_TYPE = I.FISH_TYPE)
ORDER BY I.ID
;

[코드리뷰]

• FISH_TYPE별로 가장 큰 LENGTH를 가진 물고기의 정보를 가져오는 서브쿼리를 사용하였다.
• 가장 큰 LENGTH를 반환하여 WHERE절에서 해당 값과 일치하는 행만 메인쿼리에서 반환하도록 만들었다.

기억해!

GROUP BY를 사용할 때, SELECT절의 모든 열은 GROUP BY절에 포함되거나, 집계함수를 통해 명확하게 정의되어야 한다. => 그룹화되지 않거나 집계되지 않은 열을 SELECT절에 포함시키는 것은 허용되지 않는다.

더 이상 업그레이드할 수 없는 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.

[내가 푼 풀이(정답)]

SELECT
    I.ITEM_ID,
    I.ITEM_NAME,
    I.RARITY
FROM ITEM_INFO I
INNER JOIN ITEM_TREE T
ON I.ITEM_ID = T.ITEM_ID
WHERE T.ITEM_ID NOT IN (SELECT T.PARENT_ITEM_ID
                        FROM ITEM_TREE T
                        WHERE T.PARENT_ITEM_ID IS NOT NULL
                        GROUP BY T.PARENT_ITEM_ID)
ORDER BY I.ITEM_ID DESC
;

[코드리뷰]

• INNER JOIN을 통해 두 가지 테이블을 하나로 조인하였다.
• JOIN한 후 TREE테이블의 PARENT_ITEM_ID 컬럼에 없는 ITEM_ID를 추출해 내야 하기 때문에 (업그레이드를 할 수 없는 ID)
  • 서브쿼리를 사용하여 TREE테이블의 PARENT_ITEM_ID만을 추출하였다.
  • PARENT_ITEM_ID컬럼 데이터 중 NULL값이 있었기 때문에 NULL값이 아닌 것만 필터링 하였다.
  • 또한 중복되지 않도록 PARENT_ITEM_ID별로 그룹화하였다.
• 더이상 업그레이드 할 수 없다는 것은 PARENT_ITEM_ID에 없는 ITEM_ID라는 뜻이니까 NOT IN을 사용하였다.

기억해!

1. 일단 데이터 자체, 문제 자체가 이해되지 않았다.
   • 더 이상 업그레이드 할 수 없는 데이터?가 무엇을 의미하는지 정확히 파악하기 힘들었다.
   • A -> B *: B는 A의 부모 아이템 ID이다. 즉, "A는 B로 업그레이드 할 수 있다."는 의미였다.
   • => 더 이상 업그레이드를 할 수 없는 ITEM_ID는 PARENT_ITEM_ID에 없는 아이템인 셈이다.*
   • 이걸 파악하는데 조금 시간이 걸렸다....
2. 포함되지 않아야 하기 때문에 NOT IN을 사용하였다.

문제 FISH_INFO에서 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여 잡은 수, 최대 길이, 물고기의 종류를 출력하는 SQL문을 작성해주세요. 결과는 물고기 종류에 대해 오름차순으로 정렬해주시고, 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해주세요.

컬럼명은 물고기의 종류 'FISH_TYPE', 잡은 수 'FISH_COUNT', 최대 길이 'MAX_LENGTH'로 해주세요.

[내가 푼 풀이(오답)]

SELECT
    COUNT(ID) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33
ORDER BY FISH_TYPE
;

• 오답 이유:
  • 같은 출력문이 나오긴 하지만 틀린 풀이라고 나온다.
  • 이유는 데이터 안에 있는 NULL값을 그대로 두었기 때문이다.
  • LENGTH 데이터에 NULL값이 있는지 확인하지 않았다.
  • 즉, 10cm이하인 물고기들은 10cm로 취급하는 조건을 충족시키지 못했다.

[내가 푼 풀이(정답)]

SELECT
    COUNT(ID) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(IFNULL(LENGTH,10)) >= 33
ORDER BY FISH_TYPE
;

[코드리뷰]

FISH_TYPE을 기준으로 그룹화하여 HAVING절을 사용해 LENGTH의 평균이 33cm 이상인 것으로 계산한다. +IFNULL()을 사용하여 NULL이 아니라면 LENGTH를, NULL이라면 10을 반환하여 계산한다.

기억해!

IFNULL(컬럼명, 'NULL일 경우 대체 값')

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

[내가 푼 풀이(오답)]

SELECT
    MONTH(START_DATE) AS MONTH,
    CAR_ID,
    COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31')
GROUP BY CAR_ID, MONTH
HAVING COUNT(HISTORY_ID) >= 5
ORDER BY MONTH, CAR_ID DESC
;

• 오답 이유:
  • 월별 그룹화하기 전에 5회 이상 대여된 자동차를 필터링 하지 않았다.

  • 서브쿼리에서 단순히 CAR_ID만 선택하고 있으므로 개별 CAR_ID가 총 몇 회 대여되었는지 알 수 없다.

  • HAVING COUNT(HISTORY_ID) >= 5의 위치 문제

  • 자동차별 총 대여 횟수가 5회 이상인 경우가 아닌, 월별로 그룹화한 뒤에 필터링하고 있다.

  • 메인쿼리에 대여 시작일을 기준으로 월별로 그룹화하지 않았다.

[내가 푼 풀이(정답)]

SELECT
    MONTH(START_DATE) AS MONTH,
    CAR_ID,
    COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
    START_DATE BETWEEN '2022-08-01' AND '2022-11-01'
    AND CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE START_DATE BETWEEN '2022-08-01' AND '2022-11-01'
                GROUP BY CAR_ID
                HAVING COUNT(CAR_ID) >= 5)
GROUP BY CAR_ID, MONTH(START_DATE)
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC
;

[코드리뷰]

문제에서의 조건은 다음 4개이다.

1. 대여 시작일을 기준으로 2022년 8월 ~ 10일까지 총 대여 횟수 >= 5
2. 월 별 자동차 ID별 총 대여횟수
3. 월을 기준으로 오름차순, 월이 같다면 자동차 ID를 기준으로 내림차순
4. 특정 월의 총 대여 횟수가 0인 경우 -> 결과에서 제외

• 서브쿼리를 사용하여 1번 조건 충족

    SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE START_DATE BETWEEN '2022-08-01' AND '2022-11-01'
                GROUP BY CAR_ID
                HAVING COUNT(CAR_ID) >= 5

  -> 서브쿼리를 통해 2022년 8월부터 10월까지 대여횟수가 5회 이상인 CAR_ID만 추출
• 월별 자동차 ID별 총 대여횟수 RECORDS

    GROUP BY CAR_ID, MONTH(START_DATE)

  -> CAR_ID를 기준으로, MONTH(START_DATE)를 기준으로 그룹화
• 특정 월의 총 대여 횟수가 0인 경우 -> 결과에서 제외

    HAVING RECORDS > 0

  기억해!

  여러 가지를 고려해야 하는 문제였다. HISTORY_ID를 COUNT해야할지 CAR_ID를 COUNT해야 할지 헷갈렸다. 서브쿼리를 사용하는게 아직 익숙하지 않다.

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

[내가 푼 풀이(정답)]

SELECT
    ID,
    NAME,
    HOST_ID
FROM PLACES
WHERE HOST_ID IN(SELECT HOST_ID FROM PLACES
                GROUP BY HOST_ID
                 HAVING COUNT(HOST_ID) >= 2)
ORDER BY ID
;

[코드리뷰]

• 서브쿼리를 통해 두 개 이상의 공간을 등록한 '헤비 유저'의 HOST_ID만 반환 -> 헤비 유저 정의, 그런 유저들이 등록한 공간만 조회할 수 있도록 필터링

기억해!

Operand should contain 1 column(s)

오류 설명

• 오류 메시지: Operand should contain 1 column(s)
• 이 오류는 SQL에서 서브쿼리(하위 쿼리)가 하나의 컬럼을 반환해야 하는데, 여러 컬럼을 반환하려 할 때 발생한다.

오류 원인

쿼리의 IN 조건문에서 문제 발생:

ON F.BOARD_ID = B.BOARD_ID IN (SELECT B.BOARD_ID, MAX(VIEWS) FROM USED_GOODS_BOARD ORDER BY VIEWS DESC)

IN 연산자는 하나의 컬럼이나 값만 비교할 수 있지만, 서브쿼리가 B.BOARD_ID와 MAX(VIEWS) 두 개의 컬럼을 반환하려 하기 때문에 이 오류가 발생한다.

해결 방법

서브쿼리에서 하나의 컬럼만 반환하도록 수정해야 한다.

결론

WHERE절에서 서브쿼리를 사용할 시 조건을 건 컬럼을 똑같이 SELECT절에 넣어야 한다.

    WHERE (컬럼1, 컬럼2) IN (SELECT(컬럼1, 컬럼2)
                            FROM 테이블명)

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

[내가 푼 풀이(오답)]

SELECT
    CONCAT("/home/grep/src/", F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE F
INNER JOIN USED_GOODS_BOARD B
ON F.BOARD_ID = B.BOARD_ID IN (SELECT B.BOARD_ID, MAX(VIEWS)
                               FROM USED_GOODS_BOARD
                               ORDER BY VIEWS DESC) -- 조회수가 가장 높은 게시물 BOARD_ID
ORDER BY F.FILE_ID DESC
LIMIT 1
;

SQL 실행 중 오류가 발생하였습니다. Operand should contain 1 column(s)

[내가 푼 풀이(정답)]

SELECT
    CONCAT("/home/grep/src/", BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (SELECT BOARD_ID -- 조회수가 가장 높은 BOARD_ID
                  FROM USED_GOODS_BOARD
                  ORDER BY VIEWS DESC
                  LIMIT 1)
ORDER BY FILE_ID DESC
;

[코드리뷰]

• 오답의 이유 :
  1. 서브쿼리에는 앞에 작성한 컬럼들을 그대로 적어야 한다. -> 오류 발생
  2. '게시글 ID를 기준으로 디렉토리 구분'을 놓침
• WHERE절을 통해 조회수가 가장 높은 BOARD_ID 필터링

        WHERE BOARD_ID = (SELECT BOARD_ID FROM USED_GOODS_BOARD
        ORDER BY VIEWS DESC
        LIMIT 1)

• BOARD_ID를 기준으로 디렉토리가 구분되기 때문에 첨부파일 경로의 기본 파일 경로 뒤에 추가되어야 한다.

기억해!

• 출력문에 JOIN되는 테이블의 컬럼명이 반드시 포함되어야 하는 경우가 아니라면 => JOIN을 사용하지 않고 WHERE절을 통해 원하는 조건을 바탕으로 필터링이 가능하다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

[내가 푼 풀이(오답)]

SELECT
    CAR_ID,
    CASE
        WHEN END_DATE <= '2022-10-16' THEN '대여 중'
        WHEN END_DATE > '2022-10-16' THEN '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

END_DATE를 기준으로 END_DATE가 '2022-10-16'보다 작거나 같으면 '대여중'이고 2022-10-16 이후이면 '대여 가능'일 것이다 라는 전제하에 코드를 작성하였다.

-> 문제는 '2022-10-16'보다 빠르게 반납을 했다면 대여 가능한 것이 아닌가? 라는 생각이 들었다.

그럼 BETWEEN을 사용해야겠다고 생각했다.

SELECT
    CAR_ID,
    CASE
        WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

BETWEEN을 활용했지만 오류가 났다. 왜?

데이터를 확인해 본 결과, 하나의 자동차가 여러 번 대여되었다가 반납되었다. 그래서 각 CAR_ID를 기준으로 MIN(START_DATE)와 MAX(END_DATE)를 확인했다.

SELECT
    CAR_ID,
    MIN(START_DATE),
    MAX(END_DATE)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

그렇다면 MIN(START_DATE)와 MAX(END_DATE)사이에 '2022-10-16'이 있으면 '대여중'인 것이고, 없으면 '대여 가능'이 아닌가?

SELECT
    CAR_ID,
    CASE
        WHEN '2022-10-16' BETWEEN MIN(START_DATE) AND MAX(END_DATE) THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

... 아니래...왜...?

아!!!!!! 같은 CAR_ID로 여러 번 대여를 하고, 자동차를 반납하는 날짜와 다시 대여를 하는 날짜에는 차이가 있다. ex. 위의 사진처럼 같은 CAR_ID의 START_DATE와 END_DATE를 살펴보면, 8월 4일부터 8월 8일까지 자동차를 대여되었고, 이후 8월 14일부터 대여가 시작되었다. 즉, START_DATE와 END_DATE가 이어지지 않는다!!!! 그렇기 때문에 MIN, MAX를 활용하는 것 자체가 잘못되었다. CAR_ID의 BETWEEN START_DATE AND END_DATE를 하나씩 살펴보고 '2022-10-16'이 있다면 대여 중인 것이다. => 그렇기 때문에 서브쿼리가 필요한 것이다!!!!!

[정답 풀이]

SELECT
    CAR_ID,
    CASE
        WHEN CAR_ID IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE) THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
;

기억해!

어려워...

대장균 개체의 크기가 100 이하라면 'LOW', 100 초과 1000 이하라면 'MEDIUM', 1000 초과라면 'HIGH' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류(SIZE)를 출력하는 SQL 문을 작성해주세요.이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

[내가 푼 풀이(정답)]

SELECT
    ID,
    CASE
        WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
        WHEN SIZE_OF_COLONY > 100 AND SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
        WHEN SIZE_OF_COLONY > 1000 THEN 'HIGH'
    END AS SIZE
FROM ECOLI_DATA
ORDER BY ID
;

[코드리뷰]

CASE WHEN을 사용하여 대장균 개체의 크기에 따라 분류하고 ID를 오름차순으로 정렬하여 출력한다.

기억해!

• 조금 더 간단하게 작성할 수 없을까?

• CASE문은 조건이 위에서부터 순차적으로 평가되므로, 범위를 좁히는 방법을 생각해볼 수 있다.

  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;

• 조건을 좀 더 간단하게 하면?

• 가독성 향상, 오류 발생 가능성 감소, 쿼리 성능 향상, 유지보수성 개선

HR_DEPARTMENT와 HR_EMPLOYEES 테이블을 이용해 부서별 평균 연봉을 조회하려 합니다. 부서별로 부서 ID, 영문 부서명, 평균 연봉을 조회하는 SQL문을 작성해주세요.

평균연봉은 소수점 첫째 자리에서 반올림하고 컬럼명은 AVG_SAL로 해주세요. 결과는 부서별 평균 연봉을 기준으로 내림차순 정렬해주세요.

[내가 푼 풀이(정답)]

-- 부서별 평균 연봉
SELECT
    D.DEPT_ID, -- 부서ID
    D.DEPT_NAME_EN, -- 영문 부서명
    ROUND(AVG(E.SAL)) AS AVG_SAL -- 평균연봉
FROM HR_DEPARTMENT D
INNER JOIN HR_EMPLOYEES E
ON D.DEPT_ID = E.DEPT_ID
GROUP BY D.DEPT_ID
ORDER BY AVG_SAL DESC
;

[코드리뷰]

• GROUP BY를 통해 DEPT_ID별로 그룹화
• AVG()함수를 통해 같은 부서의 사원들의 평균 연봉을 구함
• ROUND()함수를 사용하고, 별다른 옵션을 추가하지 않아 소수점 첫째 자리에서 반올림하였다.

기억해!

ROUND()는 지정한 자리에서 반올림하는 함수

• ROUND('수치값', '반올림 자릿수')

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

[내가 푼 풀이(정답)]

-- 중고 거래 게시물을 3건 이상 등록
-- 전체 주소 = 시 + 도로명 주소 + 상세 주소
-- 전화번호 XXX-XXXX-XXXX
SELECT
    U.USER_ID,
    U.NICKNAME,
    CONCAT(U.CITY, ' ', U.STREET_ADDRESS1, ' ', U.STREET_ADDRESS2) AS '전체주소',
    CONCAT(SUBSTRING(U.TLNO, 1, 3), '-',SUBSTRING(U.TLNO, 4, 4),'-',SUBSTRING(U.TLNO, 8, 12)) AS '전화번호'
FROM USED_GOODS_USER U
INNER JOIN USED_GOODS_BOARD B
ON U.USER_ID = B.WRITER_ID
GROUP BY U.USER_ID
HAVING COUNT(B.WRITER_ID) >= 3
ORDER BY U.USER_ID DESC
;

[코드리뷰]

• 중고 거래 게시물 3건 이상

  • USED_GOODS_BOARD 테이블에 같은 작성자 이름으로 작성된 게시물이 3개 이상인 WRITER_ID를 찾는다.
  • GROUP BY를 통해 WRITER_ID별로 그룹화하고 그 수가 3개 이상인 것만 필터링한다.

• 전체 주소는 시 + 도로명 주소 + 상세 주소 가 함께 출력되도록 해야한다.

  • CONCAT()함수를 활용하여 문자열을 합친다.

• 전화번호의 경우 하이픈 문자열을 삽입해야한다.

  • SUBSTRING()함수를 사용하여 전체 전화번호를 원하는 만큼 자르고, 자른 문자열 사이에 '-'하이픈을 삽입하는 CONCAT()함수를 사용한다.

기억해!

CONCAT(string1, string2, string3, ...) : 여러 문자열을 하나로 합쳐주는 역할을 한다. SUBSTRING(string, 시작위치, 길이) -> string에서 시작위치부터 길이만큼 출력한다.

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

[내가 푼 풀이(정답)]

-- 입양 간 기록은 있는데, 보호소에 들어온 기록이 없는
SELECT
    O.ANIMAL_ID,
    O.NAME
FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE O.ANIMAL_ID IS NOT NULL
AND I.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID
;

[코드리뷰]

• LEFT JOIN을 통해 없는 데이터는 NULL이 될 수 있게 테이블을 결합하였다.
• 입양 간 기록은 있는 데, 보호소에 들어온 기록이 없는 = ANIMAL_OUTS에는 기록이 있지만, ANIMAL_INS에는 기록이 없는 동물의 데이터

• WHERE O.ANIMAL_ID IS NOT NULL AND I.ANIMAL_ID IS NULL

• 두 가지 조건을 모두 충족하는 동물의 데이터만을 필터링하여 조회했다.

기억해!

JOIN의 종류를 다시 정리 해야겠다.

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

[내가 푼 풀이(오답)]

-- 음식종류별 > 즐겨찾기 수가 가장 많은 식당
SELECT
    FOOD_TYPE,
    REST_ID,
    REST_NAME,
    MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC
;

웨..? -> GROUP BY 후 그냥 MAX를 해버리는 이는 최대값이 아니라 테이블의 최상단 값을 가져와서 서브쿼리로 최대 값을 따로 찾아줘야 한다...... https://school.programmers.co.kr/questions/38854

[정답 코드]

SELECT
    FOOD_TYPE,
    REST_ID,
    REST_NAME,
    FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES)
IN (SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC
;

[코드리뷰]

• 오답코드의 경우, 그룹별 최대 FAVORITES값을 반환하는 것만 처리하고, REST_ID, REST_NAME과 같은 나머지 값들은 그 중 어떤 행의 값을 선택할지 알 수 없다. => 즉, MAX(FAVORITES)는 계산되지만, 이와 연결된 식당 ID나 이름을 정확히 최대 즐겨찾기 수와 매칭되지 않음.
• 정답코드의 경우, 서브쿼리로 각 음식 종류별 최대 즐겨찾기 수를 구한 후, 그 최대값과 해당 FOOD_TYPE을 다시 본쿼리에서 사용 => FOOD_TYPE별로 FAVORITES가 가장 큰 행만 선택할 수 있다.

기억해!

MAX()함수만으로는 그룹화된 데이터에서 정확히 어떤 행이 최대값인지 알 수 없기 때문에 서브쿼리로 정확한 최대값을 포함하는 행을 조회하는 과정이 필요하다.

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

[내가 푼 풀이(정답)]

-- 완료된 중고 거래의 총 금액이 70만원 이상인 사람
SELECT
    USER_ID,
    NICKNAME,
    SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_USER U
INNER JOIN USED_GOODS_BOARD B
ON U.USER_ID = B.WRITER_ID
WHERE B.STATUS = 'DONE'
GROUP BY USER_ID
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES
;

[코드리뷰]

• USER_ID와 WRITER_ID를 기준으로 두 테이블 결합
• GROUP BY를 사용해 USER_ID를 기준으로 그룹화 = USER_ID별로 그룹화
• HAVING절을 사용해 USER_ID로 그룹화되어 있는 데이터들의 PRICE의 합계를 구한다.
• WHERE절을 통해 완료된 중고 거래 필터링

기억해!

HAVING절은 반드시 GROUP BY로 그룹화된 컬럼을 대상으로 한다.

• SUM함수를 기준으로 정렬 -> WHERE절을 사용할 수 없기 때문에 HAVING절을 사용한다.

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

[내가 푼 풀이(오답)]

-- 자동차 종류가 '세단'인 자동차
-- 10월에 대여를 시작한 기록이 있는 자동차
SELECT
    R.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR R
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON R.CAR_ID = H.CAR_ID
WHERE CAR_TYPE = '세단' AND START_DATE LIKE '2022-10%'
ORDER BY CAR_ID DESC
;

• 중복 제거를 하지 않아서 오답

[내가 푼 풀이(정답)]

-- 자동차 종류가 '세단'인 자동차
-- 10월에 대여를 시작한 기록이 있는 자동차
SELECT
    DISTINCT(R.CAR_ID) AS CAR_ID
FROM CAR_RENTAL_COMPANY_CAR R
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON R.CAR_ID = H.CAR_ID
WHERE CAR_TYPE = '세단' AND START_DATE LIKE '2022-10%'
ORDER BY CAR_ID DESC
;

[코드리뷰]

• INNER JOIN을 통해 CAR_RENTAL_COMPANY_CAR와 CAR_RENTAL_COMPANY_RENTAL_HISTORY를 결합
• WHERE절을 통해 CAR_TYPE이 '세단'이고 대여 시작일이 '10월'인 두 가지 조건을 만족하는 데이터로 필터링
• DISTINCT를 통해 중복 제거

기억해!

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

[내가 푼 풀이(정답)]

-- 보호 시작일보다 입양일이 더 빠른 동물
SELECT
    I.ANIMAL_ID,
    I.NAME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME
;

[코드리뷰]

• LEFT JOIN을 사용해 ANIMAL_INS에 있는 모든 동물 데이터를 가져오고, 입양 기록이 있는 경우에만 ANIMAL_OUTS의 데이터를 조인한다.
• 입양 기록이 없으면 ANIMAL_OUTS의 값은 NULL
• WHERE I.DATETIME > O.DATETIME을 통해 보호 시작일이 입양일보다 이후인 동물만 조회함

기억해!

• LEFT JOIN을 사용할 때, 조인된 테이블의 값이 NULL값인 경우는?

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

[내가 푼 풀이(오답)]

-- 코드를 입력하세요
-- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리 => 이름, 보호 시작일
SELECT
    NAME, -- 이름
    DATETIME -- 보호시작일
FROM ANIMAL_INS
WHERE ANIMAL_ID
NOT IN (SELECT DATETIME
   FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3
;

[내가 푼 풀이(정답)]

-- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리 => 이름, 보호 시작일
SELECT
    NAME, -- 이름
    DATETIME -- 보호시작일
FROM ANIMAL_INS
WHERE ANIMAL_ID
NOT IN (SELECT ANIMAL_ID
   FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3
;
-- 입양을 못 간 = ANIMAL_OUTS에 없는 동물 

[다른 풀이]

SELECT
    I.NAME,
    I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME
LIMIT 3
;

[코드리뷰]

• '아직 입양을 못 간' = ANIMAL_OUTS에 없는 동물
• ANIMAL_INS 테이블을 기준으로 ANIMAL_OUTS에 데이터가 없는 동물들만 추출하여 출력하면 된다.
• 첫 번째 오답 코드의 경우,
  • 입양 날짜가 없을 것이라고 생각하여 그것에 초점을 맞춰 풀이를 진행하여 오답이 되었다.
• 두 번째 정답 코드의 경우,
  • 입양 날짜를 기준으로 필터링 한 것이 아니라 OUTS테이블에 ANIMAL_ID가 없다는 것에 초점을 맞춰 정답이 되었다.
• 세 번째 다른 풀이는,
  • LEFT JOIN을 활용하여 INS테이블과 OUTS테이블을 결합하였고, LEFT JOIN의 경우 데이터가 일치하지 않는 부분은 NULL값으로 채워지기 때문에
  • O.ANIMAL_ID가 NULL값인 데이터를 필터링 하였다.

기억해!

• 또 다른 풀이가 있을까...