Oracle SQL 테이블 설계부터 집계함수까지

이번 글은 Oracle SQL을 공부하면서 정리한 학습 기록이다.
순서대로 테이블 생성 → 데이터 입력 → 시퀀스/트리거 → 인덱스 → 조회/조인/서브쿼리 → 집계/분석 함수 순이다

---

1. 테이블 생성 (DDL)

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER,
    dept_id NUMBER
);

CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

• CREATE TABLE을 사용해 employees, departments 테이블을 만든다.
• Oracle에서는 정수형에 NUMBER, 문자열에는 VARCHAR2를 주로 사용한다.
• 각 테이블마다 기본 키(PK)를 지정해야 한다.

---

2. 데이터 입력 (DML)

INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (1, 'Alice', 3000, 10);

INSERT INTO departments (dept_id, dept_name)
VALUES (10, 'HR');

• INSERT INTO ... VALUES 문으로 데이터를 삽입한다.
• Oracle과 MySQL에서 문법은 거의 동일하다.
• 다만 Oracle에서는 보통 PK를 직접 넣기보다 시퀀스로 자동 증가시키는 방식을 쓴다.

---

3. 시퀀스 생성

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

• 시퀀스는 Oracle에서 제공하는 자동 증가 번호 생성기다.
• START WITH는 시작값, INCREMENT BY는 증가 단위를 지정한다.
• MySQL의 AUTO_INCREMENT와 비슷한 역할을 한다.

---

4. 트리거로 시퀀스 자동 적용

CREATE OR REPLACE TRIGGER trg_emp_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.emp_id := emp_seq.NEXTVAL;
END;

• 트리거는 특정 이벤트가 발생할 때 자동으로 실행되는 코드다.
• 여기서는 BEFORE INSERT를 사용해 INSERT 전에 실행되도록 한다.
• 새 레코드가 들어올 때마다 emp_seq.NEXTVAL을 가져와 emp_id에 할당한다.

---

5. 인덱스 생성

CREATE INDEX idx_salary ON employees(salary);

• salary 컬럼에 인덱스를 생성한다.
• 인덱스는 SELECT 성능을 높여주지만, INSERT/UPDATE 시에는 부하가 생길 수 있다.
• 자주 조회 조건으로 쓰이는 컬럼에만 인덱스를 걸어야 한다.

---

6. 조회와 조건문

SELECT * 
FROM employees
WHERE salary > 2000
ORDER BY salary DESC;

• SELECT로 전체 조회 후 WHERE 조건을 붙여 원하는 행만 필터링한다.
• ORDER BY로 정렬할 수 있다.

---

7. 조인 (JOIN)

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

• INNER JOIN은 양쪽 테이블에 모두 존재하는 데이터만 조회한다.
• employees의 dept_id와 departments의 dept_id를 연결한다.

---

8. 서브쿼리

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

• 서브쿼리를 사용해 조건 안에서 다른 조회 결과를 비교할 수 있다.
• 이 예시는 평균 급여보다 높은 직원만 조회한다.

---

9. 집계 함수

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 2500;

• GROUP BY로 부서별로 묶어서 평균 급여를 계산한다.
• HAVING 절로 집계 조건을 걸 수 있다.

---

10. 분석 함수

SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

• RANK()는 정렬 기준에 따라 순위를 매겨준다.
• OVER (ORDER BY ...) 구문을 반드시 지정해야 한다.

---

11. 실행 계획 확인

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 2000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• EXPLAIN PLAN을 사용하면 SQL 실행 계획을 확인할 수 있다.
• TABLE ACCESS FULL은 전체 테이블을 스캔하는 방식이고,
  INDEX RANGE SCAN이면 인덱스를 타고 조회하는 방식이다.
• 이 결과를 보고 인덱스를 추가하거나 쿼리를 수정할 수 있다.

---

12. DDL / DML / DCL / TCL 정리

• DDL (정의어) : CREATE, ALTER, DROP → 구조 정의
• DML (조작어) : INSERT, UPDATE, DELETE, SELECT → 데이터 조작
• DCL (제어어) : GRANT, REVOKE → 권한 부여/회수
• TCL (트랜잭션) : COMMIT, ROLLBACK → 트랜잭션 제어

---

13. 테이블 네이밍 규칙 정리

• 문법 규칙 : 1~30자, 문자로 시작, 예약어 금지
• 실무 권장 : 소문자 + 스네이크 케이스, 복수형, 명확한 이름 사용
• 예시
  • x tbl_usr_accnt → o user_account
  • x 123report → o  report_log

---

 

• 실습은 테이블 → 데이터 → 시퀀스/트리거 → 인덱스 → 조회/조인/서브쿼리 → 집계/분석 함수 순으로 진행했다.
• Oracle과 MySQL의 문법은 비슷하지만, Oracle은 VARCHAR2, NUMBER, 시퀀스 같은 특징이 있다.
• 인덱스와 실행 계획을 통해 성능을 확인하는 것이 중요하다.
• 테이블 이름은 가독성 + 유지보수성을 고려해 규칙적으로 짓는 게 필수다.