SQL활용 #5 - 부서/사원테이블 실습

--### 1. 부서테이블(DEPARTMENT)
--1. 테이블생성(제약조건X)
--부서번호 : NO NUMBER,
--부서명 : NAME(10이상)
--2. 제약조건
--기본키
--3. 지역컬럼 추가(AREA VARCHAR2(20))
--4. 지역컬럼의 체크값을 "부산", "서울", "대전" 만 가능하게
--5. 부서명의 길이를 30으로 변경
--6. 부서명에 NOT_NULL 추가
--7. 부서명에 UNIQUE 제약조건 설정 UQ_DEPT_NAME

--1. 테이블생성(제약조건X)
CREATE TABLE DEPARTMENT (
    NO NUMBER,
    NAME VARCHAR2(3)        
);

--2. 제약조건
--기본키
ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPT_NO PRIMARY KEY(NO);

--3. 지역컬럼 추가(AREA VARCHAR2(20))
ALTER TABLE DEPARTMENT ADD AREA VARCHAR2(20);

--4. 지역컬럼의 체크값을 "부산", "서울", "대전" 만 가능하게
ALTER TABLE DEPARTMENT ADD CONSTRAINT CK_DEPT_AREA CHECK(AREA IN ('부산', '서울', '대전'));

--5. 부서명의 길이를 30으로 변경
ALTER TABLE DEPARTMENT MODIFY NAME VARCHAR2(30);

--6. 부서명에 NOT_NULL 추가
ALTER TABLE DEPARTMENT MODIFY NAME NOT NULL;

--7. 부서명에 UNIQUE 제약조건 설정 UQ_DEPT_NAME
ALTER TABLE DEPARTMENT ADD CONSTRAINT UQ_DEPT_NAME UNIQUE(NAME);

-----------------------------------------
--### 2. 회사원테이블(EMPLOYEE)
--1. 테이블 생성
--사원번호 : NO NUMBER,
--이름 : NAME,
--부서번호 : DEPTNO (FK),
--상사번호 : PEMPNO,
--직급 : POSITION VARCHAR2(10), (CK - "대리", "과장", "부장", "차장")만 가능
--급여 : PAY NUMBER,
--등록일 : REGDATE DATE

--2. 부서테이블의 부서번호를 회사원 테이블의 부서번호와 연결

-- 테이블 삭제
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

--1. 테이블 생성
CREATE TABLE EMPLOYEE (
    -- PK 바로 설정 가능
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    DEPTNO NUMBER,
    PEMPNO NUMBER,
    POSITION VARCHAR2(10),
    PAY NUMBER,
    REGDATE DATE,
    --CONSTRAINT PK_EMPL_NO PRIMARY KEY(NO),
    CONSTRAINT CK_EMP_POSITION CHECK(POSITION IN ('대리', '과장', '부장', '차장'))    
);

--2. 부서테이블의 부서번호를 회사원 테이블의 부서번호와 연결
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(NO);

DELETE FROM EMPLOYEE;
DELETE FROM DEPARTMENT;
-----------------------------------------
-----------------------------------------

-- 부서테이블
SELECT * FROM DEPARTMENT;

INSERT INTO DEPARTMENT( NO, NAME, AREA) VALUES (101,'영업부', '');
INSERT INTO DEPARTMENT( NO, NAME, AREA) VALUES (102,'총무부', '');
INSERT INTO DEPARTMENT( NO, NAME, AREA) VALUES (103,'기획부', '');
INSERT INTO DEPARTMENT( NO, NAME, AREA) VALUES (104,'홍보부', '');

COMMIT; --또는 ROLLBACK


-- 사원테이블
SELECT * FROM EMPLOYEE;

-- 시퀀스 생성
CREATE SEQUENCE SEQ_EMP_NO
    START WITH 1001 INCREMENT BY 1 NOMAXVALUE NOCACHE;
    
-- 번호, 이름, 부서번호(101~104), 상사번호, 직급, 급여, 등록일)    
INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '김형우', 101, NULL, '부장', 1000, CURRENT_DATE);

INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '이응재', 102, 1001, '과장', 320, CURRENT_DATE);
    
INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '조용근', 102, 1001, '과장', 300, CURRENT_DATE);    

INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '이창민', 103, 1002, '차장', 270, CURRENT_DATE);
    
INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '윤춘식', 103, 1002, '차장', 260, CURRENT_DATE);   
    
INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '정다송', 103, 1002, '차장', 220, CURRENT_DATE);     

INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '김남훈', 104, 1003, '대리', 200, CURRENT_DATE);    

INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '김지훈', 104, 1003, '대리', 190, CURRENT_DATE);

INSERT INTO EMPLOYEE (NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
    VALUES(SEQ_EMP_NO.NEXTVAL, '공성욱', 104, 1003, '대리', 180, CURRENT_DATE);
    
COMMIT; --또는 ROLLBACK   
-------------------------------------------------------------------------------
-- 부서테이블의 영업부는 지역을 대전으로, 총무부는 서울, 나머지는 부산으로 설정

-- 사원테이블에서 번호, 이름, 직급을 번호순으로 내림차순하여 조회

-- 사원테이블에서 급여가 0~200이면 세금이 5%, 201~300이면 10%, 나머지 15%
-- (번호, 이름, 급여, 세금)

-- 사원테이블에서 영업부와 총무부만 이름으로 오름차순 조회

-- 사원테이블과 부서테이블을 INNER JOIN하여 조회
-- (번호, 이름, 급여, 부서명, 지역)
-------------------------------------------------------------------------------
DELETE FROM 

-- 부서테이블의 영업부는 지역을 대전으로, 총무부는 서울, 나머지는 부산으로 설정
UPDATE DEPARTMENT SET AREA =
    CASE 
--        WHEN (NAME = '영업부') THEN '대전'
--        WHEN (NAME = '총무부') THEN '서울'
        WHEN (NO = 101) THEN '대전'
        WHEN (NO = 102) THEN '서울'
        ELSE '부산'
    END;    
SELECT * FROM DEPARTMENT;    
COMMIT; --또는 ROLLBACK

-- 사원테이블에서 번호, 이름, 직급을 번호순으로 내림차순하여 조회
SELECT NO, NAME, POSITION FROM EMPLOYEE;
SELECT NO 번호, NAME 이름, POSITION 직급 FROM EMPLOYEE ORDER BY NO DESC;

SELECT * FROM EMPLOYEE;
COMMIT; --또는 ROLLBACK

-- 사원테이블에서 급여가 0~200이면 세금이 5%, 201~300이면 10%, 나머지 15%
-- (번호, 이름, 급여, 세금)
---- 1. TEX 항목 생성
--ALTER TABLE EMPLOYEE ADD TEX NUMBER;
--
---- 2. TEX항목 수정
--UPDATE EMPLOYEE SET TEX =
--    CASE
--        WHEN (PAY >=0 AND PAY <= 200) THEN PAY*0.05
--        WHEN (PAY >=201 AND PAY <= 300) THEN PAY*0.1
--        ELSE PAY*0.15
--    END;    
--SELECT NO, NAME, PAY, TEX FROM EMPLOYEE;

-- 선생님꺼
SELECT NO 번호, NAME 이름, PAY 급여, 
CASE 
    WHEN (PAY >=0 AND PAY<=200) THEN PAY*0.05 
    WHEN (PAY >=201 AND PAY<=300) THEN PAY*0.1 
    ELSE PAY*0.15
END 세금 
FROM EMPLOYEE;

SELECT * FROM EMPLOYEE;
COMMIT; --또는 ROLLBACK

-- 사원테이블에서 영업부와 총무부만 이름으로 오름차순 조회
SELECT * FROM EMPLOYEE WHERE DEPTNO IN (101, 102) ORDER BY NAME ASC;

SELECT * FROM EMPLOYEE;
COMMIT; --또는 ROLLBACK
-- 사원테이블과 부서테이블을 INNER JOIN하여 조회
-- (번호, 이름, 급여, 부서명, 지역)    
SELECT 
    EMPLOYEE.NO 사원번호, EMPLOYEE.NAME 이름, EMPLOYEE.PAY 급여, DEPARTMENT.NAME 부서명, DEPARTMENT.AREA 지역 
    FROM 
        EMPLOYEE, DEPARTMENT 
    WHERE EMPLOYEE.DEPTNO = DEPARTMENT.NO;    
    
-- 내장함수 ( CURRENT_DATE, TO_CHAR )
SELECT
    NO 번호, NAME 이름,
    PAY 급여, TO_CHAR(PAY, '999,999,999') 급여1,
    REGDATE 날짜, TO_CHAR(REGDATE, 'YYYY"년 "MM"월 "DD"일" HH24:MI:SS') 날짜1
FROM 
    EMPLOYEE;