ORACLE 실습 내용

-- DDL 정의어
-- 회원가입용 테이블(ID, Passwrod, Name, Age, Phone, Day)
CREATE TABLE usertbl(
    ID VARCHAR2(30) PRIMARY KEY,
    PASSWORD VARCHAR2(200),
    NAME VARCHAR2(30),
    AGE NUMBER,
    PHONE VARCHAR2(30),
    DAY DATE
);
-- VARCHAR는 가변 길이
-- PRIMARY KEY는 기본값이며 중복 안 됨

-- DML 조작어
-- INSERT, UPDATE, DELETE, SELECT
-- 형식 : INSERT INTO 테이블명(컬렴명) VALUES(값들)
INSERT INTO usertbl(ID, PASSWORD, NAME, AGE, PHONE, DAY)
VALUES('test', '1234', '테스터', '20', '010-1234-5678', CURRENT_DATE);
-- COMMIT은 적용
COMMIT;
-- ROLLBACK은 되돌리기
ROLLBACK;

-- INSERT : 하나의 행 데이터 추가
INSERT INTO usertbl(ID, PASSWORD, NAME, AGE, PHONE, DAY)
VALUES('ASK', '1234', '질문자', '20', '010-1234-5678', CURRENT_DATE);
COMMIT;

-- DELETE : 하나의 행 데이터 삭제
DELETE FROM USERTBL;
ROLLBACK;
DELETE FROM usertbl WHERE ID = 'ASK';
DELETE FROM usertbl WHERE NAME = '테스터';
DELETE FROM usertbl WHERE AGE = '20';

-- UPDATE : 행 데이터 수정
UPDATE USERTBL SET NAME = '기자' WHERE ID = 'ASK';
COMMIT;
UPDATE USERTBL SET AGE = '25', PHONE = '010-9876-5432' WHERE ID = 'test';
UPDATE USERTBL SET ID = 'TEST' WHERE ID = 'test';

-- SELECT : 데이터 조회
SELECT * FROM USERTBL;
SELECT ID FROM USERTBL;
SELECT PASSWORD FROM USERTBL;
SELECT NAME FROM USERTBL;
SELECT AGE FROM USERTBL;
SELECT PHONE FROM USERTBL;
SELECT DAY FROM USERTBL;

-------------------------------------------------------------
-------------------------------------------------------------
-- 물품 테이블명 : ITEM
-- 번호, 이름, 설명, 가격, 수량, 등록일
-- 번호는 PRIMARY KEY

-- 테이블 생성
CREATE TABLE ITEM(
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(100),
    CONTENT CLOB,
    PRICE NUMBER,
    COUNT NUMBER,
    DAY TIMESTAMP
);

-- SEQUENCE 생성
CREATE SEQUENCE SEQ_ITEM_NO START WITH 1 INCREMENT BY 1 NOCACHE NOMAXVALUE;

-- ITEM 테이블에 데이터 입력
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY) 
VALUES(SEQ_ITEM_NO.NEXTVAL, '맥주', '보리로 만든 술', 3500, 20, CURRENT_DATE);
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY) 
VALUES(SEQ_ITEM_NO.NEXTVAL, '소주', '한국 증류주', 4000, 30, CURRENT_DATE);
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY) 
VALUES(SEQ_ITEM_NO.NEXTVAL, '와인', '포도로 만든 발효주', 4900, 12, CURRENT_DATE);
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY) 
VALUES(SEQ_ITEM_NO.NEXTVAL, '럼', '용설과로 만든 비싼 술', 24900, 1, CURRENT_DATE);
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY) 
VALUES(SEQ_ITEM_NO.NEXTVAL, '보드카', '감자로 만든 독한 술', 18900, 1, CURRENT_DATE);
COMMIT;
SELECT * FROM ITEM;
UPDATE ITEM SET COUNT = '5' WHERE COUNT = '1';

-------------------------------------------------------------
-------------------------------------------------------------
-- 주문 테이블명 : REQUEST 
-- 주문 번호, 주문 수량, 주문 일자, 주문자 정보, 물품 정보
-- 주문 번호는 PRIMARY KEY
-- 주문자 정보는 USER의 ID와 일치
-- 물품 정보는 ITEM의 NO와 일치

CREATE TABLE REQUEST(
    NO NUMBER PRIMARY KEY,
    COUNT NUMBER,
    DAY TIMESTAMP,
    R_ID VARCHAR2(30),
    R_ITEM NUMBER
);

-- 외래키 제약조건
-- 주문자 정보, 물품 정보
ALTER TABLE REQUEST ADD FOREIGN KEY(R_ID) REFERENCES USERTBL(ID);
ALTER TABLE REQUEST ADD FOREIGN KEY(R_ITEM) REFERENCES ITEM(NO);

