Oracle

1. 부서 테이블 생성

DEPTTBL(부서) 테이블 (제약조건 없음)
DEPTNO (부서번호), 숫자
DNAME (부서명) 가변 길이(7자) 문자 = VARCHAR2(7)

CREATE TABLE DEPTTBL1(
    DEPTNO1 NUMBER, -- 길이가 30인 문자
    DNAME1 VARCHAR2(7),
    
    CONSTRAINT PK_DEPT_NO1 PRIMARY KEY(DEPTNO1)
    );

2. DEPTNO 기본키 설정

테이블 생성시 기본기 제약조건 설정완료

ALTER TABLE DEPTTBL1 ADD CONSTRAINT PK_DEPT_NO1 PRIMARY KEY(DEPTNO1);

3. ADDRESS 컬럼 타입 변경

주소 가변길이 30자

ALTER TABLE DEPTTBL1 ADD ADDRESS VARCHAR2(30);

4. DNAME 컬럼 타입 변경/NOT NULL 설정

부서명(DNAME) VARCHAR2=30, not null 설정

ALTER TABLE DEPTTBL1 MODIFY DNAME1 NOT NULL;
ALTER TABLE DEPTTBL1 MODIFY DNAME1 VARCHAR2(30);

5. 부서 등록

101 총무부 서울
102 영업부 부산
103 기획부 대구
104 홍보부 서울

SELECT DT.* FROM DEPTTBL1 DT;

INSERT ALL
    INTO DEPTTBL1 VALUES(101,'총무부','서울')
    INTO DEPTTBL1 VALUES(102,'영업부','부산')
    INTO DEPTTBL1 VALUES(103,'기획부','대구')
    INTO DEPTTBL1 VALUES(104,'홍보부','서울')
SELECT * FROM DUAL;
COMMIT;

6. 사원 테이블 생성

emptbl(사원) 테이블 생성

• name (사원이름) = 가변길이(10자),문자
• empno (사원코드) = 숫자, 기본키 제약 조건
• deptno (부서코드) = 숫자, not null 제약 조건
• pempno (상사코드) = 숫자
• pay (급여) = 숫자, not null 제약 조건
• regdate (등록일) = timestamp
• position (직급) = 가변길이(10자),문자 “사원, 대리, 과장” 값만 가능

CREATE TABLE EMPTBL1(
    NAME VARCHAR2(10),
    EMPNO NUMBER, --PK
    DEPTNO NUMBER NOT NULL,
    PEMPNO NUMBER,
    PAY NUMBER NOT NULL,
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE,
    POSITION VARCHAR2(10),
    
    CONSTRAINT PK_EMP_NO1 PRIMARY KEY(EMPNO),
    CONSTRAINT EMP_POSITION_CK1 CHECK (POSITION IN('사원','대리','과장'))
    );

*테이블 삭제하는 경우

DROP TABLE ENPTBL1 CASCADE CONSTRAINTS;

7. 시퀀스 생성

시퀀스 생성 (시퀀스명 : SEQ_EMPTBL_NO, 시작값 10001, 증가값 1)

CREATE SEQUENCE SEQ_EMPTBL1_NO START WITH 10001 INCREMENT BY 1 NOMAXVALUE NOCACHE;

8. 사원 등록

10명 사원 등록, 상사코드는 null로 입력

INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('김사원', SEQ_EMPTBL1_NO.NEXTVAL, 101, NULL, 1800000, CURRENT_DATE, '사원');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('이사원', SEQ_EMPTBL1_NO.NEXTVAL, 102, NULL, 2100000, CURRENT_DATE, '사원');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('박사원', SEQ_EMPTBL1_NO.NEXTVAL, 103, NULL, 1900000, CURRENT_DATE, '사원');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('최사원', SEQ_EMPTBL1_NO.NEXTVAL, 104, NULL, 2200000, CURRENT_DATE, '사원');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('김대리', SEQ_EMPTBL1_NO.NEXTVAL, 103, NULL, 2500000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('이대리', SEQ_EMPTBL1_NO.NEXTVAL, 102, NULL, 2400000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('최대리', SEQ_EMPTBL1_NO.NEXTVAL, 101, NULL, 2300000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('정대리', SEQ_EMPTBL1_NO.NEXTVAL, 104,  NULL, 2600000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('김과장', SEQ_EMPTBL1_NO.NEXTVAL, 102, NULL, 3000000, CURRENT_DATE, '과장');
INSERT INTO EMPTBL1(NAME, EMPNO, DEPTNO, PEMPNO, PAY, REGDATE, POSITION)
    VALUES('최과장', SEQ_EMPTBL1_NO.NEXTVAL, 103, NULL, 3200000, CURRENT_DATE, '과장');

ROLLBACK;
SELECT * FROM EMPTBL1;
COMMIT;

9. 부서테이블의 컬럼 변경

DEPTTBL의 ADDRESS를 영업부는 "대구" 나머지는 "부산" 으로 변경

UPDATE DEPTTBL1 SET ADDRESS =
    CASE 
        WHEN DEPTNO1 = 102 THEN '대구'
        ELSE '부산'
    END;
COMMIT;

10. 사원테이블 조건부 삭제

사원테이블의 부서번호가 103인 항목 삭제
DELETE FROM 테이블명 WHERE 조건;

DELETE FROM EMPTBL1 WHERE DEPTNO = 103;
ROLLBACK;

11. 사원테이블 조건부 조회 1

사원테이블의 영업부 직원과 총무부 직원의 이름, 급여, 직급을 사원번호 내림차순으로 조회
SELECT * FROM 테이블명 WHERE 조건 OR 조건 ORDER BY 컬럼 ASC|DESC;

SELECT 
	E1.EMPNO, E1.NAME, E1.PAY, E1.POSITION 
FROM 
	EMPTBL1 E1 
WHERE 
	DEPTNO = 101 OR DEPTNO = 102 
ORDER BY 
	EMPNO DESC;

12. 사원테이블 조건부 조회 2

사원테이블의 E.PAY(급여)에서 세금을 급여에 따라 조회 = CASE 사용
➡️ 사원번호, 사원명, 급여, 세금 출력

• 0~230 이면 5%
• 231~300이면 10%
• 301~400 15%
• 400이상은 20%

SELECT E.EMPNO, E.NAME, E.PAY, 
CASE
    WHEN(E.PAY >= 0 AND E.PAY<=2300000) THEN E.PAY*0.05
    WHEN(E.PAY >= 2310000 AND E.PAY<=3000000) THEN E.PAY*0.1
    WHEN(E.PAY >= 3010000 AND E.PAY<=4000000) THEN E.PAY*0.15
    WHEN(E.PAY >= 4010000) THEN E.PAY*0.2
END TAX
FROM EMPTBL1 E;

13. 사원테이블 조건부 조회 2

사원테이블의 부서별 평균 급여와 인원수 출력 = GROUP BY 사용
SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPTBL E GROUP BY E.DEPTNO;

SELECT E.DEPTNO, AVG(E.PAY), COUNT(*) FROM EMPTBL1 E GROUP BY E.DEPTNO;

14. 사원테이블 조건부 조회 3

사원테이블의 부서별 평균급여 300이상인 사원 조회
➡️ 부서번호, 부서명, 평균급여 출력

--1. 부서별 평균급여 구하기
SELECT E.DEPTNO, AVG(E.PAY) FROM EMPTBL1 E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=2500000;
--2. 1에서 구한 데이터와 DEPTNO INNER JOIN
SELECT D.DNAME1, E.* FROM DEPTTBL1 D INNER JOIN ( 
    SELECT E.DEPTNO, AVG(E.PAY) FROM EMPTBL1 E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=2500000
) E 
ON D.DEPTNO1 = E.DEPTNO;

15. 사원테이블 조건부 조회 4

사원 직급별 총 급여, 평균 급여, 총 인원수 출력

SELECT 
	E.POSITION 직급, SUM(E.PAY)총급여, AVG(E.PAY) 평균급여, COUNT(*) 인원수 
FROM 
	EMPLOYEE E 
GROUP BY 
	E.POSITION;

SELECT E.POSITION, AVG(E.PAY), COUNT(*) FROM EMPTBL1 E GROUP BY E.POSITION;

16. 15번에서 조회한 내용을 기반으로 VIEW생성

VIEW 이름 = EMPTBL_VIEW
CREATE OR REPLACE VIEW 뷰이름 AS SELECT ....

CREATE OR REPLACE VIEW EMPTBL_VIEW AS
SELECT E.POSITION, AVG(E.PAY) AVG, COUNT(*) CNT FROM EMPTBL1 E GROUP BY E.POSITION;

SELECT EV.* FROM EMPTBL_VIEW EV;

17. 사원테이블 조건부 조회 5

사원 직급별 급여가 높은 순으로 rank()출력 = PARTITION 사용

SELECT 
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RNK,
    E.* 
FROM EMPTBL E;

인덱스 생성

-- 인덱스 표기 형식
CREATE INDEX 인덱스명 ON 테이블명(컬럼명));

함수 생성

시퀀스 값 반환 함수

-- 함수 표기 형식
CREATE OR REPLACE FUNCTION 함수명 RETURN 리턴타입 
IS
BEGIN
    RETURN 처리후 리턴할 값;
END;
/