SQL 코테 대비 (Oracle)

1️⃣ 코드

SELECT문

• 테이블에 입력된 데이터를 조회하기 위해 사용

• 실행 순서는 from, where, group by, having, select, order by

• 와일드카드: %(0개 이상의 문자), _(단일 문자을 의미)

• 조건문: case문, decode 함수 사용

• 문제 풀고 오름차순/내림차순 정렬 제대로 했는지 꼭 확인하기!
  

1. select * from EMP where 사원번호 = 1000
   where rownum > 3  -- 3개만 출력 
   order by ENAME, SAL desc -- ENAME으로 오름차순 정렬, SAL로 내림차순 정렬

2. 
-- 조건 여러 개 사용 
SELECT ORDER_ID, PRODUCT_ID, TO_CHAR(OUT_DATE, 'YYYY-MM-DD'), 
    CASE 
        WHEN OUT_DATE IS NULL THEN '출고미정' 
        WHEN TO_CHAR(OUT_DATE, 'YYYY-MM-DD') <= '2022-05-01' THEN '출고완료' 
        ELSE '출고대기' 
        END AS "출고여부"  -- 컬럼명은 큰 따옴표 써야함
FROM FOOD_ORDER
ORDER BY ORDER_ID

3. decode(STATUS, 'SALE', '판매중', 'RESERVED', '예약중', 'DONE', '거래완료') as STATUS

4. select PRODUCT_ID, null as USER_ID -- 값 대신 null 출력

DATETIME

• TO_CHAR은 날짜 포맷을 지정하는데 쓰임 -> YYYY(년), MM(월), DD(일), HH24(24시간), HH(12시간)

• TO_DATE는 문자열을 날짜로 변환하는데 쓰임

• 날짜끼리 뺄셈 연산 가능함

• EXTRACT(YEAR FROM 열 이름): DATE에서 년도만 추출 (or MONTH, DAY)

• EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) -> MINUTE(분), SECOND(초)

1. select to_char(DATETIME, 'YYYY-MM-DD') from EMP

2. CASE WHEN TO_DATE('2022-05-01', 'YYYY-MM-DD') >= OUT_DATE THEN '출고완료'

3. (END_DATE - START_DATE + 1) >= 30  -- 대여 기간 30일 이상 (1 더해줌 주의!)

4. WHERE EXTRACT(YEAR FROM A.CREATED_DATE) = 2022  -- FROM을 쉼표로 쓰지 좀 말기

5. TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') LIKE '2022-10-05'  -- 날짜 일치하는지 확인

집계 함수

• COUNT(): count(*)은 널 값을 포함한 모든 행 수를 계산, count(컬럼명)은 널 값을 제외한 행 수를 계산

• SUM(): 합계를 계산

• AVG(): 평균을 계산

• ROUND(값, 자릿수): 반올림

• MAX()와 MIN(): 최댓값과 최솟값을 계산

• RANK(): 순위 구함, 중복 순위만큼 다음 순위 값을 증가시킴

• NVL(): 널이면 다른 값으로 바꾸는 함수 -> NVL(MGR, 0)

1. select count(*) from EMP
   select count(MGR) from EMP

2. count(distinct(NAME))

3. rank() orver (order by SAL desc)  -- SAL가 가장 높은 게 1

4. AGE BETWEEN 20 AND 29  -> 나이 20세 이상, 29세 이하

문자열

• LENGTH(문자열): 문자열 길이 구하기
• LENGTHB(문자열): 문자열 바이트 수 구하기

• ||: 문자열 합치기

• SUBSTR(문자열, 시작위치, 길이): 문자열 자르기

1. where OPTIONS like '%네비게이션%'  -- 네비게이션 옵션 포함한 것

2. substr(TLNO, 1, 3) || '-' || substr(TLNO, 4, 4) || '-' || substr(TLNO, 8, 4) "전화번호"

-- 문자열 포함 여부 확인 
3. where lower(NAME) in ('lucy', 'ella', 'pickle', 'rogan', 'sabrina', 'mitty')

4. select * from EMP where lower(ENAME) like 'test%'  -- like를 =로 쓰는 실수 주의

-- 30.0처럼 소수점 첫째자리까지 무조건 출력
5. TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9999990.0')

GROUP 연산

• group by와 select의 내용이 같아야 함
• having은 각 그룹에 대한 조건

-- 부서번호로 그룹을 만들고 그룹별 합계를 계산함
select DEPTNO, SUM(SAL) from EMP group by DEPNO
  having sum(SAL) > 10000  -- group by 결과에서 합계가 10000 이상만 조회함

-- 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회
select HOUR, count(HOUR) COUNT
  from (select to_number(to_char(DATETIME, 'HH24')) HOUR from ANIMAL_OUTS)
  where HOUR between 9 and 19 group by HOUR order by HOUR

