금일부로 Oracle의 최종 평가를 진행하였다.

추후에도 Oracle은 많은 용도로 사용할 것 같으니, 필요할때마다 참고하기 위해 오늘 시험에 등장한 문제들을 정리해보도록 하겠다.

---

1. 테이블 생성하기

-- 1번 --------------------------------------------
-- 테이블 생성, 기본키 지정 및 입력항목 제한
CREATE TABLE emp03
(
  name     VARCHAR2(15),
  empno    NUMBER      ,
  deptno   NUMBER       NOT NULL,
  pempno   NUMBER      ,
  pay      NUMBER       NOT NULL,
  regdate  TIMESTAMP    DEFAULT CURRENT_DATE,
  position VARCHAR2(10),
  
  
  CONSTRAINT PKval_emp03 PRIMARY KEY (empno),
  CONSTRAINT Position_chk CHECK(position IN ('사원', '대리', '과장', '차장', '부장'))
);

2. 외래키 지정하기

-- 2번 --------------------------------------------
-- 외래키 지정
ALTER TABLE emp03
  ADD CONSTRAINT fK_emp03_depno
    FOREIGN KEY (deptno)
    REFERENCES dept03 (deptno);

3. 시퀀스 생성하기

-- 3번 --------------------------------------------
-- 시퀀스 생성, 1001부터 1씩 증가
CREATE SEQUENCE seq_emp03_empno 
        INCREMENT BY 1
        START WITH 1001;

4. 데이터 등록하기

-- 개별로 등록하기 (INSERT INTO)
INSERT INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(seq_emp03_empno.NEXTVAL, '가가가', 101, '부장', 450, 0);
COMMIT; -- 커밋 잊지말고 꼭 할것!
    
    
    
-- 한번에 여러개 등록하기 (INSERT ALL)
CREATE OR REPLACE FUNCTION func_seq_emp03_nextval RETURN NUMBER -- FUNCTION 생성
IS
BEGIN
    RETURN seq_emp03_empno.NEXTVAL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
/

INSERT ALL
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '라라라', 103, '과장', 410, 0)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '마마마', 101, '대리', 300, 1003)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '바바바', 103, '대리', 400, 1004)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '사사사', 102, '대리', 320, 1002)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '아아아', 102, '사원', 380, 1007)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '자자자', 103, '사원', 250, 1006)
 INTO emp03(empno, name, deptno, position, pay, pempno)
    VALUES(func_seq_emp03_nextval, '차차차', 101, '사원', 200, 1005)
    SELECT * FROM DUAL;
COMMIT;

5. 데이터 수정하기

-- 5번 --------------------------------------------
-- 데이터 수정
UPDATE emp03 SET name = '가나다' WHERE name = '가가가';

6. 컬럼 추가하기(CASE)

-- 6번 --------------------------------------------
--CASE 활용

SELECT
    e.name, e.pay,
    CASE
    WHEN(e.pay >= 0 AND 200 >= e.pay)THEN '5%'
    WHEN(e.pay >= 201 AND 300 >= e.pay)THEN '10%'
    WHEN(e.pay >= 301 AND 400 >= e.pay)THEN '15%'
    ELSE '20%' 
    END tax
FROM emp03 e;

7. GROUP BY로 데이터 분류하기

-- 7번 --------------------------------------------
-- GROUP BY 사용 (부서번호, 급여평균, 인원수 출력)

SELECT 
	deptno, 
    AVG(ROUND(pay), -2), 
    COUNT(*) cnt 
    FROM emp03_view GROUP BY deptno;

8. 테이블뷰 생성하기 (INNER JOIN 사용)

-- 8번 --------------------------------------------
-- 테이블뷰 생성하기
CREATE OR REPLACE VIEW emp03_view
AS
SELECT d.dname, e.* FROM dept03 d INNER JOIN emp03 e ON d.deptno = e.deptno;

9. 데이터 삭제하기

-- 9번 --------------------------------------------
-- 데이터 삭제하기
DELETE FROM emp03 WHERE deptno = 101;
COMMIT;

10. FUNCTION 생성하기

-- 10번 --------------------------------------------
-- FUNCTION 생성하기 (시퀀스 개별지정용도)
CREATE OR REPLACE FUNCTION func_emp03_nextval RETURN NUMBER
IS
BEGIN
    RETURN seq_emp03_empno.NEXTVAL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
/

추가진도

11. PARTITION BY

-- 판매 테이블 조회 
SELECT * FROM PURCHASE;

-- 페이지네이션(전체 데이터에서 cnt를 기준으로 숫자를 생성시킴)
SELECT P.*, ROW_NUMBER() OVER(ORDER BY cnt DESC, no ASC) rown FROM PURCHASE P ;

-- 전체 데이터에서 userid 기준으로 cnt가 큰 순으로 숫자
-- 사용자별로 숫자를 매김.
SELECT P.*, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY cnt DESC) rown FROM PURCHASE P ; -- (userid별 cnt(주문수량) 내림차순) userid별로 rown을 매김.
SELECT P.*, ROW_NUMBER() OVER(ORDER BY cnt DESC) rown FROM PURCHASE P ORDER BY USERID; -- (먼저 cnt별로 내림차순을 한 뒤, userid 순으로 다시 정렬.)전체값의 rown을 매김.
  
  -- 등수 매기기랑 비슷함 (같은값끼리는 같은등수)
    SELECT P.*, RANK() OVER(PARTITION BY userid ORDER BY cnt DESC) rown FROM PURCHASE P ;
    SELECT P.*, DENSE_RANK() OVER(PARTITION BY userid ORDER BY cnt DESC) rown FROM PURCHASE P ;

-- 문제) userid별 주문수량이 가장 큰 것 1개만 조회
SELECT * FROM (
            SELECT P.*, ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY CNT DESC) ROWN FROM PURCHASE P)           
WHERE ROWN=1;

---

그동안 블로그글을 너무 장황하고 보기 힘들게 써서 필요한 정보를 찾는데 많은 애를 먹었다.. 앞으로는 언제든지 참고하기 쉽도록 신경써서 포스팅해야할 것 같다.

금일 평가를 마무리로, 이제 그동안 배운 모든것들을 활용하여 미니 프로젝트를 진행하게 된다. 따라서 이번주간은 현재 제작하려는 미니프로젝트에 대한 글을 포스팅해보고자 한다.

이번주도 열심히 달려가보자!