[프로그래머스] Oracle SQL (Lv1, 24문제)

<< 오라클 문법 기록장 >>

🔸7. 경기도에 위치한 식품창고 목록 출력하기

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

SELECT
    WAREHOUSE_ID,
    WAREHOUSE_NAME,
    ADDRESS,
    NVL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '%경기도%'
ORDER BY WAREHOUSE_ID;

DBMS	NULL 처리 함수	사용 예시
MySQL	IFNULL(expr, val)	IFNULL(FREEZER_YN, 'N')
Oracle	NVL(expr, val)	NVL(FREEZER_YN, 'N')
ANSI SQL 표준	COALESCE(expr, val)	COALESCE(FREEZER_YN, 'N')

• COALESCE는 MySQL, Oracle, PostgreSQL 등 대부분의 DB에서 공통적으로 지원되는 함수

🔸8. 조건에 맞는 회원수 구하기

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

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE EXTRACT(YEAR FROM JOINED) = 2021 AND AGE BETWEEN 20 AND 29

• 오라클에서  DATE타입의 컬럼에서 연도를 추출하는 방법
  1. EXTRACT 함수 : 숫자로 연도 추출
  2. TO_CHART 함수 : 문자열로 연도 추출

1. EXTRACT 함수 : 계산,비교,정렬에 유리

SELECT 
    EXTRACT(YEAR FROM CREATED_AT) AS YEAR
FROM FOOD_WAREHOUSE;

 

2. TO_CHAR 함수 : 문자열 처리 및 출력 포멧을 자유롭게 설정할 수 있다

주의! )

• TO_CHAR 함수에서 날짜 형식 지정자는 %기호를 사용하지 않는다
• YYYY,MM,DD와 같이 따옴표 안에 직접 문자를 사용해야한다

SELECT 
    TO_CHAR(CREATED_AT, 'YYYY') AS YEAR
FROM FOOD_WAREHOUSE;

---

🔸9. 흉부외과 또는 일반외과 의사 목록 출력하기

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

SELECT
    DR_NAME,
    DR_ID,
    MCDP_CD,
    TO_CHAR(HIRE_YMD, 'YYYY-MM-DD') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

• 여러개의 조건을 OR로 연결해야할 때는 IN 연산자를 사용하는 것이 코드가 간결해진다

---

🔸13. 상위 n개 레코드

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

SELECT NAME
FROM ANIMAL_INS 
ORDER BY DATETIME
FETCH FIRST 1 ROW ONLY;

상위 N개인 데이터 가져오기

• FETCH FIRST N ROW ONLY
  • ORDER BY와 함께 사용: 정렬된 결과의 가장 첫번째 행을 가져올때(MySQL의 LIMIT과 가장 유사)
  • 표준SQL, 오라클 12c 버전 부터 도입된 문법
• ROWNUM
  • Oracle 전용 문법
  • 결과가 정렬되기 전에 ROWNUM이 부여되므로 ORDER BY와 함께 사용할 때 주의
    즉, N개를 먼저 뽑고 그 N개만 정렬하기 때문에  전체 정렬 후 상위 N개가 아니다!!!
    그래서 서브쿼리를 활용해야한다.

SELECT NAME
FROM 
    (SELECT NAME
    FROM ANIMAL_INS 
    ORDER BY DATETIME)
WHERE ROWNUM = 1;

---

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

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

SELECT FH.FLAVOR
FROM FIRST_HALF FH
    JOIN ICECREAM_INFO II 
    ON FH.FLAVOR = II.FLAVOR
WHERE FH.total_order > 3000 
    AND II.INGREDIENT_TYPE = 'fruit_based'
ORDER BY FH.total_order DESC;

• 오라클은 FROM절에서 테이블에 별칭(alias)을 붙일 때는 AS키워드를 사용하지 않는다
  테이블 이름  뒤에 공백을 두고 별칭을 바로 쓴다