-- 시퀀스 생성, 주문 번호에 사용
CREATE SEQUENCE SEQ_REQUEST_NO START WITH 1 INCREMENT BY 1 NOCACHE NOMAXVALUE;

-- 주문하기
INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 5, CURRENT_DATE, 'TEST', 1);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 10, CURRENT_DATE, 'TEST', 3);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 3, CURRENT_DATE, 'TEST', 5);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 20, CURRENT_DATE, 'ASK', 2);

SELECT * FROM USERTBL;
SELECT * FROM ITEM;
SELECT * FROM REQUEST;

-- 사용자 3명 추가
INSERT INTO USERTBL(ID, PASSWORD, NAME, AGE, PHONE, DAY)
VALUES ('ABC', '1234', '일반인', 30, '010-1122-3344', CURRENT_DATE);

INSERT INTO USERTBL(ID, PASSWORD, NAME, AGE, PHONE, DAY)
VALUES ('QWE', '1234', '자영업자', 30, '010-5566-7788', CURRENT_DATE);

INSERT INTO USERTBL(ID, PASSWORD, NAME, AGE, PHONE, DAY)
VALUES ('ZXC', '1234', '소비자', 30, '010-5555-6666', CURRENT_DATE);

SELECT * FROM USERTBL;

-- 물품 3개 추가
INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY)
VALUES (SEQ_ITEM_NO.NEXTVAL, '브랜디', '와인을 증류한 증류주', 69800, 3, CURRENT_DATE);

INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY)
VALUES (SEQ_ITEM_NO.NEXTVAL, '버번위스키', '오크통위스키', 119800, 7, CURRENT_DATE);

INSERT INTO ITEM(NO, NAME, CONTENT, PRICE, COUNT, DAY)
VALUES (SEQ_ITEM_NO.NEXTVAL, '샹그리아', '가벼운 주류', 7900, 16, CURRENT_DATE);

SELECT * FROM ITEM;

-- 주문 6개 추가
INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 6, CURRENT_DATE, 'ABC', 8);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 2, CURRENT_DATE, 'ZXC', 7);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 3, CURRENT_DATE, 'QWE', 7);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 2, CURRENT_DATE, 'ZXC', 6);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 1, CURRENT_DATE, 'QWE', 8);

INSERT INTO REQUEST(NO, COUNT, DAY, R_ID, R_ITEM)
VALUES (SEQ_REQUEST_NO.NEXTVAL, 1, CURRENT_DATE, 'ABC', 6);

SELECT * FROM REQUEST;
COMMIT;

-- 현재 시퀀스 값 가져오기
SELECT SEQ_REQUEST_NO.CURRVAL FROM DUAL;
SELECT SEQ_ITEM_NO.CURRVAL FROM DUAL;
-- 수동으로 숫자 바꾸기
ALTER SEQUENCE SEQ_REQUEST_NO INCREMENT BY -9;
ROLLBACK;

-------------------------------------------------------------
-------------------------------------------------------------
-- USERTBL
-- 전체 조회하기
SELECT * FROM USERTBL;
SELECT ID, PASSWORD, NAME, AGE, PHONE, DAY FROM USERTBL;

-- 26살 이상만 조회
SELECT * FROM USERTBL WHERE AGE > 25;
-- 25살 이상, ID가 TEST인 것 조회
SELECT * FROM USERTBL WHERE AGE >= 25 AND ID = 'TEST';
-- 25살 이하이거나 ID가 ABC인 것 조회
SELECT * FROM USERTBL WHERE AGE <= 25 OR ID = 'ABC';
-- NAME 기준으로 내림차순 정렬
SELECT * FROM USERTBL ORDER BY NAME DESC;
-- NAME 기준으로 오름차순 정렬
SELECT * FROM USERTBL ORDER BY NAME ASC;

-- 나이가 20 이상인 사람만 이름을 기준으로 내림차순 정렬
SELECT * FROM USERTBL WHERE AGE >= 20 ORDER BY NAME DESC;

-- 개수 구하기
SELECT COUNT(*) FROM USERTBL;
-- 최대값 구하기
SELECT MAX(AGE) FROM USERTBL;
-- 최소값 구하기
SELECT MIN(AGE) FROM USERTBL;
-- 합계, 평균 구하기
SELECT SUM(AGE), AVG(AGE) FROM USERTBL;

--------------- 평균 나이 이상인 회원의 정보 조회 ---------------
SELECT * FROM USERTBL WHERE AGE >= (SELECT AVG(AGE) FROM USERTBL);

-------------------------------------------------------------
-------------------------------------------------------------
-- ITEM
SELECT * FROM ITEM;

