[20121206] 17일차

-----------------------------------------

회원관리+성적처리 프로젝트

Servlet_20121206.war

1. 성적 테이블, 회원 테이블 조인. FK 설정 필요.

2. 회원(회원번호-PK, 이름, 전화번호) -> 이전과 동일

3. 성적 (성적번호-PK, 회원번호-FK, 국어, 영어, 수학) 

-> 이름 대신 회원번호-FK 설정

4. 회원에 회원 등록을 먼저하고, 성적에 성적 입력을 나중에 한다.

----------------------------------------

CREATE TABLE member2 (

mid NUMBER --PK

,name VARCHAR2(10)

,tel VARCHAR2(20)

);

ALTER TABLE member2

ADD CONSTRAINT member2_mid_pk PRIMARY KEY(mid);

CREATE SEQUENCE member2Seq;

//회원 테이블에 자료 입력. INSERT 쿼리.

//쿼리문의 끝에 ; 표시하지 말것.

INSERT INTO member2 (mid, name, tel) VALUES (member2Seq.nextval, 'hong', '111-1111');

INSERT INTO member2 (mid, name, tel) VALUES (member2Seq.nextval, 'park', '222-2222');

COMMIT;

----------------------------------------

CREATE TABLE score2 (

sid NUMBER --PK, 자동 번호 부여

,mid NUMBER --FK

,kor NUMBER(3) --CK (0~100)

,eng NUMBER(3) --CK (0~100)

,mat NUMBER(3) --CK (0~100)

);

--제약 조건 추가

ALTER TABLE score2

ADD CONSTRAINT score2_sid_pk PRIMARY KEY (sid);

ALTER TABLE score2

ADD CONSTRAINT score2_kor2_ck CHECK (kor BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_eng_ck CHECK (eng BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_mat_ck CHECK (mat BETWEEN 0 AND 100);

ALTER TABLE score2

ADD CONSTRAINT score2_mid_fk FOREIGN KEY (mid)

REFERENCES member2(mid);

--자동 번호 부여 시퀀스 객체 생성

CREATE SEQUENCE score2Seq;

--INSERT 쿼리 샘플 (쿼리 끝 부분에 ; 표시하지 말 것)

INSERT INTO score2 (sid, mid, kor, eng, mat)

VALUES (score2Seq.nextval, 1, 100, 100, 100)

--SELECT 쿼리 샘플 (쿼리 끝 부분에 ; 표시하지 말 것)

SELECT m.mid AS mid, sid, name, kor, eng, mat

, (kor+eng+mat) AS tot

, (kor+eng+mat)/3 AS ave    

, CASE

WHEN ((kor+eng+mat)/3 >= 60) AND (kor<40 OR eng<40 OR 

mat<40) THEN '과락'

WHEN ((kor+eng+mat)/3 >= 60) THEN '합격'

ELSE '불합격'

END AS grade  

    FROM member2 m, score2 s

    WHERE m.mid = s.mid(+)

--SELECT 쿼리 전용 뷰 생성(scott 계정)

CREATE OR REPLACE VIEW member2Score2View

AS

SELECT m.mid AS mid, sid, name, kor, eng, mat

, (kor+eng+mat) AS tot

, (kor+eng+mat)/3 AS ave    

, CASE

WHEN ((kor+eng+mat)/3 >= 60) AND (kor<40 OR eng<40 OR 

mat<40) THEN '과락'

WHEN ((kor+eng+mat)/3 >= 60) THEN '합격'

ELSE '불합격'

END AS grade  

    FROM member2 m, score2 s

    WHERE m.mid = s.mid(+);

--뷰를 이용한 SELECT 쿼리 실행

SELECT mid, sid, name, kor, eng, mat, tot, ave, grade 

FROM member2Score2View;

-----------------

//DBConn.java

//Member2DTO.java

//Score2DTO.java

//Member2DAO.java

//Score2DAO.java

//Member2Score2Servlet.java

//Member2List.jsp

//Member2InsertForm.jsp

//Score2List.jsp

//Score2InsertForm.jsp

//web.xml

//요청주소

http://localhost:8090/Servlet_20121206/Member2List.ms

-------------------------------------------------------