📌 Oracle SQL 고급 문법 총정리

1. 👤 직원과 관리자 이름 조회 (Self Join + Sub Query)

Executive 부서에 소속된 사원의 이름과 해당 사원의 관리자 이름을 조회하는 방법

✅ Self Join + Join 활용

SELECT A.last_name AS emp_name, B.last_name AS mgr_name
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id
JOIN departments1 D ON A.department_id = D.department_id
WHERE D.department_name = 'Executive';

✅ Sub Query 활용

SELECT A.last_name AS emp_name,
       (SELECT B.last_name FROM employees B
        WHERE B.employee_id = A.manager_id) AS mgr_name
FROM employees A
WHERE A.department_id = (SELECT department_id FROM departments1
                         WHERE department_name = 'Executive');

---

2. 💰 급여 조건 조합 쿼리 (UNION, MINUS, INTERSECT)

조건	사용 예시
급여 > 10,000 OR 부서번호 = 100	UNION 사용
급여 > 10,000 AND 부서번호 ≠ 100	MINUS 사용
급여 > 10,000 AND 부서번호 = 100	INTERSECT 사용

-- UNION
SELECT employee_id, salary, department_id FROM employees
WHERE salary > 10000
UNION
SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 100;

-- MINUS
SELECT employee_id, salary, department_id FROM employees
WHERE salary > 10000
MINUS
SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 100;

-- INTERSECT
SELECT employee_id, salary, department_id FROM employees
WHERE salary > 10000
INTERSECT
SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 100;

---

3. 🔍 Sub Query 구조와 활용

📌 종류와 위치

구분	설명 예시
Scalar SubQuery	SELECT절에서 단일 값 반환	각 사원의 관리자 이름
Inline View	FROM절에서 서브쿼리를 테이블처럼 사용	부서별 최고 급여 사원
Nested SubQuery	WHERE절에서 조건으로 사용	평균 이상 급여 직원 조회

✅ 예시1: Scalar SubQuery

SELECT employee_id, first_name,
       (SELECT first_name FROM employees B WHERE B.employee_id = A.manager_id) AS mgr_name
FROM employees A;

✅ 예시2: Inline View

SELECT e.department_id, e.employee_id, e.first_name, e.salary
FROM employees e
JOIN (SELECT department_id, MAX(salary) AS max_sal FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id AND e.salary = d.max_sal;

✅ 예시3: Nested SubQuery

SELECT department_id, department_name
FROM departments1
WHERE department_id IN (
    SELECT department_id FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
);

---

4. 💸 트랜잭션(Transaction)과 롤백(Rollback)

• Transaction: 일련의 작업 단위 (예: 계좌이체)
• Commit: 작업 확정
• Rollback: 작업 취소

UPDATE employees SET salary = salary + 1000 WHERE employee_id = 100;
-- 아직 메모리 상태
COMMIT;  -- 확정
-- 또는
ROLLBACK;  -- 취소

🔒 Lock 개념

• 작업 중 commit 되지 않으면 다른 사용자가 해당 행 접근 불가
• 원인: 해당 사용자가 락을 걸고 있기 때문

---

5. 👓 View (가상 테이블)

• View: 복잡한 JOIN 쿼리를 미리 만들어 테이블처럼 사용
• 장점: 보안, 유지보수, 권한 위임

✅ 단순 뷰

CREATE OR REPLACE VIEW emp_vw1
AS SELECT employee_id, last_name, salary FROM employees1;

✅ 복합 뷰

CREATE OR REPLACE VIEW emp_vw2 AS
SELECT e.employee_id, e.last_name, d.department_name, l.city, c.country_name, r.region_name
FROM employees e
JOIN departments1 d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id
JOIN regions r ON c.region_id = r.region_id;

✅ View에서 데이터 변경

• 단순 뷰: 가능 (단, NOT NULL 조건 주의)
• 복합 뷰: 제한 있음 (함수, GROUP BY 등 포함 시 불가)

---

6. 🎯 입사일이 빠른 10명 조회

-- 잘못된 예시
SELECT ROWNUM, employee_id, hire_date
FROM employees
WHERE ROWNUM <= 10
ORDER BY hire_date;

-- 정확한 예시
CREATE TABLE emp_hired_ASC AS
SELECT * FROM employees ORDER BY hire_date;

SELECT ROWNUM, employee_id, last_name, hire_date
FROM emp_hired_ASC
WHERE ROWNUM <= 10;

---

📌 요약 포인트

• Self Join은 같은 테이블에서 여러 역할을 참조할 때 사용
• Sub Query는 위치에 따라 역할이 다름
• 집합 연산자(UNION, MINUS, INTERSECT)는 조건 조합에 따라 선택
• 트랜잭션과 롤백은 데이터 일관성을 보장
• View는 복잡한 쿼리를 단순하게 관리하고 보안 향상에도 기여