SQL 5일차(연습문제)

-- 1. 출판사들에 대한 데이터를 담기 위한 출판사 테이블(TB_PUBLISHER)
-- 칼럼 : PUB_NO (출판사 번호) - 기본키 (PUBLISHER_PK)
--        PUB_NAME (출판사명) - NOT NULL (PUBLISHER_NN)
--        PHONE (출판사 전화번호) - 제약조건 없음

DROP TABLE TB_PUBLISHER;

CREATE TABLE TB_PUBLISHER(
    PUB_NO NUMBER CONSTRAINT PUBLISHER_PK PRIMARY KEY,
    PUB_NAME VARCHAR2(30) CONSTRAINT PUBLISHER_NN NOT NULL,
    PHONE VARCHAR2(20)
);

먼저 테이블을 생성한다.

COMMENT ON COLUMN TB_PUBLISHER.PUB_NO IS '출판사 번호';
COMMENT ON COLUMN TB_PUBLISHER.PUB_NAME IS '출판사명';
COMMENT ON COLUMN TB_PUBLISHER.PHONE IS '출판사 전화번호';

COMMENT문을 이용해서 칼럼명 주석처리를 한다.

-- 샘플 3개 정도 생성

SELECT * FROM tb_publisher;

INSERT INTO tb_publisher VALUES(0001, '한빛아카데미', '02-1234-1234');
INSERT INTO tb_publisher VALUES(0002, '이지스퍼블리싱', '02-2345-2345');
INSERT INTO tb_publisher VALUES(0003, '영풍문고', '02-3456-3456');

ROW를 입력하고 SELECT문으로 검색하여 잘 삽입이 되었는지 확인한다.

-- 2. 도서들에 대한 데이터를 담기 위한 도서 테이블(TB_BOOK)
-- 칼럼 : BK_NO (도서번호) - 기본키 (BOOK_PK)
--        BK_TITLE (도서명) - NOT NULL (BOOK__NN_TITLE)
--        BK_AUTHOR (저자명) - NOT NULL (BOOK__NN_AUTHOR)
--        BK_PRICE (가격) - 제약조건 없음
--        BK_PUB_NO (출판사 번호) - 외래키 (BOOK_FK), TB_PUBLISHER 테이블 참조
--                                 이때 참조하고 있는 부모데이터 삭제 시 자식데이터도 삭제되도록 옵션 지정

DROP TABLE TB_BOOK;

CREATE TABLE TB_BOOK(
    BK_NO NUMBER CONSTRAINT BOOK_PK PRIMARY KEY,
    BK_TITLE VARCHAR2(60) CONSTRAINT BOOK__NN_TITLE NOT NULL,
    BK_AUTHOR VARCHAR2(30) CONSTRAINT BOOK__NN_AUTHOR NOT NULL,
    BK_PRICE NUMBER,
    BK_PUB_NO CONSTRAINT BOOK_FK REFERENCES TB_PUBLISHER(PUB_NO) ON DELETE CASCADE
);

칼럼레벨 방식을 이용하여 참조.

DELETE CASCADE를 이용하여 자식테이블의 데이터와 부모테이블의 데이터가 함께 삭제가 되도록 설정.

 

COMMENT ON COLUMN TB_BOOK.BK_NO IS '도서번호';
COMMENT ON COLUMN TB_BOOK.BK_TITLE IS '도서명';
COMMENT ON COLUMN TB_BOOK.BK_AUTHOR IS '저자명';
COMMENT ON COLUMN TB_BOOK.BK_PRICE IS '가격';
COMMENT ON COLUMN TB_BOOK.BK_PUB_NO IS '출판사 번호';

마찬가지로 COMMENT문을 사용해서 주석 처리.

-- 샘플 5개 정도 생성

SELECT * FROM TB_BOOK;

