기본 SELECT문으로 데이터 조회하기

---

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC] # ASC : 오름차순, DESC : 내림차순

SELECT절

• 모든 컬럼 조회 : SELECT *

WHERE절

• 비교 연산자 : =, >, <, >=, <=, !=, BETWEEN a AND b, IS (NOT) NULL
• 논리 연산자 : AND, OR, NOT
  • 우선순위 : AND > OR
• 비트 연산자 : &
• 패턴 매칭 :
  • 방법1. LIKE : LIKE <pattern>
    • % : 0개 이상의 임의 문자
    • _ : 1개의 임의 문자
  • 방법2. 정규표현식 : REGEXP_LIKE('대상문자열', '정규표현식')
    • | : 또는
• EXISTS, NOT EXISTS : 결과가 존재하는지 확인
• IN, NOT IN : 특정 값이 결과에 포함되는지 확인

집계 함수

---

집계 함수는 여러 행을 하나의 값으로 계산하는 함수이다.

• 종류
  • COUNT : 행의 개수 계산
    • COUNT() : NULL 값인 행을 포함한다.
    • COUNT(열) : NULL 값인 행을 무시한다.
  • SUM, AVG, MAX, MIN 등
• AVG(DISTINCT 열이름)와 같이 DISTINCT를 명시하면 중복된 값은 계산에서 제외한다.

SELECT COUNT(*) as total_count,
       AVG(age) as average_age,
       MAX(salary) as max_salary
FROM employees;

--가격이 제일 비싼 식품의 정보 출력하기
SELECT PRODUCT_ID, PRODUCT_NAME, PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

JOIN

---

JOIN은 두 개 이상의 테이블을 연결하여 데이터를 조회하는 방법이다.
INNER JOIN은 두 테이블에서 조건을 만족하는 데이터만 반환한다. INNER는 생략 가능하다.

-- 직원과 부서 정보를 함께 조회
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.departments_id = d.departments_id;

LEFE JOIN은 왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에서 조건을 만족하는 데이터가 있으면 함께 반환한다. 조건을 만족하지 않는 경우 NULL 값이 반환된다.

RIGHT JOIN은 LEFT JOIN과 반대로 오른쪽 테이블의 모든 데이터를 반환한다.

FULL OUTER JOIN은 양쪽 테이블의 모든 데이터를 반환한다.

그룹화

---

GROUP BY절은 특정 컬럼을 기준으로 데이터를 그룹화할 때 사용한다.

-- 부서별 평균 급여 조회
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

HAVING 절은 GROUP BY절과 함께 사용되며, 그룹화된 결과에 조건을 적용할 때 사용한다.
WHERE절이 그룹화 전의 개별 행에 조건을 적용하는 반면, HAVING절은 그룹화 후의 결과에 조건을 적용한다.

• 특징
  • SELECT에 명시한 열은(그룹함수 제외) GROUP BY에도 존재해야한다.
  • NULL값도 그룹으로 가져와진다.

서브쿼리

---

서브쿼리는 하나의 SQL문 안에 포함된 또 다른 SQL문이다. 복잡한 조건을 처리하거나 단계별로 데이터를 처리할 때 사용한다.

scala 서브쿼리는 단일 값을 반환하며, 다음과 같이 SELECT절이나 WHERE 절에서 사용한다.

-- 평균 급여보다 높은 급여를 받는 직원 조회
SELECT name, salary
FROM employees
WHERE salay > (SELECT AVG(salary) FROM employees);

인라인 뷰는 FROM 절에 사용되는 서브쿼리로, 임시 테이블처럼 사용할 수 있다.

윈도우 함수

---

윈도우 함수는 그룹화하지 않고도 집계 연산을 수행할 수 있다. 각 행에 대해 특정 범위의 행들을 대상으로 계산을 수행한다.

행 순번 - ROW_NUMBER(), RANK(), DENSE_RANK()

ROW_NUMBER()는 각 행에 고유한 순번을 부여한다.
RANK()는 동일한 값에 대해 같은 순위를 부여하고, DENSE_RANK()는 동일한 값에 대해 연속적인 순위를 부여한다.

-- 부서별 급여 순위 계산
SELECT name, department, salary,
	   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

전체 데이터를 N개 그룹으로 나누어 1, 2, ..., N 반환 - NTILE(N)

NTILE(N) OVER (ORDER BY 컬럼 [ASC|DESC])

SELECT ID,
	CASE
    	WHEN NTILE(4) OVER (ORDER BY SIZE DESC) = 1 THEN 'CRITICAL'
        WHEN NTILE(4) OVER (ORDER BY SIZE DESC) = 2 THEN 'HIGH'
        WHEN NTILE(4) OVER (ORDER BY SIZE DESC) = 3 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS NAME
FROM DATA

백분위 순위를 0~1 사이의 값으로 반환 - PERCENT_RANK()

PERCENT_RANK() OVER (ORDER BY 컬럼 [ASC|DESC])

SELECT ID,
       CASE 
           WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.25 THEN 'CRITICAL'
           WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.50 THEN 'HIGH'
           WHEN PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) <= 0.75 THEN 'MEDIUM'
       ELSE 'LOW'
    END AS NAME
FROM DATA

조건부 처리

---

CASE문은 조건에 따라 다른 값을 반환하는 조건부 처리 구문이다.

단순 CASE

하나의 표현식을 특정 값들과 비교해서 매칭되는 결과를 반환

SELECT
    BOARD_ID, CASE STATUS
        WHEN 'SALE' THEN '판매중'
        WHEN 'RESERVED' THEN '예약중'
        WHEN 'DONE' THEN '거래완료'
    END STATUS
FROM USED_GOODS_BOARD

