Create_Tables_With_Data

/**BOOK**/
CREATE TABLE BOOK (
       BOOKCODE     INT NOT NULL,
       TITLE        CHAR(40) NOT NULL,
       IMPRINT      CHAR (100) NOT NULL,
       BOOK_DESCRIPTION CHAR(1000) NOT NULL,
       BOOKTYPE     CHAR(100) NOT NULL,
       BOOKPRICE    NUMERIC(10,2) NOT NULL
       CONSTRAINT BOOK_PK   PRIMARY KEY (BOOKCODE)
);

CREATE TABLE AUTHOR (
        AUTHORNUM   INT NOT NULL    IDENTITY (0001,1),
        AUTHORNAME    CHAR(30) NOT NULL,
        AYEAR       INT NOT NULL,
        ENTRIES     INT NULL
        CONSTRAINT AUTHOR_PK    PRIMARY KEY (AUTHORNUM)
);

CREATE TABLE AUTHOR_BOOK (
        AUTHORNUM   INT NOT NULL,
        BOOKCODE    INT NOT NULL
        CONSTRAINT AUTHOR_BOOK_PK   PRIMARY KEY (AUTHORNUM, BOOKCODE),
        CONSTRAINT AUTHOR_BOOK_FK1  FOREIGN KEY (AUTHORNUM)
                        REFERENCES AUTHOR(AUTHORNUM),
        CONSTRAINT AUTHOR_BOOK_FK2  FOREIGN KEY (BOOKCODE)
                        REFERENCES BOOK(BOOKCODE)


);

CREATE TABLE CUSTOMER (
        CUSTOMERID  INT NOT NULL,
        FIRSTNAME   CHAR(20) NOT NULL,
        LASTNAME    CHAR(25) NOT NULL,
        CUSTADDRESS CHAR(100) NOT NULL,
        PHONE       INT NOT NULL,
        EMAIL       CHAR(30) NOT NULL,
        CUSTTYPE    CHAR(10) NOT NULL,
        CUSTSTATUS  CHAR(20) NOT NULL
        CONSTRAINT CUSTOMER_PK  PRIMARY KEY (CUSTOMERID)
);

CREATE TABLE BORROW_SCHEDULE (
        BORROWORDER INT NOT NULL,
        BORROWNUMBER INT NOT NULL,
        CUSTOMERID  INT NOT NULL,
        BOOKCODE    INT NOT NULL,
        BORROWDATE  DATE NOT NULL,
        DUEDATE DATE NULL,
        RETURNDATE DATE NULL,
        DIFFDATE INT NULL,
        FINE INT NULL,

        CONSTRAINT BORROW_SCHEDULE_PK   PRIMARY KEY (BORROWORDER,BORROWNUMBER),
        CONSTRAINT BORROW_SCHEDULE_FK1  FOREIGN KEY (CUSTOMERID)
            REFERENCES CUSTOMER (CUSTOMERID),
CONSTRAINT BORROW_SCHEDULE_FK2  FOREIGN KEY (BOOKCODE)
            REFERENCES BOOK(BOOKCODE)
ON UPDATE CASCADE
                ON DELETE NO ACTION

);

CREATE TABLE MEMBER (
        CUSTOMERID  INT NOT NULL,
        MEMBERID    INT NOT NULL,
        MEMBERTYPE  CHAR(20) NOT NULL
        CONSTRAINT MEMBER_PK    PRIMARY KEY (CUSTOMERID),
        CONSTRAINT MEMBER_FK    FOREIGN KEY (CUSTOMERID)
                            REFERENCES  CUSTOMER(CUSTOMERID)
);


CREATE TABLE STUDENT (
        CUSTOMERID  INT NOT NULL,
        STUDENTID   NUMERIC(10,0) NOT NULL,
        FACULTY     CHAR(20) NOT NULL,
        YEARS       INT NOT NULL,
        DEGREE      CHAR (20) NOT NULL
        CONSTRAINT STUDENT_PK   PRIMARY KEY (CUSTOMERID),
        CONSTRAINT STUDENT_FK   FOREIGN KEY (CUSTOMERID)
                            REFERENCES  CUSTOMER (CUSTOMERID)
);


/*- ตาราง AUTHOR*/
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Jarin Jingjung', 1994, 10);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Tom Condominium', 1992, 2);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Bella Ranee', 1999, 4);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Pachara Hormones', 2005, 1);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Harry Hela', 2015, 3);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Britney Smear', 2009, 5);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Taylor Smurf', 2012, 6);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Maroon Four', 1994, 1);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Bueno Maimar', 2013, 7);
INSERT INTO AUTHOR(AuthorName, AYear, Entries)
    VALUES('Finnie Bunny', 2000, 2);
/*- ตาราง BOOK*/
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1001, 'Reach for the Stars', 'Manhattan University Press', 'For children use', 'Cartoon', 200.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1002, 'Papercity', 'Korsor Pattana Publishing', 'For all ages', 'Drama', 250.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1003, 'Marry Potter', 'Jammaisai Publishing', 'Recommend for teenagers', 'Science Fiction', 650.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1004, 'Ghost Town', 'Chiang Rai Press', 'For over 20 only', 'Horror', 75.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1005, 'First 100 Words', 'Blue Star Books', 'Recommend for English learners', 'Education', 590.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1006, 'The Pretty', 'Priddy Books', 'Recommend for Women', 'Motivational Self-Help', 150.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1007, 'Do you want to build a snowman?', 'Poomson', 'For children use', 'Cartoon', 60.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1008, 'SQL Introduction and Application', 'Threshold Editions', 'For Computer Engineer', 'Education', 1050.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1009, 'To Kill a Stress', 'Jammaisai Publishing', 'General', 'Motivational Self-Help', 240.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(1010, 'Principle of Accounting', 'Chiang Rai Press', 'For Teaching Accounting Students', 'Education', 350.00);
INSERT INTO BOOK(BookCode, Title, Imprint, Book_Description, BookType, BookPrice)
    VALUES(2009, 'ASDFGH', 'HGKJHKH', 'For children use', 'Cartoon', 150.00);