INSERT INTO TB_BOOK VALUES(0101, 'DOIT SQL입문', '김성욱', 20000, 0002);
INSERT INTO TB_BOOK VALUES(0102, 'DOIT 자바프로그래밍 입문', '박은종', 30000, 0002);
INSERT INTO TB_BOOK VALUES(0103, 'DOIT HTML/CSS/JS 정석', '홍길동', 23000, 0002);
INSERT INTO TB_BOOK VALUES(0104, '이기적유전자', '리처드 도킨스', 32000, 0003);
INSERT INTO TB_BOOK VALUES(0105, '라플라스의 마녀', '히가시노 게이고', 25000, 0003);

같은 방식으로 INSERT문을 이용하여 TB_BOOK 테이블에 삽입하고 SELECT문으로 확인.

-- 3. 회원에 대한 데이터를 담기 위한 회원 테이블(TB_MEMBER)
-- 컬럼 : MEMBER_NO (회원번호) - 기본키 (MEMBER_PK)
--        MEMBER_ID (아이디) - 중복금지 (MEMBER_UQ_ID)
--        MEMBER_PWD (비밀번호) - NOT NULL (MEMBER_NN_PWD)
--        MEMBER_NAME (회원명) - NOT NULL (MEMBER_NN_NAME)
--        GENDER (성별) - M 또는 F로 제한 (MEMBER_CK_GEN)
--        ADDRESS (주소) - 제약조건 없음
--        PHONE (연락처) - 제약조건 없음
--        STATUS (탈퇴여부) - 기본값 N, N 또는 Y만 허용 (MEMBER_CK_STA)
--        ENROLL_DATE (가입일) - 기본값 SYSDATE, NOT NULL (MEMBER_NN_EN)

DROP TABLE TB_MEMBER;

CREATE TABLE TB_MEMBER(
    MEMBER_NO NUMBER CONSTRAINT MEMBER_PK PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) CONSTRAINT MEMBER_UQ_ID UNIQUE,
    MEMBER_PWD VARCHAR2(20) CONSTRAINT MEMBER_NN_PWD NOT NULL,
    MEMBER_NAME VARCHAR2(21) CONSTRAINT MEMBER_NN_NAME NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_CK_GEN CHECK(GENDER IN ('남', '여')),
    ADDRESS VARCHAR2(40),
    PHONE VARCHAR2(13),
    STATUS CHAR(2) DEFAULT 'N' CONSTRAINT MEMBER_CK_STA CHECK(STATUS IN('Y', 'N')),
    ENROLL_DATE DATE DEFAULT SYSDATE CONSTRAINT MEMBER_NN_EN NOT NULL
); -- DFAULT는 CONTRAINT를 사용할 경우 CONSTRAINT 앞에 작성!

CONSTRAINT 앞에 DEFAULT를 사용.(뒤에 작성 시 오류 발생)

 

COMMENT ON COLUMN TB_MEMBER.MEMBER_NO IS '회원번호';
COMMENT ON COLUMN TB_MEMBER.MEMBER_ID IS '아이디';
COMMENT ON COLUMN TB_MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN TB_MEMBER.MEMBER_NAME IS '회원명';
COMMENT ON COLUMN TB_MEMBER.GENDER IS '성별';
COMMENT ON COLUMN TB_MEMBER.ADDRESS IS '주소';
COMMENT ON COLUMN TB_MEMBER.PHONE IS '연락처';
COMMENT ON COLUMN TB_MEMBER.STATUS IS '탈퇴여부';
COMMENT ON COLUMN TB_MEMBER.ENROLL_DATE IS '가입일';

COMMENT문으로 주석처리

-- 샘플 5개 정도 생성

SELECT * FROM TB_MEMBER;

INSERT INTO TB_MEMBER VALUES(1, 'AAA', 'QWER1234', '홍길동', '남', NULL, '010-1111-1111', DEFAULT, DEFAULT);
INSERT INTO TB_MEMBER VALUES(2, 'BBB', 'QWER2345', '홍길서', '여', '서울특별시 강남구 테헤란로', '010-2222-1111', 'N', DEFAULT);
INSERT INTO TB_MEMBER VALUES(3, 'CCC', 'QWER3456', '홍길남', '남', NULL, '010-1111-3333', DEFAULT, DEFAULT);
INSERT INTO TB_MEMBER VALUES(4, 'DDD', 'QWER4567', '홍길북', '남', '경기도 안산시', '010-4444-1111', 'Y', '03/04/05');
INSERT INTO TB_MEMBER VALUES(5, 'EEE', 'QWER5678', '홍길중', '남', '충청북도 천안시', '010-1111-5555', 'N', '01/01/01');

