-- SELECT
SELECT * 혹은 컬럼
FROM 테이블 또는 VIEW
WHERE 조건
ORDER BY 컬럼;

어디서 가져올 것인가? FROM
무얼을 가져올 것인가? SELECT
어떻게 가져올 것인가? WHERE

-- SELECT
-- 질문 : 사원 테이블에서 급여가 5000이 넘는 사원번호와 사원명을 조회한다.
DESC employees;

SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000;

-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
-- 94p
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'IT_PROG'
ORDER BY employee_id;

-- p.94
-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

-- 급여가 5000 이상이거나 job_id가 'IT_PROG'인 사원 (OR)
-- 합집합
SELECT 
    employee_id
    , emp_name
    , salary
    , job_id
FROM employees
WHERE salary > 5000
    OR job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

SELECT * FROM employees;

-- INSERT 
CREATE TABLE ex3_1 (
    col1 VARCHAR2(10), 
    col2 NUMBER, 
    col3 DATE
);

INSERT INTO ex3_1(col1, col2, col3) 
VALUES ('ABC', 10, SYSDATE);

-- 컬럼 순서를 바꿔도 큰 문제가 안됨
INSERT INTO ex3_1(col3, col1, col2)
VALUES (SYSDATE, 'DEF', 20);

SELECT * FROM ex3_1;

-- 타입 맞추지 않음 오류 발생
INSERT INTO ex3_1(col1, col2, col3)
VALUES ('ABC', 10, 30);

-- p.97
-- 컬럼명 생략
INSERT INTO ex3_1
VALUES ('GHI', 10, SYSDATE);

INSERT INTO ex3_1 (col1, col2)
VALUES('GHI', 20);

SELECT * FROM ex3_1;

-- p.98
CREATE TABLE ex3_2(
    emp_id      NUMBER
    , emp_name  VARCHAR2(100)
);

-- 실무에서 많이 쓰임
INSERT INTO ex3_2(emp_id, emp_name) 
SELECT
    employee_id
    , emp_name
FROM employees
WHERE salary > 5000;

SELECT * FROM ex3_2;

UPDATE 테이블명
SET 컬럼1 = 변경값1, 컬럼2 = 변경값2
WHERE 조건;

SELECT * FROM ex3_1;

-- col2 모두 50으로 변경한다.
UPDATE ex3_1
SET col2 = 50;

SELECT * FROM ex3_1;

UPDATE ex3_2
SET EMP_NAME = 10 -- Michael Hartstein
WHERE EMP_ID = 201;

SELECT * FROM ex3_2;

-- MERGE
-- 조건 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT
-- 있으면 UPDATE

CREATE TABLE ex3_3(
    employee_id     NUMBER
    , bonum_amt     NUMBER DEFAULT 0
);

SELECT * FROM SALES;
DESC SALES;

-- 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;
-- group by를 통해 중복 제거

SELECT * FROM ex3_3 ORDER BY employee_id;

-- (1) 관리자 사번(manager_id)이 146번인 사원을 찾는다.
-- (2) ex3_3 테이블에 있는 사원의 사번과 일치하면
--     보너스 금액에 자신의 급여의 1%를 보너스로 갱신
-- (3) ex3_3 테이블에 있는 사원의 사번과 일치하지 않으면
--      (1)의 결과의 사원을 신규로 입력 (이 때 보너스 금액은 급여의 0.1%
-- (4) 이 때 급여가 8000미만인 사원만 처리해보자.

-- 서브쿼리
SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id IN (SELECT employee_id FROM ex3_3);

-- 관리자 사번이 146번인 사원은 161번 사원 한 명이므로
-- ex3_3 테이블에서 161인 건의 보너스 금액은 7,000 * 0.01, 즉 70으로 갱신

SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM ex3_3)
    AND manager_id = 146;

MERGE INTO ex3_3 d 
    USING (SELECT
                employee_id
                , salary 
                , manager_id
           FROM employees
           WHERE manager_id = 146) b 
        ON (d.employee_id = b.employee_id)
WHEN MATCHED THEN 
    UPDATE SET d.bonus_amt = d.bonus_amt + b.salary * 0.01 
WHEN NOT MATCHED THEN 
    INSERT (d.employee_id, d.bonus_amt) VALUES (b.employee_id, b.salary * 0.001)
    WHERE (b.salary < 8000);
    
SELECT * FROM ex3_3 ORDER BY employee_id;

-- DELETE 문
-- 106p
DELETE ex3_3;
SELECT * FROM ex3_3;

-- Commit Rollback Truncate
-- Commit : 변경한 데이터를 DB에 마지막으로 반영
-- ROLLBACK : 반대로 변경한 데이터를 변경하기 이전 상태로 되돌림
CREATE TABLE ex3_4 (
    employee_id NUMBER 
);
    
