[SQL] 코딩 테스트 연습 (1)

[SQL] 코딩 테스트 연습 (1)

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

프로그래머스

 

1. FOOD_PRODUCT 테이블에서 가격이 제일 비싼 식품의 

식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회하는 SQL문을 작성해주세요.

SELECT *
    FROM FOOD_PRODUCT
    WHERE PRICE = (SELECT MAX(PRICE) 
                        FROM FOOD_PRODUCT);

 

2. 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 

저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.

 

결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

SELECT b.AUTHOR_ID, b.AUTHOR_NAME, a.CATEGORY,
        SUM(c.SALES * a.PRICE) AS TOTAL_SALES
    FROM BOOK a 
        INNER JOIN AUTHOR b
            ON a.AUTHOR_ID = b.AUTHOR_ID  
        INNER JOIN BOOK_SALES c
            ON a.BOOK_ID = c.BOOK_ID
    WHERE 1=1
        AND YEAR(SALES_DATE) = 2022 
        AND MONTH(SALES_DATE) = 01
    GROUP BY b.AUTHOR_ID, b.AUTHOR_NAME, a.CATEGORY
    ORDER BY b.AUTHOR_ID, a.CATEGORY DESC;

 

3. 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 

이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE ,COUNT(*) AS COUNT
    FROM ANIMAL_INS
    GROUP BY ANIMAL_TYPE
    ORDER BY 1;

✔ ORDER BY의 숫자 1은 첫번째 컬럼을 의미함

 

4. USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

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

✔회원수를 집계하는 과정에서 중복제거

 

5. 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

WITH RECURSIVE TIME AS(
    SELECT 0 AS NUM
    UNION ALL
    SELECT NUM+1 
    FROM TIME
    WHERE NUM < 23
)

SELECT t.NUM AS HOUR, COUNT(a.ANIMAL_ID) AS COUNT
    FROM TIME t
        LEFT JOIN ANIMAL_OUTS a
        ON t.NUM = HOUR(a.DATETIME)
    GROUP BY HOUR
    ORDER BY HOUR

기존 테이블 (ANIMAL_OUTS)의 날짜 컬럼 (DATETIME) 엔 0~23시의 데이터가 다 있는게 아니라서 

임시 테이블로 0~23시까지의 데이터를 만들어주고 JOIN 해야함

 

🔎 WITH RECURSIVE

SQL 쿼리에서 반복적인 결과를 생성하기 위해 사용되는 기능

WITH 절은 임시 뷰를 생성하여 다른 쿼리에서 재사용 할 수 있는 쿼리블록을 정의하는 데 사용됨

RECULSIVE를 함께 사용하면 임시 뷰를 재귀적으로 확장 할 수 있음 

WITH RECURSIVE TIME AS(
    SELECT 0 AS NUM --NUM이라는 열을 가진 하나의 행을 생성, NUM 값으로 0을 가지고 있음
    UNION ALL --재귀적인 SELECT문을 실행
    SELECT NUM+1  
    FROM TIME
    WHERE NUM < 23 --NUM값이 23이 될때까지 반복함
)

위의 문제에서는 TIME이라는 임시 뷰를 생성하여 0부터 23까지의 숫자를 재귀적으로 생성함

TIME 임시 뷰는 0부터 23까지의 숫자를 포함하는 결과 집합을 생성함

 

🔎LEFT JOIN

LEFT JOIN을 사용하여 TIME 임시 뷰의 모든 시간대를 포함하도록 설정

 

6. PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.

SELECT 
    CASE 
        WHEN PRICE >= 10000 AND PRICE < 20000  THEN 10000
        WHEN PRICE >= 20000 AND PRICE < 30000  THEN 20000
        WHEN PRICE >= 30000 AND PRICE < 40000  THEN 30000
        WHEN PRICE >= 40000 AND PRICE < 50000  THEN 40000
        WHEN PRICE >= 50000 AND PRICE < 60000  THEN 50000
        WHEN PRICE >= 60000 AND PRICE < 70000  THEN 60000
        WHEN PRICE >= 70000 AND PRICE < 80000  THEN 70000
        WHEN PRICE >= 80000 AND PRICE < 90000  THEN 80000
        WHEN PRICE >= 90000 AND PRICE < 100000  THEN 90000
    END AS PRICE_GROUP,
    COUNT(PRODUCT_ID) AS PRODUCTS
    FROM PRODUCT
    GROUP BY PRICE_GROUP
    ORDER BY PRICE_GROUP;

 

🔎CASE WHEN

SQL에서 조건에 따라 다른 결과를 반환하기 위해 사용되는 구문

SELECT
  CASE 
	WHEN 조건1 then 변환값1
 	WHEN 조건2 then 변환값2
	ELSE 그외 변환값3 
  END AS <희망컬럼명>
FROM TABLE

 

7. FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, COALESCE(FREEZER_YN, 'N') AS FREEZER_YN
    FROM FOOD_WAREHOUSE
    WHERE WAREHOUSE_NAME LIKE ('%경기%')
    ORDER BY WAREHOUSE_ID;

 

🔎COALESCE

첫 번째로 지정된 열이 NULL이 아니면 해당 열의 값을 반환하고, NULL이면 대체 값으로 지정한 값을 반환

SELECT COALESCE(Column_Name, 'Replacement_Value') AS New_Column_Name
	FROM Table;

 

8. MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

SELECT m.MEMBER_NAME, r.REVIEW_TEXT, 
    DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE m
    INNER JOIN REST_REVIEW r
    ON m.MEMBER_ID = r.MEMBER_ID
WHERE m.MEMBER_ID = (
        SELECT MEMBER_ID
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY COUNT(MEMBER_ID) DESC
        LIMIT 1
        )
ORDER BY REVIEW_DATE, REVIEW_TEXT;

✔ 서브쿼리 사용

WHERE m.MEMBER_ID = (
        SELECT MEMBER_ID
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY COUNT(MEMBER_ID) DESC
        LIMIT 1
        )

REST_REVIEW 테이블에서 MEMBER_ID를 그룹화 한 뒤 리뷰 개수를 기준으로 내림차순으로 정렬한 결과 중

가장 상위 1개의 MEMBER_ID를 선택. 즉 가장 많은 리뷰를 작성한 회원의 리뷰를 조회

 

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

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

 

10. 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

SELECT o.ANIMAL_ID, o.ANIMAL_TYPE, o.NAME
FROM ANIMAL_INS i
    INNER JOIN ANIMAL_OUTS o
    ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE 1=1
    AND i.SEX_UPON_INTAKE LIKE ('Intact%')
    AND (o.SEX_UPON_OUTCOME LIKE ('Spayed%') OR o.SEX_UPON_OUTCOME LIKE ('Neutered%'))