[Oracle] DDL_실습문제

실습문제

도서관리 프로그램을 만들기 위한 테이블을 만들기
이때, 제약조건에 이름을 부여할 것
각 컬럼에 주석달기

---

1. 출판사들에 대한 데이터를 담기 위한 출판사 테이블(TB_PUBLISHER)
컬럼: PUB_NO(출판사번호)
기본키(PUBLISHER_PK)
PUB_NAME(출판사명)
NOT NULL(PUBLICHSER_NN)
PHONE(출판사전화번호)
제약조건 없음
3개 정도의 샘플 데이터 추가하기

CREATE TABLE TB_PUBLISHER(
    PUB_NO VARCHAR2(10) CONSTRAINT PUBLISHER_PK PRIMARY KEY,
    PUB_NAME VARCHAR2(20) CONSTRAINT PUBLISHER_NN NOT NULL,
    PHONE VARCHAR2(13)
    );
INSERT INTO TB_PUBLISHER VALUES('P01','촴비','01011112222');
INSERT INTO TB_PUBLISHER VALUES('P02','문학돈내','01033334444');
INSERT INTO TB_PUBLISHER VALUES('P03','부기시빌','01055556666');

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

---

2. 도서들에 대한 데이터를 담기 위한 도서 테이블(TB_BOOK)
컬럼: BK_NO(도서번호)
기본키(BOOK_PK)
BK_TITLE(도서명)
NOT NULL(BOOK_NN_TITLE)
BK_AUTHOR(저자명)
NOT NULL(BOOK_NN_AUTHOR)
BK_PRICE(가격)
BK_STOCK(재고)
기본값 1로 지정
BK_PUB_NO(출판사번호)
외래키(BOOK_FK)(TB_PUBLISHER 테이블을 참조하도록)
이때 참조하고 있는 부모데이터 삭제 시 자식데이터도 삭제되도록 설정
5개 정도의 샘플 데이터 추가하기

CREATE TABLE TB_BOOK(
BK_NO VARCHAR2(10) CONSTRAINT BOOK_PK PRIMARY KEY,
BK_TITLE VARCHAR2(40) CONSTRAINT BOOK_NN_TITLE NOT NULL,
BK_AUTHOR VARCHAR2(20) CONSTRAINT BOOK_NN_AUTHOR NOT NULL,
BK_PRICE NUMBER,
BK_STOCK NUMBER DEFAULT 1,
BK_PUB_NO VARCHAR2(10) CONSTRAINT BOOK_FK REFERENCES TB_PUBLISHER ON DELETE CASCADE 
);

INSERT INTO TB_BOOK VALUES('B01', '잉여공주', '김원종', 25000, 10, 'P01');
INSERT INTO TB_BOOK(BK_NO, BK_TITLE, BK_AUTHOR, BK_PRICE, BK_PUB_NO) 
VALUES ('B02', '미운 우리 새끼', '최원종', 180000, 'P02');
INSERT INTO TB_BOOK VALUES('B03', '어른왕자', '박원종', 33000, 2, 'P01');
INSERT INTO TB_BOOK VALUES('B04', '백설기공주', '홍원종', 45000, 4, 'P02');
INSERT INTO TB_BOOK VALUES('B05', '중지공주', '윤원종', 77000, 5, 'P03');

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_STOCK IS '재고';
COMMENT ON COLUMN TB_BOOK.BK_PUB_NO IS '출판서번호';

SELECT * FROM TB_BOOK;

---

