본문 바로가기
DB

Oracle SQL 예제

성공할 때까지 아아를 주세요 2025. 5. 30. 14:45

 Oracle SQL 예제

1. 기본 조회

  1. 전체 조회
SELECT * FROM employees;
  1. 컬럼 지정
SELECT employee_id, first_name FROM employees;
  1. WHERE 조건
SELECT * FROM employees WHERE department_id = 10;
  1. 정렬
SELECT * FROM employees ORDER BY hire_date DESC;
  1. DISTINCT
SELECT DISTINCT job_id FROM employees;

2. 조건 / 비교

  1. BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 3000 AND 7000;
  1. IN
SELECT * FROM employees WHERE job_id IN ('SA_REP', 'IT_PROG');
  1. LIKE
SELECT * FROM employees WHERE first_name LIKE 'A%';
  1. IS NULL
SELECT * FROM employees WHERE manager_id IS NULL;
  1. IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
  1. 다중 조건
SELECT * FROM employees 
WHERE job_id = 'IT_PROG' AND salary > 5000;
  1. IN 서브쿼리 다단계
SELECT first_name FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments
    WHERE location_id IN (
        SELECT location_id FROM locations
        WHERE country_id = 'US'
    )
);

3. 집계 함수

  1. COUNT
SELECT COUNT(*) FROM employees;
  1. SUM
SELECT SUM(salary) FROM employees;
  1. AVG
SELECT AVG(salary) FROM employees;
  1. MAX
SELECT MAX(salary) FROM employees;
  1. MIN
SELECT MIN(salary) FROM employees;

4. GROUP BY / HAVING

  1. GROUP BY
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;
  1. GROUP BY + HAVING
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;
  1. CASE + GROUP
SELECT department_id, COUNT(*) AS cnt,
       CASE WHEN COUNT(*) > 10 THEN 'Large'
            ELSE 'Small'
       END AS size_group
FROM employees
GROUP BY department_id;
  1. 조건별 집계
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
HAVING COUNT(*) > 3;
  1. 조인 + 그룹 집계
SELECT d.department_name, COUNT(e.employee_id)
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;
  1. 집계 + 정렬
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC;

5. JOIN

  1. INNER JOIN
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
  1. LEFT JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
  1. RIGHT JOIN
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;
  1. FULL OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
  1. NATURAL JOIN
SELECT * FROM employees NATURAL JOIN departments;
  1. 3테이블 JOIN
SELECT e.first_name, d.department_name, l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
  1. SELF JOIN
SELECT e1.first_name, e2.first_name AS manager
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;
  1. SELF JOIN (매니저 이름)
SELECT e.first_name AS emp_name, m.first_name AS mgr_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

6. 서브쿼리

  1. 서브쿼리 IN
SELECT first_name 
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);
  1. 서브쿼리 EXISTS
SELECT first_name 
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM departments d
    WHERE d.department_id = e.department_id
);
  1. 집계 + 서브쿼리
SELECT * 
FROM (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) 
WHERE avg_sal > 5000;
  1. WITH 다중 서브쿼리
WITH high_paid AS (
    SELECT * FROM employees WHERE salary > 10000
), low_paid AS (
    SELECT * FROM employees WHERE salary < 3000
)
SELECT * FROM high_paid
UNION ALL
SELECT * FROM low_paid;

7. 페이징 / 순위

  1. ROWNUM
SELECT * FROM employees WHERE ROWNUM <= 5;
  1. 페이징
SELECT * 
FROM (
    SELECT e.*, ROWNUM rn
    FROM (
        SELECT * FROM employees ORDER BY hire_date DESC
    ) e
    WHERE ROWNUM <= 30
) 
WHERE rn > 20;
  1. DENSE_RANK
SELECT first_name, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
  1. ROW_NUMBER
SELECT first_name, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC)
FROM employees;
  1. PARTITION BY
SELECT department_id, first_name,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM employees;
  1. 윈도우함수 RANK
SELECT employee_id, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;
  1. 윈도우함수 SUM OVER
SELECT department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;
  1. 윈도우함수 LAG
SELECT employee_id, salary,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;
  1. 윈도우함수 LEAD
SELECT employee_id, salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

 

8) DML (INSERT / UPDATE / DELETE)

8-1. INSERT

INSERT INTO employees (employee_id, first_name)
VALUES (999, 'NewGuy');

8-2. UPDATE

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;

8-3. DELETE

DELETE FROM employees
WHERE hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD');

8-4. 조건부 UPDATE / DELETE

-- UPDATE
UPDATE employees
SET salary = salary + 500
WHERE job_id = 'SA_REP';

-- DELETE
DELETE FROM employees
WHERE department_id = 50 AND salary < 3000;

8-5. 서브쿼리 INSERT / UPDATE / DELETE

-- INSERT (서브쿼리로 값 채움)
INSERT INTO employees (employee_id, first_name, department_id)
SELECT emp_seq.NEXTVAL, 'AutoEmp', department_id
FROM departments
WHERE department_name = 'IT';

-- UPDATE (서브쿼리로 부서 이동)
UPDATE employees
SET department_id = (
  SELECT department_id FROM departments WHERE department_name = 'Sales'
)
WHERE job_id = 'SA_REP';

-- DELETE (서브쿼리로 조건 매칭)
DELETE FROM employees
WHERE department_id = (
  SELECT department_id FROM departments WHERE department_name = 'Shipping'
);