-- 4. 어떤 회원이 어떤 도서를 대여했는지에 대한 대여목록 테이블(TB_RENT)
-- 컬럼 : RENT_NO (대여번호) - 기본키 (RENT_PK)
--        RENT_MEM_NO (대여회원번호) - 외래키 (RENT_FK_MEM), TB_MEMBER 참조
--                                     부모 데이터 삭제 시 자식데이터 값이 NULL이 되도록 지정
--        RENT_BOOK_NO (대여도서번호) - 외래키 (RENT_FK_BOOK), TB_BOOK 참조
--                                      부모 데이터 삭제 시 자식데이터 값이 NULL이 되도록 지정
--        RENT_DATE (대여일) - 기본값 SYSDATE

DROP TABLE TB_RENT;

CREATE TABLE TB_RENT(
    RENT_NO NUMBER CONSTRAINT RENT_PK PRIMARY KEY,
    RENT_MEM_NO NUMBER CONSTRAINT RENT_FK_MEM REFERENCES TB_MEMBER(MEMBER_NO) ON DELETE SET NULL,
    RENT_BOOK_NO NUMBER CONSTRAINT RENT_FK_BOOK REFERENCES TB_BOOK(BK_NO) ON DELETE SET NULL,
    RENT_DATE DATE DEFAULT SYSDATE
);

이젠 부모테이블인 TB_RENT 테이블을 생성하여 자식테이블과 관계 설정

COMMENT ON COLUMN TB_RENT.RENT_NO IS '대여번호';
COMMENT ON COLUMN TB_RENT.RENT_MEM_NO IS '대여회원번호';
COMMENT ON COLUMN TB_RENT.RENT_BOOK_NO IS '대여도서번호';
COMMENT ON COLUMN TB_RENT.RENT_DATE IS '대여일';

주석처리

-- 샘플 3개 정도 생성

INSERT INTO TB_RENT VALUES(1, 1, 0101, SYSDATE);
INSERT INTO TB_RENT VALUES(2, 3, 0103, SYSDATE);
INSERT INTO TB_RENT VALUES(3, 4, 0104, SYSDATE);

연결이 되어 있기 때문에 제약조건에 맞는 데이터를 삽입.

-- 대여목록 조회

SELECT * FROM TB_RENT;

대여테이블 TB_RENT를 모두 검색

-- TB_RENT, TB_MEMBER, TB_BOOK 테이블을 JOIN 하여 대여 정보를 조회

SELECT RENT_NO, MEMBER_NAME, BK_TITLE, RENT_DATE FROM TB_RENT
JOIN TB_MEMBER ON MEMBER_NO = RENT_MEM_NO
JOIN TB_BOOK ON BK_NO = RENT_BOOK_NO;

저번에 배운 조인을 이용하여, 대여번호, 회원이름, 책제목, 대여날짜를 검색

 

E-R 다이어그램

출판사와 책의 관계는 N:1이다.

왜냐하면, 출판사에서 만들어진 책들은 서로 다른 여러 개의 책이 존재할 수 있다.

 

책과 대여기록은 1:N이다.

왜냐하면, 하나의 책을 여러번 기록될 수 있기 때문이다.

그렇다면, 왜 책은 N으로 올 수 없는가? : 물리적으로는 책이 여러개가 존재할 수 있지만, 논리적인 데이터베이스 상에서는 하나의 ROW값만 존재하기 때문에 N으로 올 수 없다.

 

회원과 대여기록은 1:N이다

왜냐하면, 한 사람이 책을 여러 번 빌릴 수 있기 때문이다. 

(배우는 단계이기 때문에 틀린 부분이 있을 수 있습니다.)