VIEW 연습

학생 테이블 생성
학번(문자) 이름(문자) 학과(문자) 나이(숫자)
성별(문자) 전화(문자) 주소(문자) 취미(문자) 교수 번호(문자)

 

제약조건

학번 기본키 참조키 교수 번호
학과(컴퓨터학과, 통계과, 건축과, 전기과, 전자과)
나이(19세 이상) 성별(남, 여)
취미를 제외하고 모든 칼럼 NULL 포함 X

 

CREATE TABLE STUDENT (
STNUM VARCHAR2(10) PRIMARY KEY,
STNAME VARCHAR2(20) NOT NULL,
MAJOR VARCHAR2(20)  NOT NULL CHECK(MAJOR IN('컴퓨터학과','통계과','건축과','전기과','전자과')),
AGE NUMBER  NOT NULL CHECK(AGE >=19),
GENDER CHAR(5) NOT NULL CHECK(GENDER IN('남','여')),
PHONE VARCHAR2(30) NOT NULL,
ADDR VARCHAR2(50) NOT NULL,
HOBBY VARCHAR2(10),
PRONUM VARCHAR2(10) NOT NULL REFERENCES PROFESSOR);

PROFESSOR 테이블이 먼저 필요함

DROP TABLE STUDENT;

 

교수 테이블 생성
교수 번호 (문자) 이름(문자) 학과(문자) 나이(숫자) 전화(문자)
제약조건

기본키 교수 번호 나이(30세 이상)
학과(컴퓨터학과, 통계과, 건축과, 전기과, 전자과)
모든 칼럼 NULL제외

 

CREATE TABLE PROFESSOR (
PRONUM VARCHAR2(10) PRIMARY KEY,
PRONAME VARCHAR2(20) NOT NULL,
PROMAJOR VARCHAR2(20) NOT NULL CHECK(PROMAJOR IN('컴퓨터학과','통계과','건축과','전기과','전자과')),
PROAGE NUMBER NOT NULL CHECK(PROAGE >=30),
PROPHONE VARCHAR2(30) NOT NULL);

교수 번호 시퀀스 생성

CREATE SEQUENCE PROFESSOR_NUM
INCREMENT BY 1
START WITH 1;

교수 테이블에 10개의 데이터 저장

INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수일번','컴퓨터학과',50,'011-111-1111');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수이번','통계과',40,'012-222-2222');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수삼번','전기과',30,'013-333-3333');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수사번','전자과',70,'014-444-4444');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수오번','컴퓨터학과',45,'015-555-5555');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수육번','건축과',55,'016-666-6666');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수칠번','전기과',65,'017-777-7777');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수팔번','통계과',34,'018-888-8888');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수구번','컴퓨터학과',41,'019-999-9999');
INSERT INTO PROFESSOR VALUES(PROFESSOR_NUM.NEXTVAL,'교수십번','건축과',52,'010-000-0000');

학생 번호 시퀀스 생성

CREATE SEQUENCE STUDENT_NUM
INCREMENT BY 1
START WITH 1;

INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'김하나','컴퓨터학과',19,'여','010-1111-1111','서울시','독서','1');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'정두리','건축과',19,'여','010-1111-1111','대구시','게임','10');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'박세가','전기과',19,'여','010-1111-1111','부산시',NULL,'7');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'이네이','컴퓨터학과',19,'여','010-1111-1111','대전시','게임','5');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'김다서','전기과',19,'여','010-1111-1111','충청시','운동','3');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'박유성','전자과',19,'여','010-1111-1111','인천시','운동','4');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'이칠공','전자과',19,'여','010-1111-1111','광주시',NULL,'4');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'김팔삼','컴퓨터학과',19,'여','010-1111-1111','전주시','없음','9');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'박구식','건축과',19,'여','010-1111-1111','제주시','게임','6');
INSERT INTO STUDENT VALUES (STUDENT_NUM.NEXTVAL,'이열심','통계과',19,'여','010-1111-1111','서울시','독서','2');

모든 정보를 출력

SELECT * 
FROM STUDENT S LEFT JOIN PROFESSOR P
ON S.PRONUM = P.PRONUM;

SELECT S.STNUM 학번,S.STNAME 이름,S.MAJOR 학과,S.AGE 나이,S.GENDER 성별,S.PHONE 전화번호,S.ADDR 주소,S.HOBBY 취미,S.PRONUM 교수번호,P.PRONAME 교수이름,P.PROMAJOR 학과,P.PROAGE 교수나이,P.PROPHONE 교수번화번호
FROM STUDENT S LEFT JOIN PROFESSOR P
ON S.PRONUM = P.PRONUM;

인덱스를 추가

CREATE INDEX IDX_STUDENT_STNAME
ON STUDENT(STNAME);

CREATE INDEX IDX_PROFESSOR_PRONAME
ON PROFESSOR(PRONAME);

가상 테이블을 생성하여 모든 정보 출력

CREATE VIEW STUPRO_VIEW AS
SELECT S.STNUM 학번,S.STNAME 이름,S.MAJOR 학과,S.AGE 나이,S.GENDER 성별,S.PHONE 전화번호,S.ADDR 주소,S.HOBBY 취미,S.PRONUM 교수번호,P.PRONAME 교수이름,P.PROMAJOR 교수학과,P.PROAGE 교수나이,P.PROPHONE 교수번화번호
FROM STUDENT S LEFT JOIN PROFESSOR P
ON S.PRONUM = P.PRONUM;

출력

SELECT *
FROM STUPRO_VIEW;

사원명, 부서명, 급여, 급여등급, 성과급 출력

SELECT E.ENAME,D.DNAME ,E.SALARY, S.GRADE, E.COMMISSION
FROM EMPLOYEE E, department D ,salgrade S
WHERE E.DNO = D.DNO  AND SALARY > LOSAL AND SALARY <HISAL;

사원명 부서명 급여 급여등급 성과급 가상 테이블로 만들어 보기

3개의 테이블 조인

CREATE VIEW EMP_SAMPLE_VIEW
AS
SELECT E.ENAME,D.DNAME ,E.SALARY, S.GRADE, E.COMMISSION
FROM EMPLOYEE E, department D ,salgrade S
WHERE E.DNO = D.DNO  AND SALARY > LOSAL AND SALARY <HISAL;

서브 쿼리

CREATE VIEW EMP_SAMPLE2_VIEW
AS
SELECT E.ENAME, (SELECT DNAME FROM department WHERE E.DNO = DNO) AS 부서명  ,E.SALARY,
(SELECT GRADE
FROM SALGRADE
WHERE SALARY BETWEEN LOSAL AND HISAL) AS 급여등급
,NVL(E.COMMISSION,0) 커미션
FROM EMPLOYEE E;

SELECT *
FROM EMP_SAMPLE2_VIEW;