3. 회원에 대한 데이터를 담기 위한 회원 테이블(TB_MEMBER)
컬럼명: MEMBER_NO(회원번호)
기본키(MEMBER_PK)
MEMBER_ID(아이디)
중복금지(MEMBER_UQ)
MEMBER_PWD(비밀번호) NOT NULL(MEMBER_NN_PWD)
MEMBER_NAME(회원명) NOT NULL(MEMBER_NN_NAME)
GENDER(성별) 'M' 또는 'F'로 입력되도록 제한(MEMBER_CK_GEN)
ADDRESS(주소) --PHONE(연락처) --STATUS(탈퇴여부)
기본값으로 'N' 그리고 'Y' 혹은 'N'으로 입력되도록 제약조건(MEMBER_CK_STA)
ENROLL_DATE(가입일)
기본값으로 SYSDATE, NOT NULL 조건(MEMBER_NN_EN)
5개 정도의 샘플 데이터 추가하기

CREATE TABLE TB_MEMBER(
MEMBER_NO VARCHAR2(10) CONSTRAINT MEMBER_PK PRIMARY KEY,
MEMBER_ID VARCHAR2(40) CONSTRAINT MEMBER_UQ UNIQUE,
MEMBER_PWD VARCHAR2(30) CONSTRAINT MEMBER_NN_PWD NOT NULL,
MEMBER_NAME VARCHAR(20) CONSTRAINT MEMBER_NN_NAME NOT NULL,
GENDER CHAR(3) CONSTRAINT MEMBER_CK_GEN CHECK (GENDER IN('M', 'F')),
ADDRESS VARCHAR(50),
PHONE VARCHAR(13),
STATUS CHAR(1) CONSTRAINT MEMBER_CK_STA CHECK (STATUS IN('N','Y')),
ENROLL_DATE DATE DEFAULT SYSDATE CONSTRAINT MEMBER_NN_EN NOT NULL
);

SELECT * FROM TB_MEMBER;

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 '가입일';

INSERT INTO TB_MEMBER VALUES('M2421','ASDF123','123AA','김지우','F','경기도 부천역','010-1111-9999','N','2015/05/21');
INSERT INTO TB_MEMBER VALUES('M2422','ASDF234','123BB','최민준','M','경기도 철산역','010-2222-8888','N','2016/04/21');
INSERT INTO TB_MEMBER VALUES('M2423','ASDF456','123CC','이서윤','M','서울특별시 서울역','010-3333-7777','Y','2021/09/21');
INSERT INTO TB_MEMBER VALUES('M2431','ASDF789','123DD','최서준','F','강원도 강릉역','010-4444-6666','Y','2020/04/11');
INSERT INTO TB_MEMBER (MEMBER_NO, MEMBER_ID, MEMBER_PWD, MEMBER_NAME, GENDER, ADDRESS, PHONE, STATUS) VALUES('M2453','ASDF135','123EE','박도윤','F','서울특별시 신림역','010-5555-5555','N');

SELECT * FROM TB_MEMBER;

---

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
샘플데이터 3개정도 추가하기

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

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 '대여일';

INSERT INTO TB_RENT VALUES('R1', 'M2422', 'B01', '17/06/30');
INSERT INTO TB_RENT (RENT_NO,RENT_MEM_NO,RENT_BOOK_NO) VALUES('R2', 'M2453', 'B04');
INSERT INTO TB_RENT VALUES('R3', 'M2421', 'B05', '19/12/11');
INSERT INTO TB_RENT VALUES('R4', 'M2422', 'B05', '18/06/20');

SELECT * FROM TB_RENT;

---

2번 도서를 대여한 회원의 이름, 아이디, 대여일, 반납예정일(대여일+7)을 조회하시오.

SELECT MEMBER_NAME AS "이름", MEMBER_ID AS "아이디", RENT_DATE AS "대여일", RENT_DATE + 7 AS "반납예정일"
FROM TB_MEMBER
JOIN TB_RENT ON (MEMBER_NO = RENT_MEM_NO)
WHERE MEMBER_ID = (SELECT MEMBER_ID
                    FROM TB_MEMBER M 
                    JOIN TB_RENT ON (MEMBER_NO = RENT_MEM_NO)
                    GROUP BY MEMBER_ID, MEMBER_NAME
                    HAVING COUNT(*) = 2);