151009 - SQL 과제

해당 테이블 생성하기

계정 생성 후 접속

===========================================

CREATE USER pbs IDENTIFIED by 1111;

GRANT RESOURCE, connect to TEST;

CONN TEST/1111

테이블 post 만들기

===========================================

CREATE TABLE POST(

POST1 CHAR(3),

POST2 CHAR(3),

ADDR VARCHAR2(60)  CONSTRAINT POST_ADDR NOT NULL,

CONSTRAINT PK_POST PRIMARY KEY (POST1, POST2)

);

출력

=================================================

POS POS ADDR

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

        경기도 성남시 분당구 정자동

테이블 member 만들기

=================================================

CREATE TABLE MEMBER(

ID   NUMBER(4)   CONSTRAINT MEMBER_PK_ID PRIMARY KEY,

NAME  VARCHAR2(10)  CONSTRAINT MEMBER_NAME NOT NULL,

SEX   CHAR(1)    CONSTRAINT MEMBER_CK_SEX CHECK(sex=1 OR sex=2),

JUMIN1  CHAR(6),

JUMIN2  CHAR(7),

TEL   VARCHAR2(15),

POST1  CHAR(3),

POST2  CHAR(3),

ADDR  VARCHAR2(60),  

CONSTRAINT MEMBER_UK_JUMIN UNIQUE (JUMIN1, JUMIN2),

CONSTRAINT MEMBER_FK_POST FOREIGN KEY (POST1, POST2)REFERENCES POST(POST1, POST2)

);

데이터 입력하기

===================================================

ALTER TABLE POST DISABLE NOVALIDATE CONSTRAINT PK_POST; //POST 에 제약 일시정지

INSERT INTO POST VALUES('', '', '경기도 성남시 분당구 정자동'); //post 에 데이터 입력

ALTER TABLE POST ENABLE NOVALIDATE CONSTRAINT PK_POST; //POST 에 제약 재실행

ALTER TABLE MEMBER DISABLE NOVALIDATE CONSTRAINT MEMBER_FK_POST; //member 에 제약 일시정지

INSERT INTO MEMBER VALUES('1234', '홍길동', '1', '990101',  // member 에 데이터입력

       '1232344', '712-1234', '100', '010', '');

ALTER TABLE MEMBER ENABLE NOVALIDATE CONSTRAINT MEMBER_FK_POST; // member 에 제약 재실행

출력

====================================================

        ID NAME       S JUMIN1 JUMIN2  TEL             POS POS

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

ADDR

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

      1234 홍길동     1 990101 1232344 712-1234        100 010

====================================================