[SQL] 코딩테스트 연습 Lv3

# 1.조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
SELECT CONCAT('/home/grep/src/', F.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE F
LEFT JOIN USED_GOODS_BOARD B ON F.BOARD_ID = B.BOARD_ID
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC

# 2.조건에 맞는 사용자 정보 조회하기
SELECT USER_ID, NICKNAME, 
    CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS '전체주소', 
    CONCAT(LEFT(TLNO,3), '-', MID(TLNO,4,4), '-', RIGHT(TLNO,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 USER_ID DESC

# 3.자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
SELECT CAR_ID, 
    CASE WHEN CAR_ID IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE '2022-10-16' 
                            BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') 
                            AND DATE_FORMAT(END_DATE, '%Y-%m-%d')) 
    THEN '대여중' ELSE '대여 가능' 
    END AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

# 4.조건별로 분류하여 주문상태 출력하기
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') OUT_DATE, 
    CASE WHEN OUT_DATE IS NULL THEN '출고미정'
        WHEN OUT_DATE < '2022-05-02' THEN '출고완료'
        ELSE '출고대기'
    END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID
                  
# 5.헤비 유저가 소유한 장소
SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
                 FROM PLACES
                 GROUP BY HOST_ID
                 HAVING COUNT(*)>=2)
ORDER BY ID