8-6. 다중 INSERT (INSERT ALL)

INSERT ALL
  INTO employees (employee_id, first_name) VALUES (1001, 'A')
  INTO employees (employee_id, first_name) VALUES (1002, 'B')
SELECT * FROM dual;

8-7. MERGE (UPSERT)

MERGE INTO employees e
USING (SELECT 999 AS id, 'Test' AS name FROM dual) src
ON (e.employee_id = src.id)
WHEN MATCHED THEN
  UPDATE SET e.first_name = src.name
WHEN NOT MATCHED THEN
  INSERT (employee_id, first_name)
  VALUES (src.id, src.name);

9) 시퀀스 / 트리거

9-1. 시퀀스 생성 및 사용

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

SELECT emp_seq.NEXTVAL FROM dual;  -- 다음 값

9-2. 트리거로 자동 증가 적용

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.employee_id IS NULL THEN
    SELECT emp_seq.NEXTVAL INTO :NEW.employee_id FROM dual;
  END IF;
END;
/

10) 날짜·시간

-- 현재 시각
SELECT SYSDATE FROM dual;

-- 날짜 → 문자열
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

-- 문자열 → 날짜
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM dual;

-- 날짜 연산
SELECT SYSDATE + 1               FROM dual;  -- 1일 후
SELECT ADD_MONTHS(hire_date, 6)  FROM employees;  -- 6개월 후
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) FROM employees;
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
SELECT LAST_DAY(SYSDATE) FROM dual;

11) 문자열

-- 연결: Oracle은 || 사용
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

-- 부분 문자열
SELECT SUBSTR(first_name, 1, 3) FROM employees;

-- 위치 찾기
SELECT INSTR(first_name, 'a') FROM employees;

-- 길이
SELECT LENGTH(first_name) FROM employees;  -- 문자 길이
-- LENGTHB(first_name)    -- 바이트 길이 (필요 시)

11-1. LISTAGG 집계

SELECT department_id,
       LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS names
FROM employees
GROUP BY department_id;

12) 숫자 함수

-- 반올림/버림
SELECT ROUND(salary, -2), TRUNC(salary, -2) FROM employees;

-- 절대값/부호/제곱/나머지/제곱근
SELECT ABS(-100)           FROM dual;
SELECT SIGN(-100)          FROM dual;  -- -1
SELECT POWER(2, 10)        FROM dual;
SELECT MOD(11, 3)          FROM dual;
SELECT SQRT(4)             FROM dual;

13) 변환·NULL·분기

-- 숫자 → 문자열 포맷
SELECT TO_CHAR(salary, '999,999.00') FROM employees;

-- NULL 대체
SELECT NVL(commission_pct, 0) FROM employees;

-- 분기: DECODE
SELECT DECODE(job_id, 'IT_PROG', 'Developer', 'Other')
FROM employees;

-- 분기: CASE
SELECT CASE WHEN salary > 10000 THEN 'High' ELSE 'Low' END
FROM employees;

14) 집합 연산

-- 중복 제거
SELECT DISTINCT department_id FROM employees;

-- UNION / UNION ALL / INTERSECT / MINUS
SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;

SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;

SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM departments;

SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees;

15) 공통 테이블 식(CTE) / 인라인 뷰 / 스칼라 서브쿼리

-- WITH (CTE)
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_avg
WHERE avg_sal > 5000;

-- 인라인 뷰 + TOP N
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

-- 스칼라 서브쿼리
SELECT e.first_name,
       (SELECT d.department_name
        FROM departments d
        WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;

-- WITH + ROWNUM 필터 (페이징)
WITH ordered AS (
  SELECT e.*, ROWNUM rn
  FROM (SELECT * FROM employees ORDER BY hire_date DESC) e
)
SELECT * FROM ordered
WHERE rn BETWEEN 11 AND 20;

16) 조인 응용

-- 조건 포함 조인
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id
WHERE d.department_name LIKE 'S%';

-- 3테이블 조인
SELECT e.first_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations  l ON d.location_id = l.location_id;

-- 자기 조인 (매니저 이름)
SELECT e.first_name AS emp_name, m.first_name AS mgr_name
FROM employees e
JOIN employees m
  ON e.manager_id = m.employee_id;

17) 윈도우 함수 요약

-- 순위/행넘버
SELECT first_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rnum
FROM employees;

-- 파티션 집계
SELECT department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

-- 이전/다음 행 값
SELECT employee_id, salary,
       LAG(salary, 1)  OVER (ORDER BY hire_date) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

18) 기타 실무 자주 쓰는 패턴

-- ORDER BY 컬럼 위치로 정렬
SELECT first_name, salary
FROM employees
ORDER BY 2 DESC;

-- 중첩 함수 예: 근속연수(년)
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)/12, 1) AS years_worked
FROM employees;

 

'DB' 카테고리의 다른 글

Oracle SQL 테이블 설계부터 집계함수까지  (0) 2025.06.05
Oracle 인덱스 적용 전·후 성능 비교  (0) 2025.06.05
Oracle 데이터 타입 & 제약조건(Constraints) 정리  (0) 2025.06.02
Oracle 11g 조인 정리  (0) 2025.05.30
MySQL vs Oracle 11g 데이터 타입 비교  (0) 2025.05.30

'DB' Related Articles

티스토리툴바