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

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

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

프로그래머스

 

1. USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

SELECT YEAR(o.SALES_DATE) AS YEAR, 
        MONTH(o.SALES_DATE) AS MONTH, 
        COUNT(DISTINCT u.USER_ID) AS PUCHASED_USER,
        ROUND(COUNT(DISTINCT o.USER_ID) / (SELECT COUNT(DISTINCT USER_ID)
                                            FROM USER_INFO
                                            WHERE YEAR(JOINED) ='2021'), 1) AS puchased_ratio
FROM USER_INFO u
    INNER JOIN ONLINE_SALE o
    ON u.USER_ID = o.USER_ID
WHERE 1=1
    AND YEAR(JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

2. 보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

SELECT ANIMAL_ID, NAME,
    CASE
        WHEN SEX_UPON_INTAKE LIKE ('Neutered%') THEN 'O'
        WHEN SEX_UPON_INTAKE LIKE ('Spayed%') THEN 'O'
        ELSE 'X'
    END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

3. 입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A
    JOIN ANIMAL_OUTS B 
    ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF(B.DATETIME, A.DATETIME) DESC
LIMIT 2;

🔎DATEDIFF

두 날짜간의 차이를 계산하는데 사용됨

datepart에는 날짜 차이를 계산할 단위를 지정할 수 있음 (day, week, month, year) default가 day

DATEDIFF(datepart, start_date, end_date)

입양을 간 동물 중, 보호 기간이 가장 길었던 기간을 구해야 하기 때문에

(보호소에 들어온 날짜 - 입양을 간 날짜) 로 계산

 

4. PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY PRODUCT_CODE

🔎SUBSTR

문자열에서 지정된 위치에서 지정된 길이만큼의 부분 문자열을 추출하는데 사용

SUBSTR(string, start_position, length)

예를 들어 "Hello, World!" 문자열에서 "World"를 추출하려면 다음과 같이 사용할 수 있음

SUBSTR('Hello, World!', 8, 5)

 

5. ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

(SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE
    WHERE MONTH(SALES_DATE) = 3)
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 MONTH(SALES_DATE) = 3)
ORDER BY SALES_DATE ,PRODUCT_ID, USER_ID

🔎UNION ALL

두 개 이상의 SELECT 문의 결과를 결합해 하나의 결과 집합으로 만드는 데 사용

UNION ALL은 중복을 제거하지 않고 모든 행을 포함하여 결과를 결합 

(SELECT column1, column2 
    FROM table1)
UNION ALL
(SELECT column1, column2 
    FROM table2)

 

6. REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

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

🔎ROUND

수치 값을 지정된 소수 자릿수로 반올림하는 데 사용되는 함수

일반적으로 소수 자릿수를 지정하는데 사용함

ROUND(number, 소수 자릿수)

예를 들어 아래 예시는 소수점 둘째 자리에서 반올림되어 3.14가 반환

ROUND(3.14159, 2)

아래는 소수점 첫째 자리에서 반올림되어 10.7이 반환

ROUND(10.678, 1)

 

7. ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(PRODUCT_ID) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC

 

8. 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

SELECT A.FLAVOR
FROM FIRST_HALF A
    INNER JOIN JULY B
    ON A.FLAVOR = B.FLAVOR
GROUP BY B.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC
LIMIT 3;

 

9. CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT A.CAR_ID, 
    A.CAR_TYPE, 
    ROUND(A.DAILY_FEE * 30 * (100 - C.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
        ON A.CAR_TYPE = C.CAR_TYPE AND C.DURATION_TYPE = '30일 이상'
WHERE 1=1
    AND A.CAR_TYPE IN ('세단', 'SUV')
    AND A.CAR_ID NOT IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
    )
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

 

10. FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(B.AMOUNT * A.PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT A
    JOIN FOOD_ORDER B
    ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE 1=1
    AND B.PRODUCE_DATE LIKE ('2022-05%')
GROUP BY PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID