Database 4일차

-- DB03 Quiz 2 문제
--관리자사번(manager_id)이 145번인 사원을 찾아 위 테이블에 있는 사원의 사번과 일치하면 
--보너스 금액(bonus_amt)에 자신의 급여의 1%를 보너스로 갱신하고, ex3_3 테이블에 있는 사원의 사번과 
--일치하지 않는 사원을 신규 입력 (이때 보너스 금액은 급여의 0.5%로 한다) 하는 MERGE 문을 작성해 보자.

실행 사진 및 출력결과

 

 

실행코드

-- DB03 Quiz 2 문제
--관리자사번(manager_id)이 145번인 사원을 찾아 위 테이블에 있는 사원의 사번과 일치하면 
--보너스 금액(bonus_amt)에 자신의 급여의 1%를 보너스로 갱신하고, ex3_3 테이블에 있는 사원의 사번과 
--일치하지 않는 사원을 신규 입력 (이때 보너스 금액은 급여의 0.5%로 한다) 하는 MERGE 문을 작성해 보자.
DELETE ex3_3;

INSERT INTO ex3_3 (employee_id)
SELECT e.employee_id
FROM employees e, sales s
WHERE e.employee_id = s.employee_id
  AND s.SALES_MONTH BETWEEN '200010' AND '200012'
GROUP BY e.employee_id;

COMMIT;

MERGE INTO ex3_3 tgt
USING (
    SELECT employee_id, salary
    FROM employees
    WHERE manager_id = 145
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
    UPDATE SET tgt.bonus_amount = src.salary * 0.01
WHEN NOT MATCHED THEN
    INSERT (employee_id, bonus_amount)
    VALUES (src.employee_id, src.salary * 0.005);

 

 

---

 초기화

DELETE ex3_3;

• ex3_3 테이블의 기존 데이터 모두 삭제.

---

데이터 준비

INSERT INTO ex3_3 (employee_id)
SELECT e.employee_id
FROM employees e, sales s
WHERE e.employee_id = s.employee_id
  AND s.SALES_MONTH BETWEEN '200010' AND '200012'
GROUP BY e.employee_id;
COMMIT;

• 2000년 10월~12월 매출이 있었던 사원의 employee_id를 ex3_3에 삽입.
• COMMIT으로 확정.

---

 MERGE 실행

MERGE INTO ex3_3 tgt
USING (
    SELECT employee_id, salary
    FROM employees
    WHERE manager_id = 145
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
    UPDATE SET tgt.bonus_amount = src.salary * 0.01
WHEN NOT MATCHED THEN
    INSERT (employee_id, bonus_amount)
    VALUES (src.employee_id, src.salary * 0.005);

• 조건 일치(MATCHED): 이미 있는 사원 → bonus_amount = 급여 * 1% 갱신.
• 조건 불일치(NOT MATCHED): 없는 사원 → 새로 삽입, bonus_amount = 급여 * 0.5%.

---

요약: 1,테이블 초기화 → 2. 특정 기간 매출 사원 삽입

          → 3. manager_id=145 기준으로 MERGE 실행 (있으면 갱신, 없으면 삽입).

---

[ DELETE 문 ]

테이블에 있는 데이터를 삭제하는 문장

① 일반 구문 DELETE [FROM] [스키마.]테이블명 WHERE delete조건;

 

② 특정 파티션만 삭제할 경우의 구문 DELETE [FROM] [스키마.]테이블명 PARTITION (파티션명) WHERE delete조건;

 

SELECT *
FROM ex3_3
ORDER BY employee_id;

-- DELETE
DELETE ex3_3;

---

[트랜젝션(Transaction)]
• 오라클의 트랜젝션은 데이터의 일관성을 위해서 ALL-or Nothing 방식으로 처리한다.
• 즉, 여러 개의 명령어중 하나만 잘못되어도 모든 명령을 취소 시켜서 데이터의 일관성을 유지한다.
• 트랜젝션 관리를 위해 제공하는 명령어는 두가지인데. COMMIT와 ROLLBACK이다.

 

트랜젝션(Transaction) - COMMIT
• COMMIT 명령은 모든 작업을 정상 처리 완료하고 처리의 모든 과정을 확정하는 명령이다.
• 모든 트랜젝션의 처리과정을 데이터베이스에 반영하고 변경된 모든 내용을 영구 저장을 한다.
• COMMIT 명령어를 수행하면 하나의 트랜젝션 과정을 종료한다.

 

트랜젝션(Transaction) - ROLLBACK
• ROLLBACK 명령은 작업중 문제가 발생해서 트랜젝션 처리 과정에 발생한 변경 내용을 취소하는 명령이다.
• ROLLBACK은 트랜젝션으로 인한 하나의 묶음 처리가 시작되기 이전 상태로 되돌린다.

 

[ COMMIT, ROLLBACK, TRUNCATE ]

- COMMIT
  변경한 데이터를 데이터베이스에 최종적으로 반영
  구문
  COMMIT [WORK] [TO SAVEPOINT 세이브포인트명] ;

-- COMMIT, ROLLBACK, TRUNCATE
CREATE TABLE ex3_4 (
    employee_id NUMBER);
    
COMMIT;

INSERT INTO ex3_4 VALUES (100);

- ROLLBACK
  변경한 데이터를 변경 전 상태로 되돌림
  구문
  ROLLBACK [WORK] [TO SAVEPOINT 세이브포인트명] ;

-- COMMIT, ROLLBACK, TRUNCATE
CREATE TABLE ex3_4 (
    employee_id NUMBER);
    
COMMIT;

INSERT INTO ex3_4 VALUES (100);

ROLLBACK;

 

COMMIT 하고 DELETE하고 ROLLBACK 하면 사라진게 다시 복구됨.

Table EX3_4이(가) 생성되었습니다.

커밋 완료.

1 행 이(가) 삽입되었습니다.

롤백 완료.

1 행 이(가) 삽입되었습니다.

커밋 완료.

1 행 이(가) 삭제되었습니다.

롤백 완료.

 

- TRUNCATE

  DELETE 처럼 테이블 데이터를 삭제

  실행 시 영구적으로 데이터가 삭제되는 DDL 문으로 WHERE 조건은 붙을 수 없다.

  구문 TRUNCATE TABLE [스키마명.]테이블명;

TRUNCATE는 영구적으로 삭제되기 때문에 롤백으로 복구가 되지 않는다.

---

[ 의사컬럼 ]

테이블의 컬럼처럼 동작하지만 실제로 테이블에 저장되지는 않는 컬럼
CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, LEVEL
계층형 쿼리에서 사용하는 의사컬럼
NEXTVAL, CURRVAL
시퀀스에서 사용하는 의사컬럼
ROWNUM,ROWID
ROWNM은 쿼리에서 반환되는 각 로우에 대한 순서값
ROWID는 테이블에 저장된 각 로우가 저장된 주소값

 

-- 의사 컬럼
SELECT ROWNUM, employee_id
    FROM employees;

SELECT ROWNUM, employee_id
    FROM employees
WHERE ROWNUM < 5;

SELECT ROWNUM, employee_id, ROWID
    FROM employees
WHERE ROWNUM < 5;

- ROWNUM: 결과 행에 순차적으로 붙는 번호 → 상위 N개 추출에 활용
- ROWID: 행의 고유 주소값 → 특정 행을 빠르게 식별 가능
- ROWNUM은 쿼리 실행 시점에 붙는 번호라서 ORDER BY와 함께 쓰려면 서브쿼리가 필요함.

---

[ 연산자 ]

수식연산자 : +, -, *, /
문자연산자 : ||
논리연산자 : >, <, >=, <=, =, <>, !=, ^=
집합연산자 : UNION, UNION ALL, INTERSECT, MINUS
계층형 쿼리 연산자 : PRIOR, CONNECT_BY_ROOT

SELECT employee_id || '-' || emp_name AS employee_info
    FROM employees
WHERE ROWNUM < 5;

- || → 문자열 연결
- AS employee_info → 결과 컬럼 이름 지정
- ROWNUM < 5 → 상위 4개 행만 출력
이 쿼리는 employees 테이블에서 앞의 4명만 뽑아, employee_id와 emp_name을 id-이름 형태로 합쳐서 보여주는 것

---

[ 표현식 ]

한 개 이상의 값과 연산자 그리고 SQL 함수 등이 결합된 식

CASE 표현식

구문

CASE WHEN 조건1 THEN 값1

          WHEN 조건2 THEN 값2

          …

          ELSE 기타값

END

 

SELECT employee_id, salary, 
    CASE WHEN salary <= 5000 THEN 'C등급'
        WHEN salary > 5000 AND salary <= 15000 THEN 'B등급'
        ELSE 'A등급'
    END AS salary_grade
FROM employees;

1. CASE ... END
- SQL에서 조건에 따라 다른 값을 반환하는 조건문.
- 프로그래밍 언어의 if-else와 비슷한 역할.

2. 조건별 의미
- WHEN salary <= 5000 THEN 'C등급'
→ 급여가 5000 이하인 직원은 C등급
- WHEN salary > 5000 AND salary <= 15000 THEN 'B등급'
→ 급여가 5001 ~ 15000 사이인 직원은 B등급
- ELSE 'A등급'
→ 그 외(즉, 15000 초과)는 A등급

3. AS salary_grade
- 결과 컬럼에 별칭(alias)을 붙임.
- 출력 시 컬럼명이 salary_grade로 표시됨.

4. 전체 결과
- employee_id : 직원 번호
- salary : 급여
- salary_grade : CASE 문으로 계산된 급여 등급

---

[ 조건식 ]

조건 혹은 조건식(Condition)은 한 개 이상의 표현식과 논리 연산자가 결합된 식
- 비교 조건식
  논리 연산자나 ANY, SOME, ALL 키워드로 비교하는 조건식
  TRUE, FALSE, UNKNOWN 3가지 타입을 반환

-- 조건식
-- 비교 조건식
SELECT employee_id, salary
    FROM employees
WHERE salary = ANY (2000, 3000, 4000)
ORDER BY employee_id;

SELECT employee_id, salary
    FROM employees
WHERE salary = 2000
    OR salary = 3000
    OR salary = 4000
ORDER BY employee_id; 

SELECT employee_id, salary
    FROM employees
WHERE salary = SOME (2000, 3000, 4000)
ORDER BY employee_id;

-- 위의 3개다 같은 결과를 출력하는 코드

이 쿼리는 employees 테이블에서 급여가 2000, 3000, 4000 중 하나인 직원만 조회
조회된 결과는 employee_id와 salary만 보여주며, 직원 번호 기준으로 정렬
즉, 특정 급여값을 가진 직원 목록을 번호 순으로 출력하는 쿼리

 

- 논리 조건식
  AND, OR, NOT을 사용하는 조건식

SELECT employee_id, salary
    FROM employees
WHERE NOT (salary >= 2500)
ORDER BY employee_id;

1. **`WHERE NOT (salary >= 2500)`**  
   - `salary >= 2500` 조건을 **부정(NOT)** → 즉, **급여가 2500 이상이 아닌 경우**  
   - 결국 **급여가 2500 미만인 직원만** 조회됨.

2. **`SELECT employee_id, salary`**  
   - 직원 번호(`employee_id`)와 급여(`salary`)만 출력.

3. **`ORDER BY employee_id`**  
   - 결과를 직원 번호 기준으로 오름차순 정렬.

- NULL 조건식
  특정 값이 NULL인지 여부를 체크하는 조건식
  IS NULL, IS NOT NULL

 

[ 조건식 ]

- BETWEEN AND 조건식
  범위에 해당되는 값을 찾을 때 사용

-- BETWEEN AND 조건식
SELECT employee_id, salary
    FROM employees
WHERE salary BETWEEN 2000 AND 2500 -- >= 2000 and <= 2500
    ORDER BY employee_id;

1. **`BETWEEN 2000 AND 2500`**  
   - 급여(`salary`)가 **2000 이상이고 2500 이하**인 직원만 선택합니다.  
   - 즉, `salary >= 2000 AND salary <= 2500`과 동일한 의미입니다.

2. **`SELECT employee_id, salary`**  
   - 직원 번호와 급여만 출력합니다.

3. **`ORDER BY employee_id`**  
   - 결과를 직원 번호 기준으로 오름차순 정렬합니다.

 

 

- IN 조건식
  조건절에 명시한 값이 포함된 건을 반환, ANY와 비슷

-- IN 조건식
SELECT employee_id,salary
    FROM employees
WHERE salary IN (2000, 3000, 4000)
ORDER BY employee_id;
    
SELECT employee_id,salary
    FROM employees
WHERE salary NOT IN (2000, 3000, 4000)
ORDER BY employee_id;

 

- IN → 지정한 값 목록에 포함된 경우 선택
- NOT IN → 지정한 값 목록에 포함되지 않은 경우 선택
즉, 첫 번째는 2000·3000·4000 급여 직원만, 두 번째는 그 외 급여 직원만 보여주는 차이입니다.

 

- EXISTS 조건식
  IN과 비슷하지만 후행 조건절로 값의 리스트가 아닌 서브쿼리만 올 수 있다
  또한 서브쿼리 내에서 조인조건이 있어야 한다

 

-- EXISTS 조건식
SELECT department_id, department_name
    FROM departments a
WHERE EXISTS ( SELECT *
                                FROM employees b
                                WHERE a.department_id = b.department_id
                                AND b.salary > 3000)
ORDER BY a.department_name;

- EXISTS → 서브쿼리 결과가 존재하면 TRUE
- 이 쿼리는 급여가 3000 초과인 직원이 속한 부서만 조회하는 SQL입니다.
- 결과적으로, 조건을 만족하는 부서 목록을 알 수 있습니다.

- LIKE 조건식
  문자열의 패턴을 검색할떄 사용

 

-- LIKE 조건식
SELECT emp_name
    FROM employees
WHERE emp_name LIKE 'Al%'  -- A를 포함하는 '%A%', A로 끝나는 '%A'
ORDER BY emp_name;

### 1. `LIKE 'Al%'`
- `LIKE` 연산자는 **패턴 매칭**을 위한 조건식입니다.  
- `'Al%'` → **"Al"로 시작하는 문자열**을 찾습니다.  
  - `%`는 **0개 이상의 임의의 문자**를 의미합니다.  
  - 예: `Allen`, `Alfred`, `Alina` 등이 조건에 맞습니다.

---

### 2. 주석 부분 보완
- 주석에 적힌 설명은 조금 혼동이 있습니다. 올바른 패턴은 다음과 같습니다:
  - `'%A%'` → A를 **포함하는** 문자열  
  - `'%A'` → A로 **끝나는** 문자열  
  - `'A%'` → A로 **시작하는** 문자열  
  - `'Al%'` → Al로 **시작하는** 문자열  

즉, 현재 코드의 조건은 **"Al로 시작하는 이름"**을 찾는 것입니다.

---

### 3. `ORDER BY emp_name`
- 결과를 `emp_name` 기준으로 **오름차순 정렬**합니다.

 

CREATE TABLE ex3_5 (
    names VARCHAR2(30));
    
INSERT INTO ex3_5 VALUES ('홍길동');
INSERT INTO ex3_5 VALUES ('홍길용');
INSERT INTO ex3_5 VALUES ('홍길상');
INSERT INTO ex3_5 VALUES ('홍길상동');

SELECT *
FROM ex3_5
WHERE names LIKE '홍길_';

SELECT *
FROM ex3_5
WHERE names LIKE '홍길__';

- LIKE '홍길_' → 홍길 뒤에 한 글자만 있는 이름 검색
- LIKE '홍길__' → 홍길 뒤에 두 글자가 있는 이름 검색
즉, _는 글자 수를 정확히 제한할 때 쓰이고, %는 글자 수 제한 없이 패턴을 찾을 때 쓰임

---

마지막 퀴즈 추가