조건식 지정 CASE

조건식을 직접 지정해서 결과를 반환

-- 급여 등급 분류
SELECT name, salary,
  CASE
      WHEN salary >= 80000 THEN 'Senior'
      WHEN salary >= 50000 THEN 'Mid'
      ELSE 'Junior'
  END as salary_grade
FROM employees;

집합 연산자

---

연산자	의미
UNION	합집합 (중복 제거)
UNION ALL	합집합 (중복 포함, 성능 유리)
INTERSECT	교집합
MINUS	차집합 (첫 번째 – 두 번째)

⚠️ 집합 연산자 사용 시 주의사항

• 컬럼 수와 데이터 타입 일치
• ORDER BY는 집합 연산 후 전체 결과 집합에만 적용 가능

상위 n개의 데이터 조회

---

Oracle 11g 이하

ROWNUM은 ORDER BY 절이 실행되기 전에 할당되므로, 반드시 서브쿼리로 정렬 후 ROWNUM 조건을 적용해야 원하는 결과를 얻을 수 있다.

-- 급여 상위 5명 조회
SELECT * FROM (
    SELECT employee_id, name, salary
    FROM employees
    ORDER BY salary DESC  -- 먼저 정렬
) WHERE ROWNUM <= 5;

Oracle 12c 이상

-- 급여 상위 5명 조회
SELECT employee_id, name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

문자열 함수

---

• 문자열 연결 : str1 || str2 || ...
• 부분 문자열 추출 : SUBSTR(대상문자열, start_position[, length])
  • start_position는 1부터 지정
• 부분 문자열 위치 : INSTR(대상문자열, 찾을 문자열[, start_position [, occurrence]])
  • 위치는 1부터 시작함
  • start_position는 1부터 지정
• LENGTH() : 문자열 길이 반환
• UPPER(), LOWER() : 대소문자 변환
• REPLACE(str, 대상문자여, 교체문자열) : 문자열 치환
• TRIM(), LTRIM(), RTRIM() : 앞 뒤 공백 제거

날짜 함수

---

• SYSDATE : 현재 날짜
• 날짜 비교 : DATE1 [>|<|>=] DATE2

변환

• TO_CHAR(datetime, 'YYYY-MM-DD') : 날짜를 날짜 형식의 문자로 변환
  • 날짜 형식은 대소문자를 구분하지 않는다.
  • 날짜 형식에 Q를 입력하면, 분기(1, 2, 3, 4)를 반환한다.
• TO_DATE('20190701', 'YYYYMMDD') : 날짜 형식의 문자를 날짜 타입으로 변환
  • 날짜 형식은 대소문자를 구분하지 않는다.
• EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM timestamp), EXTRACT(YEAR/MONTH/DAY FROM date) : 날짜의 일부분 추출
  • datetime은 ANSI DATE로 인식되어 시분초를 추출할 땐 timestamp로 변환해야한다.

계산

• 날짜를 일 단위로 더하거나 뺀다. : DATE [+|-] n
• 날짜를 월 단위로 더하거나 뺀다. : ADD_MONTH(date, n)
• 개월 수 차이 : MONTHS_BETWEEN(기준일자, 비교일자)
• 명시한 기준까지만 데이터를 남기고, 아래는 절사 : TRUNC(date, YEAR/MONTH/DAY/HH24)
  • e.g. TRUNC('2024-01-02 21:35:24', 'HH24') -- 2024-01-02 21:00:00

수치 계산 함수

---

• ABS : 절대값
• ROUND(실수, 소수점 자리수) : 반올림
  • 소수점 자릿수(default 0)
    • 양수면 소수점 이하 자리수
    • 0이면 정수로
    • 음수면 소수점 왼쪽에서 반올림
  • 예시

    SELECT
      ROUND(123.456, 2)   AS ex1,   -- 123.46
      ROUND(123.456, 0)   AS ex2,   -- 123
      ROUND(123.456)      AS ex3,   -- 123 (m 생략 시 0)
      ROUND(123.456, -1)  AS ex4,   -- 120  (십의 자리에서 반올림)
      ROUND(149,     -2)  AS ex5,   -- 100  (백의 자리)
      ROUND(150,     -2)  AS ex6,   -- 200  (백의 자리, 50↑면 올림)
      ROUND(-1.55,    1)  AS ex7;   -- -1.6

• TRUNC(수, 자리수) : 자리수 아래부터 절사
  • TRUNC(123.456, 2) = 123.45
  • TRUNC(123.456, -1) = 120 (일의 자리 절사)

기타

---

• 가상 테이블 - DUAL
  단순한 계산이나 함수 테스트를 위해 DUAL이라는 가상 테이블을 사용한다.
  예를 들어 현재 날짜를 조회하려면 SELECT SYSDATE FROM DUAL과 같이 작성한다.

• NULL 함수 - NVL(column, 대체할값)
  값이 NULL일 때, 대체할 값을 명시한다.

• 문자를 숫자로 변환 : TO_NUMBER('문자')

• 숫자 출력 포맷 - TO_CHAR(number, 'format')

  • format
    • 9 : 숫자가 있으면 표시, 없으면 공백(소수부에서는 공백 대신 0)
    • 0 : 숫자가 없으면 0으로 강제 출력
    • FM : Fill Mode, 앞뒤 공백 제거

• 복잡한 서브쿼리를 미리 정의해두고, 이후 메인 쿼리에서 재사용 - WITH절

  WITH <subquery_name1> AS (
      SELECT ...
      FROM ...
      WHERE ...
  ),
  <subquery_name2> AS (
      SELECT ...
      FROM ...
      WHERE ...
  ), 
  ...
  SELECT ... FROM <subquery_name>