-- 가격이 20000원 이상인 항목 출력
SELECT * FROM ITEM WHERE PRICE >= 20000;
-- 재고 수량이 10개 미만인 것을 40개로 변경
--SELECT * FROM ITEM WHERE COUNT < 10;
UPDATE ITEM SET COUNT = '40' WHERE COUNT < 10;
-- 재고 수량 평균
SELECT AVG(COUNT) FROM ITEM;
-- 물품번호 1번인 항목의 가격을 4000으로 변경
--SELECT * FROM ITEM WHERE NO = '1';
UPDATE ITEM SET PRICE = '4000' WHERE NO = '1';
-- 판매 가격이 가장 높은 물품을 출력
SELECT * FROM ITEM WHERE PRICE = (SELECT MAX(PRICE) FROM ITEM);
-- 주문조회하기
SELECT * FROM REQUEST, ITEM WHERE REQUEST.R_NO = ITEM.I_NO;

-- 뷰만들기 : 조회한 정보를 바탕으로 뷰 만들기(성능적인 부분)
CREATE VIEW REQUESTITEM_VIEW AS SELECT * FROM REQUEST, ITEM 
WHERE REQUEST.R_NO = ITEM.I_NO;
-- 뷰 조회하기
SELECT * FROM REQUESTITEM_VIEW;

SELECT * FROM REQUEST, USERTBL WHERE REQUEST.R_ID = USERTBL.U_ID;
CREATE VIEW REQUESTUSER_VIEW AS SELECT * FROM REQUEST, USERTBL 
WHERE REQUEST.R_ID = USERTBL.U_ID;
-- 뷰 조회하기
SELECT * FROM REQUESTUSER_VIEW;

-------------------------------------------------------------
-------------------------------------------------------------
/* dept02 테이블
    deptno      숫자, 기본키
    dname       가변길이 6자
*/
/* emp02 테이블
    empno       숫자, 기본키
    name        가변길이 10자 not null
    deptno      숫자 not null
    position    가변길이 10자로 "사원, 대리, 과장, 부장"만 가능
    pay         숫자, not null
    pempno      숫자
    regdate date로
*/
-- DEPT02 테이블 생성
CREATE TABLE DEPT02 (
    DEPTNO NUMBER PRIMARY KEY,
    DNAME VARCHAR2(12)
);
-- EMP02 테이블 생성
CREATE TABLE EMP02(
    EMPNO NUMBER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL,
    DEPTNO NUMBER NOT NULL,
    POSITION VARCHAR2(20) CHECK (POSITION IN ('사원', '대리', '과장', '부장')),
    PAY NUMBER NOT NULL,
    P_EMPNO NUMBER,
    DAY DATE
);

-- 외래키 적용
ALTER TABLE EMP02 ADD FOREIGN KEY(DEPTNO) REFERENCES DEPT02(DEPTNO);
-- 시퀀스 생성
CREATE SEQUENCE SEQ_EMP02_EMPNO START WITH 1 INCREMENT BY 1 NOCACHE NOMAXVALUE;

-- DEPT02 데이터 추가
INSERT INTO DEPT02(DEPTNO, DNAME)
VALUES ('101', '영업부');
INSERT INTO DEPT02(DEPTNO, DNAME)
VALUES ('102', '총무부');
INSERT INTO DEPT02(DEPTNO, DNAME)
VALUES ('103', '기획부');
INSERT INTO DEPT02(DEPTNO, DNAME)
VALUES ('104', '홍보부');
SELECT * FROM DEPT02;
COMMIT;

-- EMP02 데이터 추가
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '김독자', 101, '부장', 500, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '한수영', 102, '부장', 450, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '이상아', 101, '과장', 400, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '이현성', 103, '과장', 350, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '이길영', 101, '대리', 310, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '이지혜', 102, '과장', 400, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '한초희', 102, '대리', 320, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '한명오', 103, '사원', 380, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '유중혁', 101, '사원', 280, NULL, CURRENT_DATE);
INSERT INTO EMP02(EMPNO, NAME, DEPTNO, POSITION, PAY, P_EMPNO, DAY)
VALUES (SEQ_EMP02_EMPNO.NEXTVAL, '공필두', 104, '사원', 250, NULL, CURRENT_DATE);

SELECT * FROM EMP02;
COMMIT;
SELECT * FROM EMP02, DEPT02 WHERE EMP02.DEPTNO = DEPT02.DEPTNO;

CREATE VIEW COMPANY_VIEW AS SELECT * FROM EMP02, DEPT02 
WHERE EMP02.E_DEPTNO = DEPT02.DEPTNO;
SELECT * FROM COMPANY_VIEW;