INSERT INTO ex3_4 VALUES (100);

SELECT * FROM ex3_4;

commit;
rollback;

-- TRUNCATE 문
-- 한번 실행시, 데이터 바로 삭제, ROLLBACK 적용 안됨
-- DELETE문은 데이터 삭제 후, COMMIT 필요 / ROLLBACK 데이터가 삭제되기 전의 복구 불가

SELECT * FROM ex3_4;
TRUNCATE TABLE ex3_4;

-- 60P
-- not null
-- 제약 조건
CREATE TABLE ex2_6(
    COL_NULL        VARCHAR2(10)
    , COL_NOT_NULL  VARCHAR2(10) NOT NULL
);

INSERT INTO ex2_6 VALUES ('AA', '');
INSERT INTO ex2_6 VALUES ('AA', 'BB');

SELECT * FROM ex2_6;

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_6';

-- UNIQUE
-- 중복값 허용 안 함
CREATE TABLE ex2_7 (
    COL_UNIQUE_NULL VARCHAR2(10) UNIQUE
    , COL_UNIQUE_NULL VARCHAR2(10) UNIQUE NOT NULL
    , COL_UNIQUE VARCHAR(10)
    , CONSTRAINTS unique_nm1 UNIQUE (COL_UNIQUE)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_7';
 
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUEs('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('', 'BB', 'BB');
 INSERT INTO ex2_7 VALUES('', 'CC', 'CC');
 SELECT * FROM ex2_7;

-- 기본키 ( 63P)
CREATE TABLE ex2_8(
    COL VARCHAR2(10) PRIMARY KEY
    , COL2 VARCHAR2(10)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_8';
 
INSERT INTO ex2_8 VALUES('','AA');
-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.

-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.
INSERT INTO ex2_8 VALUES('AA','AA');
SELECT * FROM ex2_8;

INSERT INTO ex2_8 VALUES('AA','AA');
-- ORA-00001: 무결성 제약 조건(ORA_USER.SYS_C007484)에 위배됩니다
-- 값이 같은 데이터를 입력하여 생기는 오류이다. 다른 데이터를 입력해야 한다.

-- 90p 테이블 1, 2, 3번 생성
CREATE TABLE ORDERS (
    ORDER_ID              NUMBER(12, 0)
    , ORDER_DATE          DATE
    , ORDER_MODE          VARCHAR2(8 BYTE)
    , CUSTOMER_ID         NUMBER(6, 0)
    , ORDER_STATUS        NUMBER(2, 0)
    , ORDER_TOTAL         NUMBER(8, 2) DEFAULT 0
    , SALES_REP_ID        NUMBER(6, 0)
    , PROMOTION_ID        NUMBER(6, 0)
    , CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
    , CONSTRAINT CK_ORDER_MODE CHECK (ORDER_MODE in ('direct', 'online'))
);

CREATE TABLE ORDER_ITEMS (
           ORDER_ID	    NUMBER(12,0),
           LINE_ITEM_ID NUMBER(3,0) ,
           ORDER_MODE	  VARCHAR2(8 BYTE),
           PRODUCT_ID   NUMBER(3,0), 
           UNIT_PRICE   NUMBER(8,2) DEFAULT 0, 
           QUANTITY     NUMBER(8,0) DEFAULT 0,
           CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY (ORDER_ID, LINE_ITEM_ID)
); 

CREATE TABLE PROMOTIONS (
           PROMO_ID	    NUMBER(12,0),
           PROMO_NAME	  VARCHAR2(8 BYTE),
           CONSTRAINT PK_PROMOTIONS PRIMARY KEY (PROMO_ID)
); 

-- CHECK
CREATE TABLE ex2_9 (
    num1     NUMBER 
        CONSTRAINTS check1 CHECK ( num1 BETWEEN 1 AND 9),
    gender   VARCHAR2(10) 
        CONSTRAINTS check2 CHECK ( gender IN ('MALE', 'FEMALE'))        
); 

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_9';
 
INSERT INTO ex2_9 VALUES (10, 'MAN');
INSERT INTO ex2_9 VALUES (5, 'FEMALE');

-- DEFAULT 
-- PL/SQL 사용하면 편하게 해결 가능
DROP TABLE ex2_10;

CREATE TABLE ex2_10 (
   Col1        VARCHAR2(10) NOT NULL, 
   Col2        VARCHAR2(10) NULL, 
   Create_date DATE DEFAULT SYSDATE
);
   
-- Col1 Col2 사용자가 입력
-- Create_Date DB에서 자동으로 입력
INSERT INTO ex2_10 (col1, col2) VALUES ('AA', 'BB');
SELECT * FROM ex2_10; 

-- 테이블 변경
-- 69p (1) 컬럼명 변경

ALTER TABLE ex2_10 RENAME COLUMN Col1 TO Col11;
SELECT * FROM ex2_10;

DESC ex2_10;

-- (2) 컬럼 타입 변경
-- 컬럼 타입 변경 (VARCHAR2(10) ~ VARCHAR2(30))으로 변경
ALTER TABLE ex2_10 MODIFY Col2 VARCHAR2(30);
DESC ex2_10;

-- (3) col3 NUMBER 타입으로 신규 생성
ALTER TABLE ex2_10 ADD Col3 NUMBER;
DESC ex2_10;

-- (4) 컬럼삭제
ALTER TABLE ex2_10 DROP COLUMN Col3;
DESC ex2_10;

-- 제약조건 추가
ALTER TABLE ex2_10 ADD CONSTRAINTS pk_ex2_10 PRIMARY KEY (col11);

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';

-- 제약조건 삭제
 ALTER TABLE ex2_10 DROP CONSTRAINTS pk_ex2_10;
 
 -- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';

SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id -- 부서명 컬럼
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;

CREATE OR REPLACE VIEW emp_dept_v1 AS 
SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;

-- 인덱스 생성
-- 75p
-- 추후 공부해야 할 내용 : 인텍스 내부 구조에 따른 분류
---- (초중급 레벨) B-Tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스
---- DB 성능
-- 인덱스 생성
-- col11 값에 중복 값을 허용하지 않는다.
-- 인덱스 생성 시, user_indexes 시스템 뷰에서 내역 확인
CREATE UNIQUE INDEX ex2_10_ix011 
ON ex2_10(col11);

SELECT index_name, index_type, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EX2_10';

CREATE TABLE ex2_1 (
COLUMN1     CHAR(10)
, COLUMN2     VARCHAR2(10)
, COLUMN3     NVARCHAR2(10)
, COLUMN4     NUMBER
);

CREATE TABLE ex2_1 (
COLUMN1 CHAR(10)
, COLUMN2 VARCHAR2(10)
, COLUMN3 NVARCHAR2(10)
, COLUMN4 NUMBER
);

-- 데이터 추가
INSERT INTO ex2_1 (COLUMN1, COLUMN2) VALUES('abc', 'abc')

-- 데이터 확인
SELECT
COLUMN1
, LENGTH(COLUMN1) as len1
, COLUMN2
, LENGTH(COLUMN2) as len2
FROM ex2_1;

-- 새로운 테이블 생성
CREATE TABLE ex2_2(
COLUMN1 VARCHAR2(3)
, COLUMN2 VARCHAR2(3 byte)
, COLUMN3 VARCHAR2(3 char)
);

-- 데이터 추가
INSERT INTO ex2_2 VALUES('abc', 'abc', 'abc');

-- 조회
SELECT
COLUMN1
, LENGTH(COLUMN1) As len1
, COLUMN2
, LENGTH(COLUMN2) As len2
, COLUMN3
, LENGTH(COLUMN3) As len3
FROM ex2_2;

-- 한글 데이터 삽입
-- 에러 발생
INSERT INTO ex2_2 VALUES('홍길동', '홍길동', '홍길동');

- 데이터 조회
SELECT
COLUMN3
, LENGTH(COLUMN3) As len3
, LENGTHB(COLUMN3) As bytelen
FROM ex2_2;

- 숫자 데이터 삽입
CREATE TABLE ex2_3(
COL_INT INTEGER
, COL_DEC DECIMAL
, COL_NUM NUMBER
);

SELECT
column_id
, column_name
, data_type
, data_length
FROM user_tab_cols
WHERE table_name = 'EX2_3'
ORDER BY column_id;

-- p57
CREATE TABLE ex2_4 (
COL_FLOT1 FLOAT(32),
COL_FLOT2 FLOAT
);

INSERT INTO ex2_4 (col_flot1, col_flot2) VALUES (1234567891234, 1234567891234);

-- 조회
SELECT * FROM ex2_4;

-- p.58
CREATE TABLE ex2_5(
COL_DATE DATE
, COL_TIMESTAMP TIMESTAMP
);
INSERT INTO ex2_5 VALUES (SYSDATE, SYSTIMESTAMP);
SELECT * FROM ex2_5;

-- LOB 데이터 타입
-- Large object의 약자로 대용량 데이터 저장할 수 있는 데이터 타입
-- 비정형 데이터는 그 크기가 매우 큰데, 이런 데이터를 저장한다.