[MySQL] 프로그래머스 SQL 고득점 Kit - String, Date

 

중요한 부분은 아니지만 마무리는 해야지

https://school.programmers.co.kr/learn/courses/30/parts/17047

프로그래머스

 

 

---

1. 자동차 평균 대여 기간 구하기

SELECT CAR_ID, 
       ROUND(AVG(DATEDIFF(END_DATE, START_DATE)), 1)+1 AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE, START_DATE))+1 >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

• DATEDIFF() -> 두 날짜 데이터 간의 차를 DAY로 계산해서 반환한다.
• 문제에서 같은 날 대여 후 반납도 1일도 계산하므로 +1을 하여 계산했다.

 

2. 루시와 엘라 찾기

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

 

 

3. 이름에 el이 들어가는 동물 찾기

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE LOWER(NAME) LIKE "%el%" AND ANIMAL_TYPE="Dog"
ORDER BY NAME

• 소문자/대문자 변환을 위해 LOWER()/UPPER() 함수를 사용한다.

 

4. 중성화 여부 파악하기

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') AS '중성화'
FROM ANIMAL_INS

 

 

5. 오랜 기간 보호한 동물(2)

-- 코드를 입력하세요
SELECT AI.ANIMAL_ID, AI.NAME
FROM ANIMAL_OUTS AO JOIN ANIMAL_INS AI ON AO.ANIMAL_ID=AI.ANIMAL_ID
ORDER BY AO.DATETIME-AI.DATETIME DESC
LIMIT 2;

 

 

6. 카테고리 별 상품 개수 구하기

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY_CODE, 
       COUNT(*) AS PRODUCT_COUNT
FROM PRODUCT
GROUP BY CATEGORY_CODE
ORDER BY CATEGORY_CODE ASC;

• LEFT() 함수를 이용해 문자열을 왼쪽부터 추출하여 CATEOGRY_CODE로 지정했다. 이 데이터를 GROUP BY로 그룹화 및 정렬했다
• 이러한 문자열 함수로 LEFT(), RIGHT(), TRIM(), CONCAT(), SUBSTRING() 등이 있다.

 

7. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

-- 코드를 입력하세요
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 FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1)
ORDER BY FILE_ID DESC

• CONCAT() 함수 내에서 여러 문자열을 이을 수 있다.

 

8. 조건에 부합하는 중고거래 상태 조회하기

-- 코드를 입력하세요
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE WHEN STATUS='SALE' THEN '판매중'
WHEN STATUS='RESERVED' THEN '예약중'
WHEN STATUS='DONE' THEN '거래완료'
END
AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE "2022-10-05%"
ORDER BY 1 DESC;

 

 

9. 자동차 대여 기록 별 대여 금액 구하기

SELECT H.HISTORY_ID, 
       ROUND(C.DAILY_FEE * (DATEDIFF(H.END_DATE, H.START_DATE) + 1) * (1 - IFNULL(D.DISCOUNT_RATE, 0) / 100), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
    ON C.CAR_TYPE = D.CAR_TYPE
    AND D.DURATION_TYPE =
        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일 이상'
        END 
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC;

 

• PLAN TABLE에 대해 LEFT JOIN을 수행한다. PLAN에 맞는 데이터가 없더라도 NULL로 채운다.
• LEFT JOIN을 하며 테이블을 생성할 때, DURATION_TYPE은 대여 기간에 따라 다르게 저장한다. 이렇게 하면 HOISTORY_ID 행에 알맞은 DURATION_TYPE이 저장된다.
• ROUND 및 DATEDIFF() 를 적절하게 활용하여 계산한다.

 

10. 특정 옵션이 포함된 자동차 리스트 구하기

-- 코드를 입력하세요
SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE "%네비게이션%"
ORDER BY 1 DESC;

 

 

11. 자동차 대여 기록에서 장기/단기 대여 구하기

-- 코드를 입력하세요
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, "%Y-%m-%d"),DATE_FORMAT(END_DATE, "%Y-%m-%d"), IF(DATEDIFF(END_DATE, START_DATE)+1>=30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE "2022-09%"
ORDER BY 1 DESC

 

 

12. 조건별로 분류하여 주문상태 출력하기

-- 코드를 입력하세요
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,"%Y-%m-%d"), 
CASE WHEN OUT_DATE IS NULL THEN '출고미정' 
WHEN OUT_DATE<'2022-05-02' THEN '출고완료'
ELSE '출고대기'
END
AS '출고여부'
FROM FOOD_ORDER

 

 

13. 대여 기록이 있는 자동차 리스트 구하기

-- 코드를 입력하세요
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE START_DATE LIKE "%10-%")
AND CAR_TYPE='세단'
ORDER BY 1 DESC;

 

 

14. 조건에 맞는 사용자 정보 조회하기

-- 코드를 입력하세요
SELECT USER_ID, NICKNAME, CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) AS '전체주소', 
CONCAT(SUBSTRING(TLNO,1,3),'-',SUBSTRING(TLNO,4,4),'-',SUBSTRING(TLNO,8,4)) AS '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID FROM USED_GOODS_BOARD GROUP BY WRITER_ID HAVING COUNT(*)>=3)
ORDER BY 1 DESC;

• CONCAT()과 SUBSTRING을 적절하게 사용하여 전화번호 형식으로 추출하기!

 

15. 취소되지 않은 진료 예약 조회하기

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

• 조건 잘보기...그리고 출력에 있는 소수점은 그대로 출력하지 않아도 통과된다.
  • 필요하면 SELECT DATE_FORMAT('2022-04-13 09:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')  이런식으로 출력하기

 

16. DATETIME에서 DATE로 형 변환

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS '날짜'
FROM ANIMAL_INS

 

 

17. 연도 별 평균 미세먼지 농도 조회하기

-- 코드를 작성해주세요
SELECT YEAR(YM) AS 'YEAR',ROUND(AVG(PM_VAL1), 2) AS 'PM10', ROUND(AVG(PM_VAL2), 2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR(YM)
ORDER BY YEAR(YM)

 

 

18. 한 해에 잡은 물고기 수 구하기

-- 코드를 작성해주세요
SELECT COUNT(*) FISH_COUNT
FROM FISH_INFO
WHERE TIME LIKE "2021%"

 

 

19. 분기별 분화된 대장균의 개체 수 구하기

SELECT QUARTER, COUNT(ID) AS ECOLI_COUNT
FROM
    (SELECT ID, 
           (CASE 
                WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q'
                WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q'
                WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q'
                WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 10 AND 12 THEN '4Q'
           END) AS QUARTER
    FROM ECOLI_DATA) AS TEMP
GROUP BY 1
ORDER BY 1

• 문제 이해를 잘못해서...오래걸렸다. GROUP BY를 위해 FROM 절에서 미리 QUATER를 지정한다.

 

 

---

회고

sql 기초 끝!

이제 어느정도는 풀 수 있겠지

 

 

까먹지 말쟈