프로그래머스 SQL 고득점 Kit - String, Date 문제 풀이 (Oracle)

프로그래머스 SQL 고득점 Kit 문제 풀이 

 위 링크에서 String,Date 문제를 풀고 풀이를 기록하는 게시글입니다. 

 

 

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

- 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼

- 같은날 대여하고, 반납하는 경우에는 대여기간을 0일로 계산하기 때문에 1일을 더해서 해당경우에 대한 대여기간 일수를 계산해야한다.!! 주의!!

SELECT HISTORY_ID, CAR_ID, TO_CHAR(START_DATE,'YYYY-MM-DD') START_DATE, 
        TO_CHAR(END_DATE,'YYYY-MM-DD') END_DATE,
        CASE 
            WHEN (END_DATE-START_DATE+1 >= 30)
            THEN '장기 대여'
            ELSE '단기 대여'
        END RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE TO_CHAR(START_DATE, 'YYYY-MM') = '2022-09'
ORDER BY HISTORY_ID DESC

 

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

SELECT '/home/grep/src/'||B.BOARD_ID||'/'||B.FILE_ID||FILE_NAME||FILE_EXT FILE_PATH
FROM USED_GOODS_BOARD A JOIN USED_GOODS_FILE B
ON A.BOARD_ID = B.BOARD_ID
WHERE A.VIEWS =(SELECT MAX(VIEWS)
                FROM USED_GOODS_BOARD)
ORDER BY B.FILE_ID DESC

 

 

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

값이 다를때 join을해야하면 CASE문으로 값을 동일하게만들어준 후 join

SELECT A.HISTORY_ID,CASE
                        WHEN A.DUR_CHAR IS NULL
                        THEN A.DUR * A.DAILY_FEE
                        ELSE A.DUR * A.DAILY_FEE *(100-B.DISCOUNT_RATE)/100
                    END FEE
        
FROM
(SELECT CAR_ID, HISTORY_ID, DAILY_FEE, DUR,CASE WHEN DUR >= 90 THEN '90일 이상'
                                                ELSE (CASE WHEN DUR >= 30 THEN '30일 이상'
                                                      ELSE (CASE WHEN DUR >= 7 THEN '7일 이상'
                                                            ELSE NULL END)
                                                      END)
                                                END DUR_CHAR
FROM (SELECT B.CAR_ID, A.DAILY_FEE, B.HISTORY_ID, B.END_DATE-B.START_DATE+1 DUR
        FROM CAR_RENTAL_COMPANY_CAR A RIGHT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
        ON B.CAR_ID = A.CAR_ID
        WHERE A.CAR_TYPE = '트럭')) A LEFT JOIN
        (SELECT DURATION_TYPE, DISCOUNT_RATE
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
        WHERE CAR_TYPE='트럭') B
ON A.DUR_CHAR = B.DURATION_TYPE
ORDER BY 2 DESC, 1 DESC

 

 

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

alias를 따옴표로지정할떄는 큰따옴표쓰기!

SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE,'YYYY-MM-DD') OUT_DATE,
(CASE WHEN OUT_DATE IS NULL THEN '출고미정'
ELSE (CASE WHEN OUT_DATE < TO_DATE('2022-05-02' ,'YYYY-MM-DD')
     THEN '출고완료'
     ELSE '출고대기'
     END)
END) AS "출고여부"
FROM FOOD_ORDER 
ORDER BY ORDER_ID

 

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

DECODE로 조건을 쓴다.

SELECT BOARD_ID,WRITER_ID, TITLE, PRICE, 
DECODE(STATUS, 'SALE', '판매중','RESERVED', '예약중', 'DONE', '거래완료') STATUS
FROM USED_GOODS_BOARD 
WHERE TO_CHAR(CREATED_DATE,'YYYY-MM-DD') = '2022-10-05' 
ORDER BY 1 DESC

 

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

