03/24 TEST

-- 계정 생성(아이디: USER01, 암호: 1111)
CREATE USER USER219 IDENTIFIED BY "1111";
-- 권한 부여
GRANT CONNECT, RESOURCE, DBA TO USER219;
-- 계정삭제, 권한삭제
DROP USER USER219 CASCADE;

-- 부서 테이블 생성(DEPT01), 제약조건 설정x
-- 부서번호 : deptno NUMBER
-- 부서명  : deptname VARCHAR2(12)

CREATE TABLE DEPT01(
    deptno NUMBER,
    deptname VARCHAR2(12)
);

-- 지역컬럼 추가 추가ADD, 변경 MODIFY
-- deptarea VARCHAR(15)
ALTER TABLE DEPT01 ADD deptarea VARCHAR2(15);
-- 기본키 제약조건 (PK_DEPT01_NO)
ALTER TABLE DEPT01 ADD CONSTRAINT PK_DEPT01_NO PRIMARY KEY(deptno);
-- 부서명 not null 컬럼변경
ALTER TABLE DEPT01 MODIFY deptname NOT NULL;
-- 부서명 고유 제약조건 (UQ_DEPT01_NAME)
ALTER TABLE DEPT01 ADD CONSTRAINT UQ_DEPT01_NAME UNIQUE(deptname);

-- 사원테이블(EMP01) (제약조건은 기본키, 체크만)
-- 사원번호 : no NUMBER (PK),
-- 이름 : name
-- 부서번호 :  deptno (FK)
--- 상사번호 : pempno
-- 직급 : position VARCHAR(10), (CK - "대리", "과장", "부장", "차장") 만 가능
-- 급여 : pay NUMBER
-- 등록일 : regdate DATE
CREATE TABLE EMP01(
    no NUMBER PRIMARY KEY,
    name VARCHAR2(20),    
    deptno NUMBER,
    pempno NUMBER,
    position VARCHAR(10),
    pay NUMBER,
    regdate DATE ,
    CONSTRAINT CK_EMP01_POSITION CHECK ( POSITION IN ('대리', '과장', '부장', '차장') )        
);

-- 외래키 제약조건 (FK_EMP01_DEPTNO)
-- 부서테이블의 기본키를 사원테이블의 deptno에 외래키 제약
ALTER TABLE EMP01 
ADD CONSTRAINT FK_EMP01_DEPTNO FOREIGN KEY(deptno)REFERENCES DEPT01(deptno);

-- 인덱스 (IDX_PMP01_NAME) 생성 => 건색 속도 개선
CREATE INDEX IDX_PMP01_NAME ON emp01(name);

-- 시퀀스 (SEQ_EMP01_NO, 시작값1, 증가값1)
CREATE SEQUENCE SEQ_EMP01_NO START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 부서추가하기
INSERT INTO DEPT01( deptno, deptname, deptAREA) VALUES (101,'영업부', '');
INSERT INTO DEPT01( deptno, deptname, deptAREA) VALUES (102,'총무부', '');
INSERT INTO DEPT01( deptno, deptname, deptAREA) VALUES (103,'기획부', '');
INSERT INTO DEPT01( deptno, deptname, deptAREA) VALUES (104,'홍보부', '');
COMMIT; --또는 ROLLBACK


-- 번호, 이름, 부서번호(101 ~ 104), 상사번호, 직급, 급여, 등록일)
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES (SEQ_EMP01_NO.NEXTVAL, '가나다', 101, null, '부장', 3000000, CURRENT_DATE);
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES (SEQ_EMP01_NO.NEXTVAL, '나다라', 102, 1001, '과장', 2300000, CURRENT_DATE);
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES (SEQ_EMP01_NO.NEXTVAL, '나다라', 103, 1001, '과장', 2600000, CURRENT_DATE);
COMMIT;

SELECT * FROM EMP01;
SELECT * FROM DEPT01;


-- 부서테이블의 영업부는 지역을 대전으로, 총무부는 서울, 나머지는 부산으로 설정
UPDATE DEPT01 SET DEPTAREA =
    CASE 
        WHEN (DEPTNO=101) THEN '대전'
        WHEN (DEPTNO=102) THEN '서울'
        ELSE '부산'
    END;
COMMIT;


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

-- 사원테이블에서 급여가 0~200이면 세금이 5%, 201~300 10%, 나머지는 15%로 표시
-- (번호, 이름, 급여, 세금)
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 EMP01;


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

-- 사원테이블과 부서 테이블을 inner join하여 조회 
-- (번호, 이름, 급여, 부서명, 지역)
SELECT E.NO 번호, E.NAME 이름, E.PAY 급여,D.DEPTNAME 부서명, D.DEPTAREA 지역 FROM DEPT01 D, EMP01 E WHERE  D.DEPTNO = E.DEPTNO; 

-- 내장함수 ( 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 
    EMP01;

-- 부서별 급여 평균을 구하기(부서명, 평균)
SELECT DEPTNO, AVG(PAY) FROM EMP01 GROUP BY(deptno);
SELECT DEPTNAME, AVG(PAY) FROM DEPT01, EMP01 WHERE DEPT01.DEPTNO=EMP01.DEPTNO GROUP BY(DEPTNAME);

-- 부서별 인원수 구하기
SELECT DEPTNO, COUNT(*) FROM DEPT01 GROUP BY(deptno);
SELECT DEPTNAME, COUNT(*) FROM DEPT01, EMP01 WHERE DEPT01.DEPTNO=EMP01.DEPTNO GROUP BY(DEPTNAME);

-- 부서별 급여 평균이 200이상인 
SELECT DEPTNO, AVG(PAY) FROM DEPT01 GROUP BY(deptno) HAVING AVG(PAY) > = 200;
SELECT DEPTNAME, AVG(PAY) FROM DEPT01, EMP01 
    WHERE DEPT01.DEPTNO=EMP01.DEPTNO GROUP BY(DEPTNAME)HAVING AVG(PAY) > = 200;

-- 시퀀스값을 가져오는 함수
CREATE OR REPLACE FUNCTION FUNC_BOARD3_SEQ RETURN NUMBER
IS
    V_SEQ NUMBER := 0;
BEGIN
    -- 시퀀스의 다음 숫자 가져오기
    SELECT SEQ_BOARD3_NO.NEXTVAL INTO V_SEQ FROM DUAL;
    RETURN V_SEQ;
END;
/

SELECT FUNC_BOARD3_SEQ FROM DUAL;
-- 테이블 조회 후 view로 만들기
CREATE  VIEW EMP01_VIEW AS SELECT ....;

CREATE OR REPLACE  VIEW EMP01_VIEW AS SELECT * FROM EMP01;

SELECT * FROM EMP01_VIEW;




SELECT * FROM EMP01;
SELECT * FROM DEPT01;