SQL 서브쿼리 (Subquery)와 데이터 조작 명령어 (DML) 정리

SQL에서 서브쿼리와 데이터 조작 명령어는 데이터베이스를 효율적으로 관리하고, 복잡한 데이터를 처리하는 데 중요한 역할을 합니다. 이번 글에서는 각 요소의 정의, 기능, 구문 및 예제를 통해 자세히 설명하겠습니다.

1. 서브쿼리 (Subquery)

서브쿼리란?

서브쿼리는 하나의 SQL 문장 내에 포함된 다른 SQL 문장으로, 주로 메인 쿼리의 조건이나 데이터를 제공하는 데 사용됩니다. 서브쿼리는 데이터의 유연한 검색 및 필터링을 가능하게 해줍니다.

서브쿼리의 주요 기능

• 복잡한 쿼리 단순화: 서브쿼리를 사용하면 복잡한 조건을 간단히 처리할 수 있습니다.
• 재사용성 향상: 동일한 로직을 여러 번 사용해야 할 때 서브쿼리를 활용하여 코드 중복을 줄일 수 있습니다.

서브쿼리 구문 예시

SELECT column1, column2
FROM table1
WHERE column3 = (SELECT column4 FROM table2 WHERE condition);

2. 서브쿼리의 종류

단일 행 서브쿼리 (Single Row Subquery)

• 정의: 서브쿼리가 하나의 결과(행)만 반환하는 경우입니다.
• 사용: 주로 =와 같은 비교 연산자와 함께 사용됩니다.
• 예제:

SELECT last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

이 쿼리는 'Sales' 부서의 department_id를 서브쿼리로 찾아, 해당 부서에 소속된 모든 직원의 성(last_name)을 조회합니다.

다중 행 서브쿼리 (Multiple Row Subquery)

• 정의: 서브쿼리가 여러 행을 반환하는 경우입니다.
• 연산자: IN, ANY, ALL을 사용하여 조건을 지정합니다.
• 예제:

SELECT last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

이 쿼리는 location_id가 1700인 부서에 소속된 모든 직원의 성(last_name)을 조회합니다.

스칼라 서브쿼리 (Scalar Subquery)

• 정의: 서브쿼리가 정확히 하나의 행과 하나의 열(값)을 반환하는 경우입니다.
• 사용: SELECT 절에서 계산된 값을 가져오거나, 조건 비교에 사용됩니다.
• 예제:

SELECT last_name, 
       (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name
FROM employees;

이 쿼리는 각 직원의 성(last_name)과 그 직원이 속한 부서의 이름(department_name)을 조회합니다. 서브쿼리는 항상 하나의 값(부서 이름)을 반환합니다.

3. 인라인 뷰 (Inline View)

정의 및 특징

인라인 뷰는 서브쿼리를 FROM 절에서 사용하여 일시적인 테이블을 생성하는 방법입니다. 이를 통해 복잡한 쿼리를 간소화할 수 있습니다.

• 기능: 특정 조건을 만족하는 데이터 집합을 가상의 테이블로 사용하여 추가적인 연산이나 필터링을 가능하게 합니다.
• 장점: 복잡한 쿼리를 단순화하고 가독성을 높입니다.

사용 예시

SELECT *
FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 10;

이 쿼리는 직원들 중 급여(salary)가 높은 상위 10명의 정보를 조회합니다. 내부 서브쿼리가 급여 순으로 정렬된 결과를 만들어 주고, 외부 쿼리가 그 중 상위 10개를 필터링합니다.

4. ROWNUM 사용 예시

ROWNUM은 오라클 데이터베이스에서 각 행에 임시로 번호를 붙여주는 가상 컬럼입니다. 이를 사용하여 결과 집합의 특정 범위의 데이터를 쉽게 선택할 수 있습니다.

예시

SELECT *
FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 10;

위 쿼리는 급여가 높은 직원의 목록에서 상위 10명의 정보를 선택합니다.

2. 데이터 조작 언어 (DML) 명령어

데이터 조작 언어(DML)는 데이터베이스의 데이터를 추가, 수정, 삭제하는 데 사용되는 SQL 명령어입니다. 다음은 DML의 주요 명령어들입니다.

1. INSERT

• 기능: 테이블에 새로운 데이터를 추가합니다.
• 구문:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• 예제: DEPARTMENTS 테이블에 새로운 행 삽입

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (270, 'Data Analytics', 200, 1700);

이 쿼리는 DEPARTMENTS 테이블에 새로운 부서를 추가합니다.

2. UPDATE

• 기능: 기존의 데이터를 수정합니다.
• 구문:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• 예제: DEPARTMENTS 테이블 데이터 수정

UPDATE DEPARTMENTS
SET DEPARTMENT_NAME = 'IT Bank'
WHERE DEPARTMENT_NAME = 'IT Support';

이 쿼리는 'IT Support' 부서의 이름을 'IT Bank'로 변경합니다.

3. DELETE

• 기능: 테이블에서 특정 데이터를 삭제합니다.
• 구문:

DELETE FROM table_name
WHERE condition;

• 예제: DEPARTMENTS 테이블에서 행 삭제

DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Sales';

이 쿼리는 'Sales'라는 이름의 부서를 삭제합니다.

4. MERGE

• 기능: 두 개의 테이블을 비교하여, 일치하는 데이터는 업데이트하고, 일치하지 않는 데이터는 새로 삽입합니다.
• 구문:

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN
  UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
  INSERT (column1, column2, ...)
  VALUES (value1, value2, ...);

• 예제:

MERGE INTO emp_it E
USING DUAL
ON (E.EMPLOYEE_ID = 'XXX' AND E.JOB_ID = 'YYY')
WHEN MATCHED THEN
  UPDATE SET E.EMPLOYEE_ID = 200
WHEN NOT MATCHED THEN
  INSERT (E.EMPLOYEE_ID, E.JOB_ID, E.SALARY)
  VALUES ('XXXX', 'YYYY', 20000);

이 쿼리는 특정 조건에 맞는 데이터가 존재하면 업데이트하고, 없으면 새로 삽입합니다.

5. CTAS (Create Table As Select)

• 기능: 기존 테이블의 데이터를 기반으로 새로운 테이블을 생성합니다.
• 구문:

CREATE TABLE new_table AS
SELECT columns
FROM existing_table
WHERE condition;

• 예제: jobs_it 사본 테이블 생성

CREATE TABLE jobs_it AS
SELECT * FROM jobs
WHERE min_salary > 6000;

이 쿼리는 min_salary가 6000 이상인 jobs 테이블의 데이터를 기반으로 새로운 테이블 jobs_it을 생성합니다.

Example Exercises with SQL Queries

1. DEPTS 테이블에 새로운 행 삽입 후 ROLLBACK으로 취소하기

실습 목표: 새로운 데이터를 삽입하고, ROLLBACK으로 그 변화를 취소합니다.

-- 트랜잭션 시작
BEGIN;

-- DEPTS 테이블에 새로운 행 삽입
INSERT INTO DEPTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (280, 'Marketing', 100, 1800);

-- 삽입 후 데이터 확인
SELECT * FROM DEPTS WHERE DEPARTMENT_ID = 280;

-- ROLLBACK으로 변경사항 취소
ROLLBACK;

-- 변경된 데이터 확인 (280번 부서가 없음을 확인)
SELECT * FROM DEPTS WHERE DEPARTMENT_ID = 280;

2. DEPTS 테이블의 다양한 조건으로 데이터 수정하기

실습 목표: 여러 조건을 사용해 DEPTS 테이블의 데이터를 업데이트합니다.

-- 특정 부서의 이름 수정
UPDATE DEPTS
SET DEPARTMENT_NAME = 'Sales and Marketing'
WHERE DEPARTMENT_NAME = 'Marketing';

-- LOCATION_ID가 1800인 부서의 MANAGER_ID 수정
UPDATE DEPTS
SET MANAGER_ID = 101
WHERE LOCATION_ID = 1800;

-- 수정된 데이터 확인
SELECT * FROM DEPTS WHERE DEPARTMENT_NAME = 'Sales and Marketing' OR LOCATION_ID = 1800;

3. department_id로 행 삭제하기

실습 목표: 특정 department_id에 해당하는 행을 삭제합니다.

-- DEPTS 테이블에서 department_id가 280인 행 삭제
DELETE FROM DEPTS
WHERE DEPARTMENT_ID = 280;

-- 삭제된 데이터 확인 (280번 부서가 없어야 함)
SELECT * FROM DEPTS WHERE DEPARTMENT_ID = 280;

4. 사본 테이블 데이터 삭제 및 수정하기

실습 목표: 사본 테이블을 생성하고, 그 데이터를 수정하거나 삭제합니다.

-- DEPTS 테이블의 사본 테이블 생성
CREATE TABLE DEPTS_COPY AS
SELECT * FROM DEPTS;

-- 사본 테이블의 데이터 확인
SELECT * FROM DEPTS_COPY;

-- 사본 테이블에서 특정 부서 삭제
DELETE FROM DEPTS_COPY
WHERE DEPARTMENT_NAME = 'Sales and Marketing';

-- 삭제된 데이터 확인
SELECT * FROM DEPTS_COPY WHERE DEPARTMENT_NAME = 'Sales and Marketing';

-- 사본 테이블에서 특정 부서의 MANAGER_ID 수정
UPDATE DEPTS_COPY
SET MANAGER_ID = 102
WHERE DEPARTMENT_NAME = 'Marketing';

-- 수정된 데이터 확인
SELECT * FROM DEPTS_COPY WHERE DEPARTMENT_NAME = 'Marketing';

5. jobs_it 테이블 생성 후 조건에 맞는 데이터 추가 및 수정하기

실습 목표: 새로 생성한 테이블에 데이터를 추가하고 수정하는 과정을 익힙니다.

-- jobs 테이블에서 조건에 맞는 데이터를 기반으로 jobs_it 테이블 생성
CREATE TABLE jobs_it AS
SELECT * FROM jobs
WHERE min_salary > 6000;

-- 새로 생성된 테이블 확인
SELECT * FROM jobs_it;

-- jobs_it 테이블에 새로운 데이터 추가
INSERT INTO jobs_it (job_id, job_title, min_salary, max_salary)
VALUES ('IT_PROG', 'IT Programmer', 7000, 12000);

-- 추가된 데이터 확인
SELECT * FROM jobs_it WHERE job_id = 'IT_PROG';

-- jobs_it 테이블의 특정 행 수정
UPDATE jobs_it
SET max_salary = 13000
WHERE job_id = 'IT_PROG';

-- 수정된 데이터 확인
SELECT * FROM jobs_it WHERE job_id = 'IT_PROG';