SQL 고득점 Kit (Oracle 풀이) SELECT 편

유승선 ·2023년 2월 25일

SQL, JPQL

목록 보기

7/8

평균 일일 대여 요금 구하기

SELECT ROUND(avg(daily_fee),0) AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR 
WHERE CAR_TYPE = 'SUV'

그냥 SUV의 평균 일일 대여 요금을 출력하면 되는 문제다. 여기서 사용된 함수는 AVG(daily_fee) 이지만 문제에는 평균 일일 대여 요금 소수 첫 번쨰 자리에서 반올림 하라고 나왔기에 ROUND()를 같이 사용해주었다. 참고로 ROUND 함수 두번째 나오는 숫자는 몇번째 소수점까지 반올림 할지를 얘기하는건데 0은 없어도 괜찮다. 다만 1이나 2가 있을경우 첫번째 숫자 혹은 두번째 숫자까지 반올림 한다고 생각하면 된다. ROUND 함수 참고링크.

3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, TO_CHAR(DATE_OF_BIRTH,'YYYY-MM-DD') DATE_OF_BIRTH 
FROM MEMBER_PROFILE 
WHERE TO_CHAR(DATE_OF_BIRTH,'MM') = '03'
AND TLNO IS NOT NULL 
AND GENDER = 'W'
ORDER BY MEMBER_ID

으악..나 왜 이렇게 모르는게 많은 걸까요..항상 DB에서 데이터 타입을 다룰때는 변환하는 방법이 필요한데 MySQL이랑 다르게 Oracle에서는 TO_CHAR 이라는 함수를 사용해서 날짜 타입을 바꿀 수 있다. YYYY-MM-DD 형태 혹은 MM만 원하는경우 'MM' 으로 쉽게 변경 가능한거같다. 날짜 관련된 함수는 각 SQL마다 다른거같지만 그래도 익혀두면 좋은 함수인거같다. 날짜 함수 참고링크.

서울에 위치한 식당 목록 출력하기

SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, B.SCORE 
FROM REST_INFO A INNER JOIN 
(SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) AS SCORE 
FROM REST_REVIEW 
GROUP BY REST_ID) B
ON A.REST_ID = B.REST_ID 
WHERE A.ADDRESS LIKE '서울%'
ORDER BY B.SCORE DESC,  A.FAVORITES DESC

아무리 생각해도 기본 문제가 아닌데...일단 기본 문제에 있어서 풀어보려고 했다가 망해서 다른 사람의 풀이를 참고했다. 오라클 자체에서 잘 경험 못한 에러를 마주했는데 GROUP BY를 할때 SELECT에 적혔던 모든 목록이랑 같이 해줘야 한다. 안그러면 에러나온다. MySQL같은 경우에는 굳이 다 안해줘도 괜찮았는데 신기하다... 하여튼 이전에 배운 ROUND 함수를 사용해줬지만

REST_ID가 NULL인 부분이 TRUE여서 그냥 LEFT JOIN 했을경우에 아이디가 서로 겹치지 않아서 SCORE이 안나왔다. 그래서 INNER JOIN을 사용해주었는데 섭쿼리로 조인을 한 꽤 흥미로운 문제였다.

12세 이하인 여자 환자 목록 출력하기

-- 코드를 입력하세요
SELECT PT_NAME, PT_NO, GEND_CD, AGE, NVL(TLNO,'NONE') AS TLNO 
FROM PATIENT 
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC

분명히 어디선가 느꼈는데 NVL 함수를 배웠는데 까먹었다. 오라클에서 NULL처리 함수는 NVL 과 NVL2를 사용할 수 있고 NVL2같은 경우 NULL 값이 아닌 경우에도 변수 명을 바꿀 수 있어서 괜찮은거같다. NVL? NVL2? 링크.

재구매가 일어난 상품과 회원 리스트 구하기

-- 코드를 입력하세요
SELECT USER_ID, PRODUCT_ID 
FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID 
HAVING COUNT(PRODUCT_ID) >= 2 
ORDER BY USER_ID ASC, PRODUCT_ID DESC

재구매가 이뤄졌다는것은 해당 컬럼에 대한 카운드가 2 이상인 목록이다. 난 항상 COUNT 같은 함수를 SELECT 문에서만 사용했는데 이렇게 중복을 확인할 수 있게 선 GROUP BY 를 해주고 HAVING 으로 WHERE 절을 대신한 부분이 좀 인상적이었다. 참고 풀이.

상위 n개 레코드

SELECT ROWNUM, NAME
FROM (SELECT * 
FROM ANIMAL_INS 
ORDER BY DATETIME) 
WHERE ROWNUM < 2;

이렇게 풀다보니 MySQL이 생각보다 정말로 쉬운 쿼리였구나 느꼈다. MySQL같은 경우 Limit 함수를 걸어서 바로 상위 N개의 레코드를 얻는게 굉장히 쉬웠지만..오라클로 풀 경우에 섭 쿼리를 넣어주고 ROWNUM을 조건부에 적어야지 원하는 답을 얻을 수 있었다.

ROWNUM을 그냥 처음 적었던 섭 쿼리에 적으면 어떨까? 라는 생각에 도전을 해봤고 아래와 같았다.

오잉? JACK의 ROWNUM이 83이다. 그렇다. 첫번째 쿼리에서는 오더바이로 순서를 바꿨기 때문에 ROWNUM이 가장 위에 있는 데이터임에도 저런 다른숫자가 나오는것이기에 먼저 순서를 맞추고 ROWNUM 조건을 걸어줘야지 상위 N개의 레코드를 얻는데 성공할 수 있었다.

오프라인/온라인 판매 데이터 통합하기

SELECT * 
FROM(
SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE, 
       PRODUCT_ID, 
       USER_ID,
       SALES_AMOUNT
FROM ONLINE_SALE 
WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') LIKE '2022-03%' 

UNION ALL 

SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD') AS SALES_DATE, 
       PRODUCT_ID, 
       NULL AS USER_ID, 
       SALES_AMOUNT 
FROM OFFLINE_SALE  
WHERE TO_CHAR(SALES_DATE,'YYYY-MM') LIKE '2022-03%' 
) ORDER BY SALES_DATE, PRODUCT_ID, USER_ID