SpringMVCStudy/mybatisWork03_1_scott.sql at main · hyodii/SpringMVCStudy · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SELECT USER
FROM DUAL;
--==>> SCOTT

SELECT *
FROM TBL_STUDENT;

SELECT *
FROM TBL_GRADE;

-- 학생 테이블 생성
CREATE TABLE TBL_STUDENT
(  SID      	NUMBER		-- PK
, NAME     	VARCHAR2(30)
, TEL      	VARCHAR2(40)
, CONSTRAINT STUDENT_SID_PK PRIMARY KEY(SID)
);
--==>> Table TBL_STUDENT이(가) 생성되었습니다.

-- 성적 테이블 생성
CREATE TABLE TBL_GRADE
(  SID       	NUMBER		-- PK / FK
, SUB1	NUMBER(3)
, SUB2	NUMBER(3)
, SUB3 	NUMBER(3)
, CONSTRAINT GRADE_SID_PK PRIMARY KEY(SID)
, CONSTRAINT GRADE_SID_FK FOREIGN KEY(SID)
        REFERENCES TBL_STUDENT(SID)
);
--==>> Table TBL_GRADE이(가) 생성되었습니다.

-- 학생 리스트 시퀀스 생성
CREATE SEQUENCE STUDENTLISTSEQ
NOCACHE;
--==>> Sequence STUDENTLISTSEQ이(가) 생성되었습니다.

-- 학생 테이블에 샘플 데이터 삽입
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(STUDENTLISTSEQ.NEXTVAL, '정효진', '010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(STUDENTLISTSEQ.NEXTVAL, '정말로', '010-2222-2222');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_STUDENT(SID, NAME, TEL)
VALUES(STUDENTLISTSEQ.NEXTVAL, '점심', '010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.

-- 성적 테이블에 샘플 데이터 삽입
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(1, 100, 100, 100);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(2, 90, 80, 70);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_GRADE(SID, SUB1, SUB2, SUB3)
VALUES(3, 30, 80, 70);
--==>> 1 행 이(가) 삽입되었습니다.


-- 학생 뷰 만들기
CREATE OR REPLACE VIEW STUDENTVIEW
AS
SELECT S.SID AS SID, S.NAME AS NAME, S.TEL AS TEL
     , (SELECT COUNT(*)
        FROM TBL_GRADE
        WHERE SID = S.SID) AS SUB
FROM TBL_STUDENT S;
--==>> View STUDENTVIEW이(가) 생성되었습니다.

-- 성적 뷰 만들기
CREATE OR REPLACE VIEW GRADEVIEW
AS
SELECT G.SID AS SID
    , (SELECT NAME FROM TBL_STUDENT WHERE SID=G.SID) AS NAME
    , G.SUB1 AS SUB1, G.SUB2 AS SUB2, G.SUB3 AS SUB3
    , (G.SUB1+G.SUB2+G.SUB3) AS TOT
    , ROUND((G.SUB1+G.SUB2+G.SUB3)/3.0, 2) AS AVG
    , CASE WHEN ( (G.SUB1+G.SUB2+G.SUB3)/3.0 < 60 ) THEN '불합격'
           WHEN ( G.SUB1<40 OR G.SUB2<40 OR G.SUB3<40 ) THEN '과락'
           ELSE '합격'
       END AS CH
FROM TBL_GRADE G;
--==>> View GRADEVIEW이(가) 생성되었습니다.


SELECT SID, NAME, TEL, SUB
FROM STUDENTVIEW;

SELECT SID, NAME, SUB1, SUB2, SUB3, TOT, AVG, CH
FROM GRADEVIEW;

-- 커밋
COMMIT;
--==>> 커밋 완료