프로그래머스 SQL 문제풀이1

대여 기록이 존재하는 자동차 리스트 구하기

MYSQL

SELECT DISTINCT B.CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE A.CAR_ID = B.CAR_ID AND
    A.CAR_TYPE = '세단' AND
    MONTH(B.START_DATE)= '10'
		ORDER BY 1 DESC;

ORACLE

SELECT DISTINCT B.CAR_ID
    FROM CAR_RENTAL_COMPANY_CAR A, CAR_RENTAL_COMPANY_RENTAL_HISTORY B
    WHERE A.CAR_ID = B.CAR_ID AND
    A.CAR_TYPE = '세단' AND
    EXTRACT(MONTH FROM B.START_DATE)= '10'
    ORDER BY 1 DESC;

날짜 쿼리
  - MYSQL
      - MONTH(), YEAR(),DAY()
  - 오라클
       - 날짜에서 월만 추출하기 :
EXTRACT(MONTH FROM DATE_OF_BIRTH)= '3’

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

-- 코드를 입력하세요
SELECT CAR_ID,  MAX(CASE WHEN TO_DATE('2022-10-16','YYYY-MM-DD') BETWEEN START_DATE AND END_DATE THEN '대여중'
                ELSE '대여 가능' END)  AS AVAILABILITY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID
    ORDER BY 1 DESC;

오랜 기간 보호한 동물(1)

SELECT NAME,DATETIME
    FROM ANIMAL_INS
    WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
    ORDER BY DATETIME FETCH FIRST 3 ROWS ONLY;

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

- oracle

SELECT ANIMAL_ID, NAME
    FROM(
SELECT  A.ANIMAL_ID  AS ANIMAL_ID, A.NAME AS NAME, A.DATETIME-B.DATETIME AS PERIOD
    FROM ANIMAL_OUTS A INNER JOIN ANIMAL_INS B
    ON A.ANIMAL_ID = B.ANIMAL_ID)
    ORDER BY PERIOD DESC FETCH FIRST 2 ROWS ONLY;

- mysql

SELECT ANIMAL_ID
     , NAME
  FROM (
        SELECT A.ANIMAL_ID
             , A.NAME
             , DATEDIFF(B.DATETIME, A.DATETIME) AS DATE_DIFF
          FROM ANIMAL_INS A
          JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
       ) A
 ORDER BY DATE_DIFF DESC
 LIMIT 2;