20220325 시험용

(●'◡'●)

• 계정생성(아이디: USER01, 암호: 1111)

CREATE USER USER210 IDENTIFIED BY "1111";

• 권한부여

GRANT CONNECT, RESOURCE, DBA TO USER210;

• 계정삭제, 권한삭제

DROP USER USER210 CASCADE;

---

• 부서테이블 생성

CREATE TABLE DEPT01(
	DEPTNO NUMBER,
	DEPTNAME VARCHAR2(12)
);

• 지역컬럼 추가(DEPTAREA VARCHAR2(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);

---

• 사원테이블 생성

CREATE TABLE EMP01 (
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    DEPTNO NUMBER,
    PEMPNO NUMBER,
    POSITION VARCHAR2(10),
    PAY NUMBER,
    REGDATE DATE,
    CONSTRAINT CK_EMP01_POSITION CHECK (POSITION IN ('대리', '과장', '부장', '차장')),
    CONSTRAINT FK_EMP01_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT01(DEPTNO)
);

• 외래키 따로 빼내서 쓰는 법

ALTER TABLE EMP01 ADD CONSTRAINT 
FK_EMP01_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT01(DEPTNO);

• 인덱스 생성

CREATE INDEX IDX_EMP01_NAME ON EMP01(NAME);

• 시퀀스 생성

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, '홍보부', '');

• 사원에 자료 추가

INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP01_NO.NEXTVAL, '김개똥', 101, 1001, '부장', 2600000, CURRENT_DATE);
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP01_NO.NEXTVAL, '김용기', 102, 1002, '차장', 2200000, CURRENT_DATE);
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP01_NO.NEXTVAL, '김희망', 103, 1003, '부장', 3100000, CURRENT_DATE);
INSERT INTO EMP01(NO, NAME, DEPTNO, PEMPNO, POSITION, PAY, REGDATE)
VALUES(SEQ_EMP01_NO.NEXTVAL, '김긍지', 104, 1004, '과장', 2900000, CURRENT_DATE);

---

• 부서테이블의 영업부는 대전으로 총무부는 서울 나머지는 부산

UPDATE DEPT01 SET DEPTAREA =
    CASE 
        WHEN (NO=101) THEN '대전'
        WHEN (NO=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.NO = 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
	D.DEPTNAME 부서명, AVG(E.PAY) 급여평균
FROM DEPT01 D, EMP01 E WHERE D.DEPTNO=E.DEPTNO GROUP BY D.DEPTNAME;

• 부서별 인원수

SELECT DEPTNO, COUNT(*) FROM DEPT01 GROUP BY(DEPTNO);

• 부서별 급여 평균이 200 이상인

SELECT 
    D.DEPTNO 부서명, AVG(E.PAY) 급여평균
FROM DEPT01 D, EMP01 E 
WHERE D.DEPTNO=E.DEPTNO GROUP BY D.DEPTNO HAVING AVG(E.PAY) >= 200;

---

• 시퀀스값을 가져오는 함수
• FUNC_BOARD3_SEQ => 현재의 시퀀스 값을 가져오는 함수

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;

---

• 뷰 만들기

CREATE OR REPLACE VIEW BOARD33 AS
SELECT
    NO, TITLE, WRITER, HIT, REGDATE,
    ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN
FROM
    BOARD3;