본문 바로가기
728x90
320x100

💻 하나씩 차곡차곡/프로그래머스 (SQL)105

[String, Date/LEVEL2] 루시와 엘라 찾기(MySQL/Oracle) MySQL/OracleSELECT ANIMAL_ID, NAME, SEX_UPON_INTAKEFROM ANIMAL_INSWHERE NAME IN ( 'Lucy' , 'Ella' , 'Pickle' , 'Rogan' , 'Sabrina' , 'Mitty' )ORDER BY ANIMAL_ID; 2023. 6. 16.
[String, Date/LEVEL2] 자동차 평균 대여 기간 구하기 (MySQL/Oracle) MySQLSELECT CAR_ID , ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATIONFROM CAR_RENTAL_COMPANY_RENTAL_HISTORYGROUP BY CAR_IDHAVING AVERAGE_DURATION >= 7ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC날짜 계산할 때 처음에 단순하게 END_DATE-START_DATE 이렇게 계산 했는데, 죽어도 안됨. 알고보니 DATEDIFF() 함수를 사용해야 했어야함. 왜 DATEDIFF 함수를 사용함? -> DATEDIFF 함수는 일수로 계산하고 그냥 (-) 하면 초단위로 계산해서 DATEDIFF 함수가 더 정확함. Or.. 2023. 6. 15.
[String, Date/LEVEL2] 조건에 부합하는 중고거래 상태 조회하기 (MySQL/Oracle) MySQLSELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE WHEN STATUS = 'SALE' THEN '판매중' WHEN STATUS = 'RESERVED' THEN '예약중' WHEN STATUS = 'DONE' THEN '거래완료' END STATUSFROM USED_GOODS_BOARDWHERE CREATED_DATE = '2022-10-05'ORDER BY BOARD_ID DESC OracleSELECT BOARD_ID, WRITER_ID, TITLE, PRICE, CASE WHEN STATUS = 'SALE' THEN '판매중' WHEN STATUS = 'RESERVED' THEN '예약중' W.. 2023. 6. 14.
[String, Date/LEVEL1] 자동차 대여 기록에서 장기/단기 대여 구분하기 (MySQL/Oracle) MySQLSELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE, CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '장기 대여' ELSE '단기 대여' END AS RENT_TYPEFROM CAR_RENTAL_COMPANY_RENTAL_HISTORYWHERE YEAR(START_DATE) = 2022AND MONTH(START_DATE) = 9ORDER BY HISTORY_ID DESCOracleSELECT HISTORY_ID, CAR_ID, TO_CHAR(START_DATE,.. 2023. 6. 13.
[String, Date/LEVEL1] 특정 옵션이 포함된 자동차 리스트 구하기 (MySQL/Oracle) MySQL/OracleSELECT *FROM CAR_RENTAL_COMPANY_CARWHERE OPTIONS LIKE '%네비게이션%'ORDER BY CAR_ID DESC; 2023. 6. 12.
[String, Date/LEVEL2] 카테고리 별 상품 개수 구하기 (MySQL/Oracle) MySQL/OracleSELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTSFROM PRODUCTGROUP BY SUBSTR(PRODUCT_CODE, 1, 2)ORDER BY CATEGORY; 2023. 6. 9.
728x90
320x100

티스토리툴바