[Oracle SQL] 알짜배기 쿼리문 복습하기 🛸

3년 만에 다시 공부하는 데이터베이스 ... ^__^

나흘 후 코테이니 프로그래머스의 SQL 고득점kit과 함께 빠르게 DQL만 복습해 봅니닷

프로그래머스

---

쿼리 순서  FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY

예제 테이블 EMPLOYEE

	NAME	AGE	JOB
1	ZENA	25	DEVELOPER
2	RYU	27	DEVELOPER
3	MISO	29	SALESMAN
4	BUDDLE	27	SALESMAN
5	MOON	25	DEVELOPER

---

SELECT * FROM EMPLOYEE
EMPLOYEE 라는 테이블의 모든 컬럼을 조회

	NAME	AGE	JOB
1	ZENA	25	DEVELOPER
2	RYU	27	DEVELOPER
3	MISO	29	SALESMAN
4	BUDDLE	27	SALESMAN
5	MOON	25	DEVELOPER

---

SELECT NAME, AGE FROM EMPLOYEE
EMPLOYEE라는 테이블의 컬럼 중 NAME, AGE에 해당하는 컬럼만 조회

	NAME	AGE
1	ZENA	25
2	RYU	27
3	MISO	29
4	BUDDLE	27
5	MOON	25

---

SELECT DISTINCT JOB FROM EMPLOYEE
중복을 제외한 JOB을 조회

	JOB
1	DEVELOPER
2	SALESMAN

---

SELECT NAME, AGE
FROM EMPLOYEE
ORDER BY AGE

	NAME	AGE
1	ZENA	25
2	MOON	25
3	RYU	27
4	BUDDLE	27
5	MISO	29

ASC (오름차순), DESC (내림차순)
ORDER BY는 디폴트로 오름차순으로 정렬되기 때문에 생략 가능

정렬 조건이 여러 개인 경우
예. 나이를 내림차순으로 정렬하고, 나이가 같으면 이름의 오름차순 정렬
SELECT NAME, AGE
FROM EMPLOYEE
ORDER BY AGE DESC, NAME

	NAME	AGE
1	MISO	29
2	BUDDLE	27
3	RYU	27
4	MOON	25
5	ZENA	25

---

데이터 중 NULL 값을 처리하는 경우

	NAME	AGE	JOB
1	ZENA	25	DEVELOPER
2	RYU	27	DEVELOPER
3	MISO	29	SALESMAN
4	BUDDLE	27	NULL
5	MOON	25	DEVELOPER

SELECT NAME, NVL(JOB, 'STUDENT') FROM EMPLOYEE --null이면 'STUDENT'로 대체

	NAME	JOB
1	ZENA	DEVELOPER
2	RYU	DEVELOPER
3	MISO	SALESMAN
4	BUDDLE	STUDENT
5	MOON	DEVELOPER

SELECT NAME FROM EMPLOYEE
WHERE JOB IS NULL -- 직무가 null인 직원의 이름 조회

	NAME
1	BUDDLE

---

SELECT MAX(AGE) FROM EMPLOYEE -- 컬럼의 최댓값을 출력

	MAX(AGE)
1	29

---

SELECT NAME FROM EMPLOYEE
WHERE NAME LIKE '%O%' -- 특정 문자열 포함 여부

	NAME
1	MISO
2	MOON

---

SELECT NAME FROM EMPLOYEE
WHERE NOT JOB = 'DEVELOPER' -- 부정 조건문, 뒤에 나오는 조건문이 거짓

	NAME
1	MISO
2	BUDDLE

---

SELECT NAME FROM EMPLOYEE
WHERE AGE = (SELECT MAX(AGE) FROM EMPLOYEE) -- 나이가 가장 많은 직원의 이름을 출력

	NAME
1	MISO

---

SELECT NAME, AGE,
               CASE WHEN AGE > 25 THEN 'SENIOR'
               ELSE 'JUNIOR'
               END TYPE

if문과 같은 기능, DECODE를 사용할 수도 있지만 조건문이 복잡할 수록 CASE WEHN 사용을 권유한다.

	NAME	AGE	TYPE
1	ZENA	25	JUNIOR
2	RYU	27	SENIOR
3	MISO	29	SENIOR
4	BUDDLE	27	SENIOR
5	MOON	25	JUNIOR

---

SELECT NAME FROM EMPLOYEE WHERE AGE > 26
UNION
SELECT NAME FROM EMPLOYEE WHERE JOB = 'SALESMAN'

	NAME
1	RYU
2	MISO
3	BUDDLE

두 쿼리 결과를 합쳐서 조회하는데, 중복된 결과는 하나만 조회

---

SELECT NAME FROM EMPLOYEE WHERE AGE > 26
UNION ALL
SELECT NAME FROM EMPLOYEE WHERE JOB = 'SALESMAN'

	NAME
1	RYU
2	MISO
3	BUDDLE
4	MISO
5	BUDDLE

두 쿼리 결과를 합쳐서 조회하는데, 중복된 결과는 중복 조회

---

SELECT NAME, AGE, RANK() OVER (ORDER BY AGE DESC) FROM EMPLOYEE  순위를 구하는 함수

	NAME	AGE	RANK()
1	ZENA	25	4
2	RYU	27	2
3	MISO	29	1
4	BUDDLE	27	2
5	MOON	25	4

---

