LIBRARY_SJ.sql

CREATE TABLE BOOK(
    BOOK_SEQ DECIMAL PRIMARY KEY,
    TITLE VARCHAR2(40),
    AUTHOR VARCHAR2(20),
    COMPANY VARCHAR2(20)
);
CREATE TABLE USERLIST(
    USER_ID VARCHAR2(20) PRIMARY KEY,
    USER_PW VARCHAR2(20),
    USER_NAME VARCHAR2(20),
    BIRTH VARCHAR2(20),
    R_LEVEL VARCHAR2(20),
    DISCOUNT_RATE DECIMAL,
    RENT VARCHAR2(20)
);
CREATE TABLE RENT(
    RENT_SEQ DECIMAL PRIMARY KEY,
    BOOK_SEQ DECIMAL,
    USER_ID VARCHAR2(20),
    RENT_DATE VARCHAR2(20),
    RETURN_DATE VARCHAR2(20),
    OVERDUE_MONEY VARCHAR2(20)
);

CREATE SEQUENCE BOOK_SEQ START WITH 1000;

ALTER TABLE RENT ADD CONSTRAINT RENT_FK_BOOK_SEQ FOREIGN KEY (BOOK_SEQ) REFERENCES BOOK (BOOK_SEQ);
ALTER TABLE RENT ADD CONSTRAINT RENT_FK_USER_ID FOREIGN KEY (USER_ID) REFERENCES USERLIST (USER_ID);

SELECT USER_ID, R_LEVEL, (DISCOUNT_RATE||'%') DIS, RENTBAN FROM USERLIST;

commit;

INSERT INTO BOOK (BOOK_SEQ, TITLE, AUTHOR, COMPANY)
VALUES (BOOK_SEQ.NEXTVAL, 'TITLE', 'AUTHOR', 'COMPANY');

INSERT INTO USERLIST (USER_ID, USER_PW, USER_NAME, BIRTH, R_LEVEL, DISCOUNT_RATE, RENTBAN)
VALUES ('TEST6', 'TEST6', 'TEST6', '080806-2', '6', '0' , 'NONE');

DELETE FROM USERLIST WHERE USER_ID LIKE 'TEST%';
UPDATE BOOK SET BOOK_SEQ = 1000
WHERE BOOK_SEQ LIKE 999;

SELECT 
    B.BOOK_SEQ BOOKSEQ,
    B.TITLE, 
    B.AUTHOR,
    B.COMPANY,
    R.USER_ID,
    R.RENT_DATE
FROM BOOK B
    JOIN RENT R
        ON B.BOOK_SEQ = R.BOOK_SEQ
WHERE B.RENTABLE LIKE 'UNABLE'
;

ALTER TABLE BOOK ADD (RENTABLE VARCHAR2(20));
ALTER TABLE USERLIST RENAME COLUMN RENT TO RENTBAN;