• SELECT절에서 AS 사용은 선택 사항이나 사용하는 것을 권장
•  컬럼별칭에 공백,한글,특수문자가 들어갈 경우 큰따옴표(")를 사용
  • 백틱(`): MySQL에서 사용.
  • 큰따옴표("): 오라클에서 사용.

---

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

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;

1. 큰따옴표(") vs 작은따옴표(')

• 큰따옴표(") : 별칭(alias)을 지정할 때 사용
• 작은따옴표('): 문자열 리터럴(string literal)을 나타낼때 사용
  • '장기대여'와 같이 데이터 자체의 값을 취급되는 모든 문자열은 작은 따옴표로묶어줘야한다
  • 즉, 큰따옴표는 이름을 정하는 용도이고, 작은 따옴표는 값을 표현하는 용도  

2. end_date - start_date +1 을 하는 이유

end_date - start_date는 오라클에서 빌린 날을 제외하는 기간을 계산한다

하지만 보통 대여 기간을 계산할 때는 시작일과 종료일을 모두 포함하기 때문에  +1을 해줘야 정확한 대여 기간을 구할 수 있다.

 

3. 데이터 베이스별 날짜 차이 구하기

데이터베이스	함수/문법	설명	예시
MySQL	DATEDIFF(end_date, start_date)	두 날짜의 차이를 일(day) 단위로 반환합니다.	DATEDIFF('2024-03-15', '2024-03-01') → 14
Oracle	end_date - start_date	두 날짜를 직접 빼면 일(day) 단위로 소수점까지 결과가 나옵니다.	TO_DATE('2024-03-15') - TO_DATE('2024-03-01') → 14
Oracle (TRUNC 사용)	TRUNC(end_date) - TRUNC(start_date)	TRUNC를 사용하면 시간 부분을 제외하고 날짜만 계산할 수 있습니다.	TRUNC(TO_DATE('2024-03-15')) - TRUNC(TO_DATE('2024-03-01')) → 14
SQL Server	DATEDIFF(datepart, start_date, end_date)	첫 번째 인자로 **단위(year, month, day 등)**를 지정합니다.	DATEDIFF(day, '2024-03-01', '2024-03-15') → 14

---

🔸나머지 문제 풀이

1. 강원도에 위치한 생산공장 목록 출력하기

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

SELECT 
    FACTORY_ID,
    FACTORY_NAME,
    ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '%강원도%'
ORDER BY FACTORY_ID;

 

2. 가장 비싼 상품 구하기

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

SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT;

 

3. 나이 정보가 없는 회원 수 구하기

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

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL;

4. 여러기준으로 정렬하기

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

SELECT 
    ANIMAL_ID,
    NAME,
    DATETIME
FROM ANIMAL_INS 
ORDER BY NAME, DATETIME DESC;

 

5. 아픈 동물 찾기

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

SELECT
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID;

 

6. 이름이 없는 동물의 아이디

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

SELECT ANIMAL_ID
FROM ANIMAL_INS 
WHERE NAME IS NULL
ORDER BY ANIMAL_ID;

10. 역순 정렬하기

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

SELECT
    NAME,
    DATETIME
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID DESC;

 

11. 어린 동물 찾기

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

SELECT 
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID;

 

12. 이름이 있는 동물의 아이디

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

SELECT ANIMAL_ID
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

 

14. 인기있는 아이스크림

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

SELECT FLAVOR
FROM FIRST_HALF 
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC

 

15. 동물의 아이디와 이름

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

SELECT
    ANIMAL_ID,
    NAME
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

 

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

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

SELECT 
    PT_NAME,
    PT_NO,
    GEND_CD,
    AGE,
    COALESCE(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC;

 

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

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

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

 

18. 평균 일일 대여 요금 구하기

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

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

20. 특정 옵션이 포함된 자동차 리스트 구하기

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

SELECT *
FROM CAR_RENTAL_COMPANY_CAR 
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

 

21. 모든 레코드 조회하기

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

SELECT *
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID;

22. 최댓값 구하기

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

SELECT DATETIME "시간"
FROM ANIMAL_INS
ORDER BY DATETIME DESC
FETCH FIRST 1 ROW ONLY;

 

 

24. 조건에 부합하는 중고거래 댓글 조회하기

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

SELECT 
    BOARD.TITLE,
    BOARD.BOARD_ID,
    REPLY.REPLY_ID,
    REPLY.WRITER_ID,
    REPLY.CONTENTS,
    TO_CHAR(REPLY.CREATED_DATE, 'YYYY-MM-DD') AS CREATED_DATE
FROM USED_GOODS_BOARD BOARD
    JOIN USED_GOODS_REPLY REPLY ON BOARD.BOARD_ID = REPLY.BOARD_ID
WHERE TO_CHAR(BOARD.CREATED_DATE, 'YYYY-MM') = '2022-10'
ORDER BY REPLY.CREATED_DATE, BOARD.TITLE;