SELECT NAME, AGE, DENSE_RANK() OVER (ORDER BY AGE DESC) FROM EMPLOYEE 중복 순위를 무시하는 순위 함수

	NAME	AGE	RANK()
1	ZENA	25	3
2	RYU	27	2
3	MISO	29	1
4	BUDDLE	27	2
5	MOON	25	3

---

숫자, 문자, 분석, 그룹 함수

반올림(숫자, 자릿수)
SELECT ROUND(16.375, 1) FROM DUAL -- 16.4 
SELECT ROUND(16.375, -1) FROM DUAL -- 20

---

TRUNC 소수 또는 날짜 절사
대충 수도 코드 느낌으로 작성하자면
TRUNC('2024-04-26 17:26:22') -- 2024-04-26 00:00:00
TRUNC(123.45) -- 123

---

GROUP BY 특정 컬럼을 기준으로 그룹화
HAVING 그룹화된 컬럼에 조건 부여

프로그래머스

SELECT USER_ID, PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(PRODUCT_ID) > 1
ORDER BY USER_ID, PRODUCT_ID DESC

한 유저가 동일 상품을 재주문(2개 이상 구매)한 경우의 수를 조회하는데, 유저 아이디 오름차순과 상품아이디의 내림차순으로 출력

---

UPPER 대문자 변환
LOWER 소문자 변환
INITCAP 카멜케이스

---

SUBSTR 문자열 자르기
SUBSTR(ZENA, 2) -- ENA, 문자열 끝까지
SUBSTR(ZENA, 2, 1) -- E, 앞에서 2번째 인덱스~현 위치
SUBSTR(ZENA, 2, 2) -- EN, 앞에서 두번째 인덱스~현 위치에서 2번째 인덱스까지
SUBSTR(ZENA, -3, 2) -- EN, 뒤에서 3번째 인덱스~현 위치에서 2번째 인덱스까지

---

CONCAT 또는 || 문자열 합치기
CONCAT(CONCAT('JOB', ' / '), 'DEVELOPER') -- JOB / DEVELOPER
'JOB' || ' / ' || 'DEVELOPER') -- JOB / DEVELOPER

---

날짜 함수

TO_CHAR char 형태로 변경

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL -- 2024-04-25
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS) -- 2024/04/25 13:58:49

---

EXTRACT 날짜 정보 추출

SELECT TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
WHERE EXTRACT(PUBLISHED_DATE, YEAR) = '2024'
FROM BOOK
-- PUBLISHED_DATE(출판일)이 2024년에 해당되는 것만 출력
-- YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 추출 가능

SELECT EXTRACT(YEAR FROM DATE 타입의 컬럼) FROM 테이블
DATE에서 연도만 추출

프로그래머스

관련 문제

SELECT COUNT(*)
FROM USER_INFO
WHERE EXTRACT(YEAR FROM JOINED) = 2021 AND AGE >= 20 AND AGE <= 29

위 쿼리에 BETWEEN을 사용해 범위 조건을 조회할 수도 있다.

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

---

JOIN 함수

둘 이상의 테이블을 묶어 하나의 결과로 나타낼 수 있는 함수
최소한 하나 이상의 공통 컬럼을 가져야 함

INNER JOIN (ON) 조건이 일치하는 것만 조회 

프로그래머스

INNER JOIN을 사용해 해결한 예시 문제

-- USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된
-- 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 
-- 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 
-- 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

SELECT BOARD.TITLE, 
       BOARD.BOARD_ID, 
       REPLY.REPLY_ID, 
       REPLY.WRITER_ID, 
       REPLY.CONTENTS, 
       TO_CHAR(REPLY.CREATED_DATE, 'YYYY-MM-DD')
FROM USED_GOODS_BOARD BOARD INNER 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

INNER JOIN은 명시하지 않고 콤마(,)로 표시할 수 있다고 생각했는데 프로그래머스에서는 오류가 발생한다

---

OUTER JOIN (ON) 조인 조건을 만족하지 않는 행도 모두 조회, 이들은 NULL로 조회됨

프로그래머스

왼쪽 테이블을 기준으로 맵핑하는 LEFT OUTER JOIN을 이용하여 해결한 문제

SELECT A.HISTORY_ID, (DAILY_FEE * RENT_DAY) * (100 - NVL(DISCOUNT_RATE, 0)) / 100 AS FEE
FROM (
    SELECT HISTORY_ID, CAR.CAR_ID, CAR_TYPE, DAILY_FEE, 
      (END_DATE - START_DATE + 1) AS RENT_DAY,
      CASE WHEN END_DATE - START_DATE + 1 BETWEEN 7 AND 29 THEN '7일 이상'
           WHEN END_DATE - START_DATE + 1 BETWEEN 30 AND 89 THEN '30일 이상'
           WHEN (END_DATE - START_DATE + 1) >= 90 THEN '90일 이상'
      END AS DURATION_TYPE
      FROM CAR_RENTAL_COMPANY_CAR CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HISTORY
      ON CAR.CAR_ID = HISTORY.CAR_ID
      WHERE CAR.CAR_TYPE = '트럭'
) A
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE = B.CAR_TYPE AND A.DURATION_TYPE = B.DURATION_TYPE
WHERE A.CAR_TYPE = '트럭'
ORDER BY FEE DESC, A.HISTORY_ID DESC

---

이정도면 웬만한 sql 문항은 다 풀 수 있듯
일주일동한 복기 + 프로그래머스 SQL 고득점 킷 풀며 작성한 오늘의 포스트 끗-