추가된 SQL 문제에 대한 풀이입니다.
SELECT ugb.TITLE
, ugb.BOARD_ID
, ugr.REPLY_ID
, ugr.WRITER_ID
, ugr.CONTENTS
, TO_CHAR(ugr.CREATED_DATE,'YYYY-MM-DD')
FROM USED_GOODS_REPLY ugr
LEFT JOIN USED_GOODS_BOARD ugb
ON ugr.BOARD_ID = ugb.BOARD_ID
WHERE ugb.CREATED_DATE BETWEEN TO_DATE('20221001','YYYYMMDD') AND TO_DATE('20221031','YYYYMMDD')
ORDER BY TO_CHAR(ugr.CREATED_DATE,'YYYY-MM-DD'),ugb.TITLE;
SELECT BOARD_ID
, WRITER_ID
, TITLE
, PRICE
,(CASE WHEN STATUS='SALE' THEN '판매중'
WHEN STATUS='RESERVED' THEN '예약중'
WHEN STATUS='DONE'THEN '거래완료'
ELSE '' END) AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE=TO_DATE('20221005','YYYYMMDD')
ORDER BY BOARD_ID DESC;
SELECT ('/home/grep/src/'||BOARD_ID||'/'||FILE_ID||FILE_NAME||FILE_EXT) FILE_PATH
FROM USED_GOODS_FILE ugf
WHERE BOARD_ID=(
SELECT BOARD_ID FROM USED_GOODS_BOARD WHERE VIEWS=(
SELECT MAX(VIEWS) FROM USED_GOODS_BOARD
)
)
ORDER BY FILE_ID DESC;
SELECT USER_ID
, NICKNAME
, (CITY||' '||STREET_ADDRESS1||' '||STREET_ADDRESS2) 전체주소
, (SUBSTR(TLNO,1,3)||'-'||SUBSTR(TLNO,4,4)||'-'||SUBSTR(TLNO,8,4)) 전화번호
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;
SELECT ugb.WRITER_ID USER_ID
, ugu.NICKNAME
, SUM(ugb.PRICE) TOTAL_SALES
FROM USED_GOODS_BOARD ugb
LEFT JOIN
USED_GOODS_USER ugu
ON ugu.USER_ID=ugb.WRITER_ID
WHERE ugb.STATUS='DONE'
GROUP BY ugb.WRITER_ID, ugu.NICKNAME
HAVING SUM(ugb.PRICE)>=700000
ORDER BY TOTAL_SALES;
SUBSTR()
CASE WHEN~THEN ELSE~END