SELECT A.APNT_NO, B.PT_NAME, A.PT_NO, A.MCDP_CD, C.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A JOIN PATIENT B
ON A.PT_NO = B.PT_NO JOIN DOCTOR C
ON A.MDDR_ID = C.DR_ID
WHERE A.APNT_CNCL_YN ='N'
AND A.MCDP_CD = 'CS'
AND TO_CHAR(A.APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
ORDER BY A.APNT_YMD

 

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

SUBSTR(컬럼,어디부터뽑을지, 몇개뽑을지)

||는 공백없이 칼럼명을 써줘야함

SELECT USER_ID, NICKNAME, CITY||' '||STREET_ADDRESS1||' '||STREET_ADDRESS2 AS "전체주소" 
, SUBSTR(TLNO, 1,3)||'-'|| SUBSTR(TLNO, 4,4)||'-'||SUBSTR(TLNO,8,4) AS "전화번호"
FROM USED_GOODS_USER 
WHERE USER_ID IN (SELECT A.WRITER_ID
                    FROM USED_GOODS_BOARD A LEFT JOIN USED_GOODS_USER B
                    ON A.WRITER_ID = B.USER_ID
                    GROUP BY A.WRITER_ID
                    HAVING COUNT(A.WRITER_ID) >= 3)
ORDER BY USER_ID DESC

 

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

LEFT RIGHT조인이아닌 맞는값만 조인한다.

SELECT ANIMAL_ID, NAME
FROM (SELECT B.ANIMAL_ID, B.NAME, B.DATETIME-A.DATETIME
FROM ANIMAL_INS A , ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY 3 DESC)
WHERE ROWNUM < 3

 

 

9. 자동차 평균 대여 기간 구하기 (중요!! 잘기억해야함!)

기간이 7일 이상인것을 확인하려면 END -START+1 >= 7이다.

SELECT CAR_ID, ROUND(SUM(END_DATE - START_DATE) / COUNT(CAR_ID)+1,1)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
HAVING  (SUM(END_DATE - START_DATE) / COUNT(CAR_ID)+1) >=7
ORDER BY 2 DESC, 1 DESC

 

 

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

and, or에는 괄호꼭쓰기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS 
WHERE (NAME LIKE '%el%'
OR NAME LIKE '%EL%'
OR NAME LIKE '%El%'
OR NAME LIKE '%eL%')
AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

 

 

 

참고

 

날짜, 시간,분,초 차이계산

(END_DATE - START_DATE)
ROUND((END_DATE - START_DATE)*24,2)
ROUND((END_DATE - START_DATE)*24*60,2)
ROUND((END_DATE - START_DATE)*24*60*60,2)

 

문자처리함수

SELECT INITCAP('Oracle SQL')  #문자열의 첫 번째 문자는 대문자, 나머지는 소문자로 변환하여 반환한다.
SELECT UPPER('Oracle SQL')
SELECT LOWER('Oracle SQL')
SELECT LENGTH('Oracle SQL')
SELECT INSTR('Oracle SQL','L') #위치 반환
SELECT SUBSTR('Oracle SQL',8,1)  #8부터 1개추출
SELECT SUBSTR('Oracle SQL',8)  #처음부터 8개추출
SELECT SUBSTR('Hello Oracle', -2) #뒤에서부터도 자를 수있다.
SELECT REPLACE('Oracle SQL','L','BL')  # 문자열이 완벽히일치하지않으면 대체불가능
SELECT TRANSLATE('hello world', 'hw', 'HW') # 한자한자 매핑해서 바꾸고 매핑되는게없으면제거
SELECT LPAD('Oracle SQL',10,'*') #왼쪽에 지정문자10개채운다. 문자를 오른쪽정렬후
SELECT RPAD('Oracle SQL',10,'*')
SELECT LTRIM('MILLER', 'M')  #옵션안넣으면 공백제거
SELECT RTRIM('MILLER', 'R')
SELECT TRIM()
SELECT CHR(65) # 지정된 수와 일치하는 ASCII코드 리턴
SELECT ASCII('A')  # 아스키코드와 일치하는 숫자리턴