/*- ตาราง AUTHOR_BOOK (Intersection Table)*/
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0001, 1003);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0001, 1004);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0002, 1004);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0004, 1001);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0003, 1002);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0005, 1006);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0005, 1007);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0007, 1005);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0010, 1006);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0006, 1003);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0008, 1010);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0009, 1001);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0010, 1008);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0003, 1009);
INSERT INTO AUTHOR_BOOK(AuthorNum, BookCode)
    VALUES(0005, 1008);
/*- ตาราง CUSTOMER*/
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3001, 'Krisana', 'Nenononee', '1588/40 Makkasan Ratchwewi Bangkok 10400', 0908933402, 'Knonee@hotmail.com', 'Student', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3002, 'Katy', 'Smith', '123 Jatujak Park Bangkok 11100', 0811233542, 'KS@gmail.com', 'Member', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3003, 'Thana', 'Pad', '55 Sapan Kwai Bangkok 10330', 0955124792, 'Thanapad@hotmail.com', 'Student', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3004, 'Pentor', 'Paiyangnguan', '128 Bang Bar Bangna Bangkok 11200', 0908933333, 'pp@hotmail.com', 'Member', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3005, 'Pattanon', 'Seethongprasert', '14/6 Lardprao Bangkok 10220', 0863462462, 'P_N@gmail.com', 'Student', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3006, 'Omyim', 'Minteesud', '5 Phayathai Bangkok 10330', 0934454253, 'Ooooon@hotmail.com', 'Member', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3007, 'Kittimate', 'Lookom', '11/12 Khlong Toei Bangkok 10320', 0936356745, 'KL_happy@gmail.com', 'Student', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3008, 'Ningnong', 'Nikko', '34 Rama9 Bangkok 11000', 0945463267, 'Ningning@hotmail.com', 'Member', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3009, 'Kunanon', 'Auhehe', '62 Don Muang Bangkok 10310', 0953456402, 'handsomeau@hotmail.com', 'Student', 'Avaliable');
INSERT INTO CUSTOMER(CustomerID, FirstName, LastName, CustAddress, Phone, Email, CUSTTYPE, CUSTSTATUS)
    VALUES(3010, 'Winwinds', 'Kittitouch', '73 Rama IX Bangkok 10390', 0899963452, 'winwindslovelove@hotmail.com', 'Member', 'Avaliable');

/*- ตาราง MEMBER*/
INSERT INTO MEMBER(CustomerID, MemberID, MemberType)
    VALUES(3002, 15001, 'Temporary');
INSERT INTO MEMBER(CustomerID, MemberID, MemberType)
    VALUES(3004, 15002, 'Permanent');
INSERT INTO MEMBER(CustomerID, MemberID, MemberType)
    VALUES(3006, 15003, 'Temporary');
INSERT INTO MEMBER(CustomerID, MemberID, MemberType)
    VALUES(3008, 15004, 'Permanent');
INSERT INTO MEMBER(CustomerID, MemberID, MemberType)
    VALUES(3010, 15005, 'Temporary');

/*- ตาราง STUDENT*/
INSERT INTO STUDENT(CustomerID, StudentID, Faculty, Years, Degree)
    VALUES(3001, 5631134523, 'Science', 3, 'Undergraduate');
INSERT INTO STUDENT(CustomerID, StudentID, Faculty, Years, Degree)
    VALUES(3003, 5580024521, 'Engineer', 4, 'Graduate');
INSERT INTO STUDENT(CustomerID, StudentID, Faculty, Years, Degree)
    VALUES(3005, 5736535130, 'Medicine', 2, 'Undergraduate');
INSERT INTO STUDENT(CustomerID, StudentID, Faculty, Years, Degree)
    VALUES(3007, 5882312726, 'Commerce', 1, 'Graduate');
INSERT INTO STUDENT(CustomerID, StudentID, Faculty, Years, Degree)
    VALUES(3009, 5843342527, 'Education', 1, 'Undergraduate');

INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000001,1,3001, 1001, '2015-11-03')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000002,1,3002, 1002, '2015-11-07')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000003,1,3010, 1003, '2015-11-02')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000004,1,3008, 1004, '2015-11-01')
 INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
 VALUES (2000005,1,3006, 1005, '2015-11-15')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000006,1,3004, 1006, '2015-11-10')
 INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
 VALUES (2000007,1,3009, 1007, '2015-11-03')
 INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
 VALUES (2000008,1,3003, 1008, '2015-11-06')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000009,1,3004, 1009, '2015-11-09')
INSERT INTO BORROW_SCHEDULE (BORROWORDER,BORROWNUMBER,CUSTOMERID,BOOKCODE,BORROWDATE)
VALUES (2000006,2,3004, 2009, '2015-11-18')