오라클 문제 풀이

sumi Yoo·2023년 1월 19일

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

https://school.programmers.co.kr/learn/courses/30/lessons/151138?language=oracle

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

평균 일일 대여 요금 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151136

SELECT ROUND(AVG(DAILY_FEE)) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

조건에 맞는 도서 리스트 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/144853

SELECT BOOK_ID,
       TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK
WHERE TO_CHAR(PUBLISHED_DATE, 'YYYY') = '2021'
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE

과일로 만든 아이스크림 고르기

https://school.programmers.co.kr/learn/courses/30/lessons/133025

SELECT FH.FLAVOR
FROM FIRST_HALF FH, ICECREAM_INFO IIF
WHERE FH.FLAVOR = IIF.FLAVOR
AND FH.TOTAL_ORDER > 3000
AND IIF.INGREDIENT_TYPE = 'fruit_based'
ORDER BY FH.TOTAL_ORDER DESC

LV.3

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

https://school.programmers.co.kr/learn/courses/30/lessons/59044#qna

SELECT *
FROM(SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I, ANIMAL_OUTS O
WHERE I.ANIMAL_ID = O.ANIMAL_ID(+)
AND O.DATETIME IS NULL
ORDER BY I.DATETIME)
WHERE ROWNUM <= 3

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

https://school.programmers.co.kr/learn/courses/30/lessons/59411?language=oracle

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

있었는데요 없었습니다.

https://school.programmers.co.kr/learn/courses/30/lessons/59043

SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
AND B.DATETIME < A.DATETIME
ORDER BY A.DATETIME

없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

SELECT B.ANIMAL_ID, B.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID(+) = B.ANIMAL_ID
AND A.ANIMAL_ID IS NULL
ORDER BY B.ANIMAL_ID

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

https://school.programmers.co.kr/learn/courses/30/lessons/131113

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

즐겨찾기가 가장 많은 식당 정보 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131123

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, B.FAVORITES
FROM REST_INFO A, 
(SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
FROM REST_INFO
GROUP BY FOOD_TYPE) B
WHERE A.FOOD_TYPE = B.FOOD_TYPE
AND A.FAVORITES = B.FAVORITES
ORDER BY A.FOOD_TYPE DESC

카테고리 별 도서 판매량 집계하기

https://school.programmers.co.kr/learn/courses/30/lessons/144855

SELECT B.CATEGORY, SUM(A.SALES) AS TOTAL_SALES
FROM BOOK_SALES A, 
(SELECT BOOK_ID, CATEGORY
FROM BOOK) B
WHERE A.BOOK_ID = B.BOOK_ID
AND TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY

헤비 유저가 소유한 장소

https://school.programmers.co.kr/learn/courses/30/lessons/77487

SELECT A.ID, A.NAME, A.HOST_ID
FROM PLACES A, (SELECT HOST_ID, COUNT(*)
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(*) > 1) B
WHERE A.HOST_ID = B.HOST_ID
ORDER BY A.ID

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151139

SELECT TO_NUMBER(TO_CHAR(A.START_DATE, 'MM')) AS MONTH, A.CAR_ID, COUNT(A.CAR_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A, (
SELECT CAR_ID, COUNT(*) 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(START_DATE, 'YYYY-MM-DD') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY CAR_ID
HAVING COUNT(*) >= 5) B
WHERE A.CAR_ID = B.CAR_ID
AND TO_CHAR(A.START_DATE, 'YYYY-MM-DD') BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY TO_NUMBER(TO_CHAR(START_DATE, 'MM')), A.CAR_ID
HAVING COUNT(*) != 0
ORDER BY MONTH, A.CAR_ID DESC