3/9 기준 새로 SQL 고득점 Kit문제가 올라왔길래, 이를 풀어보고자 한다!
✅ SELECT
🎈 조건에 부합하는 중고거래 댓글 조회하기
SELECT TITLE, BOARD.BOARD_ID, REPLY_ID, REPLY.WRITER_ID, REPLY.CONTENTS, LEFT(REPLY.CREATED_DATE,10) AS CREATED_DATE
FROM USED_GOODS_BOARD AS BOARD
INNER JOIN USED_GOODS_REPLY AS REPLY ON BOARD.BOARD_ID = REPLY.BOARD_ID
WHERE DATE_FORMAT(BOARD.CREATED_DATE, '%Y-%m') = '2022-10'
ORDER BY REPLY.CREATED_DATE, TITLE< 풀이 과정 >
두 테이블에서 각 필드를 가져오는데, USED_GOODS_REPLY에서 작성자 ID, 댓글내용, 작성일을 가져오고 USED_GOODS_BOARD에서는 게시글 ID와 INNER JOIN으로 두 테이블의 키인 게시글 ID를 가져온다.
WHERE 절로 2022-10월만 가져와야 하고, ORDER BY로 요구 조건대로 정렬하여 출력해준다.
✅ GROUP BY
🎈 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
SELECT CAR_ID, (CASE WHEN CAR_ID IN
(SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE '2022-10-16' BETWEEN LEFT(START_DATE, 10) AND LEFT(END_DATE, 10))
THEN '대여중'
ELSE '대여 가능'
END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC< 풀이 과정 >
CAR_ID를 SELECT하고 AVAILABILITY를 위해 subquery와 CASE 문을 활용하여 주어진 CAR_ID의 WHERE절 조건이 만족하는 CAR_ID는 THEN 대여중으로, 아닌 경우 ELSE 대여가능 처리 이후 END하여 컬럼명을 정하고 GROUP BY, ORDER BY하여 구현하였다.
🎈 조건에 맞는 사용자 정보 조회하기
SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B
JOIN USED_GOODS_USER AS U ON U.USER_ID = B.WRITER_ID
WHERE B.STATUS = 'DONE'
GROUP BY USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES< 풀이 과정 >
USER 테이블에서 USER_ID, NICKNAME을 가져오고 BOARD테이블의 PRICE를 합산한 결과를 TOTAL_SALES로 출력하는 문제.
이때 JOIN을 활용하여 USER의 USER_ID, BOARD의 WRITER_ID를 키로 연결하고, BOARD테이블의 STATUS가 DONE인 것들에 한해 GROUP BY를 진행해 TOTAL_SALES가 700000이상인 ROW만 출력하는 문제
✅ STRING, DATE
🎈 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
SELECT CONCAT('/home/grep/src/',B.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE F
JOIN USED_GOODS_BOARD B ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(B1.VIEWS) FROM USED_GOODS_BOARD B1)
ORDER BY F.FILE_ID DESC< 풀이 과정 >
FILE 테이블에서 BOARD_ID, FILE_ID, FILE_NAME, FILE_EXT를 /로 연결하고 앞부분에 /home/grep/src/를 추가하여 CONCAT으로 합쳐준 컬럼을 FILE_PATH로 지정한다.
이후 BOARD 테이블에서의 MAX값을 뽑아내 가장 큰 VIEW를 가진 FILE_PATH를 출력하되, 게시글 ID인 FILE_ID를 기준으로 내림차순 정렬하여 출력해준다.
🎈 조건에 부합하는 중고거래 상태 조회하기
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE STATUS WHEN 'SALE' THEN '판매중'
WHEN 'RESERVED' THEN '예약중'
WHEN 'DONE' THEN '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE LEFT(CREATED_DATE, 10) = '2022-10-05'
ORDER BY BOARD_ID DESC< 풀이 과정 >
CASE WHEN THEN 구문을 활용하여 주어진 STATUS가 각각 SALE, RESERVED, DONE일 때 변환하여 STATUS로 출력하고 게시글 등록일이 22년 10월 5일인 ROW만 출력하는 문제.
🎈 조건에 맞는 사용자와 총 거래금액 조회하기
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, 11)) AS '전화번호'
FROM USED_GOODS_USER U
INNER JOIN USED_GOODS_BOARD B ON B.WRITER_ID = U.USER_ID
GROUP BY USER_ID
HAVING COUNT(USER_ID) >= 3
ORDER BY USER_ID DESC< 풀이 과정 >
USED_GOODS_USER테이블에서 CITY, STREET_ADDRESS1, STREET_ADDRESS2를 빈칸과 함께 CONCAT을 활용하여 전체주소로 만들고, SUBSTRING과 하이픈 문자열을 함께 CONCAT하여 전화번호를 만든다.
그 이후 INNER JOIN으로 WRITER_ID 와 USER_ID를 묶고 GROUP BY를 기준으로 3건 이상이면 USER_ID 기준으로 내림차순하여 정렬하도록 구현했다.