JOIN

• 조인의 가장 기본은 교집합을 만드는 것

• 💡 조인 코드의 평균적인 형태

-- 아직 입양을 못간 동물 중, 가장 오래 보호소에 있었던 동물 3마리
select NAME, DATETIME	
from (select A.NAME, A.DATETIME, rank() over (order by A.DATETIME) RNK
	  from ANIMAL_INS A left join ANIMAL_OUTERS B 
	  on A.ANIMAL_ID = B.ANIMAL_ID
      where B.ANIMAL_ID is null) -- 차집합 A-B
where RNK <= 3  -- 상위 3마리를 구하려면 where이 한 번 더 필요함!

집합

2️⃣ 문제 풀이

자동차 대여 기록 별 대여 금액 구하기 (Level 4)

• 3개의 테이블 조인하기
• '7일 이상'에서 7을 추출하는 게 아니라 CASE문으로 7일 이상이면 '7일 이상'으로 행을 만듦

SELECT B.HISTORY_ID AS HISTORY_ID, (DAILY_FEE * DAY) * (100 - NVL(DISCOUNT_RATE, 0)) / 100 AS FEE
FROM (
    SELECT HISTORY_ID, C.CAR_ID, CAR_TYPE, DAILY_FEE, (END_DATE - START_DATE + 1) AS DAY,
    CASE
        WHEN (END_DATE - START_DATE + 1) >= 7 AND (END_DATE - START_DATE + 1) < 30 THEN '7일 이상'
        WHEN (END_DATE - START_DATE + 1) >= 30 AND (END_DATE - START_DATE + 1) < 90 THEN '30일 이상'
        WHEN (END_DATE - START_DATE + 1) >= 90 THEN '90일 이상'
    END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H INNER JOIN CAR_RENTAL_COMPANY_CAR C
    ON H.CAR_ID = C.CAR_ID
    WHERE CAR_TYPE = '트럭'
    ORDER BY HISTORY_ID
) B LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON B.CAR_TYPE = P.CAR_TYPE AND B.DURATION_TYPE = P.DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC;

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

음식 종류별로 즐겨찾기 수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기 수 조회

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
                                FROM REST_INFO
                                GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

WHERE CATEGORY IN ('과자', '국', '김치', '식용유')

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

2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력
-> 자동차별로 가장 최근 대여 기록만 신경 쓰면 됨

SELECT CAR_ID, MAX(
    CASE WHEN '2022-10-16' 
    BETWEEN TO_CHAR(START_DATE,'YYYY-MM-DD') AND TO_CHAR(END_DATE,'YYYY-MM-DD') THEN '대여중'
    ELSE '대여 가능' END) AS AVAILABILITY 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

CASE WHEN deptno = '10' THEN 'New York'
WHEN deptno = '20' THEN 'Dallas'
ELSE 'Unknown'
END

저자 별 카테고리 별 매출액 집계하기

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(판매량*판매가)를 구하는 문제
도서 정보 테이블(BOOK), 저자 정보 테이블(AUTHOR), 판매 정보 테이블(BOOK_SALES)
​

-> 3개 이상 테이블 조인

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

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

1. 출력은 대여 시작일, 자동차ID, 월별 대여 횟수

SELECT EXTRACT(MONTH FROM START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID

# GROUP BY에 CAR_ID 안넣으면 'not a GROUP BY expression' 오류

​

2. 8월과 10월 사이
   대여가 한 번도 되지 않은 월은 제거
   월 오름차순, 자동차ID 내림차순

SELECT EXTRACT(MONTH FROM START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
➕WHERE 8 <= EXTRACT(MONTH FROM START_DATE) AND EXTRACT(MONTH FROM START_DATE) <= 10
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
➕HAVING COUNT(*) != 0
➕ORDER BY MONTH, CAR_ID DESC

​

3. 8월에서 10월 사이의 총 대여 횟수가 5대 이상인 자동차들(CAR_ID)
   -> 이미 WHERE, GROUP BY를 사용했기 때문에 서브쿼리 사용

SELECT EXTRACT(MONTH FROM START_DATE) MONTH, CAR_ID, COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
➕WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE EXTRACT(MONTH FROM START_DATE) IN (8, 9, 10) 
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5
) AND 8 <= EXTRACT(MONTH FROM START_DATE) AND EXTRACT(MONTH FROM START_DATE) <= 10
GROUP BY EXTRACT(MONTH FROM START_DATE), CAR_ID
HAVING COUNT(*) != 0
ORDER BY MONTH, CAR_ID DESC

날짜에서 월 추출 -> EXTRACT(MONTH FROM START_DATE)