CREATE TABLE Books (
BookID NUMBER(10,0) PRIMARY KEY NOT NULL,
BookName VARCHAR2(100 Byte) NOT NULL,
BookCategory VARCHAR2(20 Byte),
Copies NUMBER,
AuthorID NUMBER,
CONSTRAINT Books_FK FOREIGN KEY (AuthorID) REFERENCES
Author(AuthorID)
);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (1, 'A Game of Thrones', 'Fantasy', 20, 6);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (2, 'The Great Gatsby', 'Classic Fiction', 12, 1);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (3, 'The Fault in Our Stars', 'Contemporary', 15, 2);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (4, 'The Da Vinci Codes', 'Thriller', 10, 3);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (5, 'The Girl with the Dragon Tattoo', 'Thriller', 3, 1);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (6, 'Don Quixote', 'Classic Fiction', 5, 1);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (7, 'The Lightning Thief', 'Fantasy', 5, 2);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (8, 'The Hunger Games', 'Contemporary', 15, 4);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (9, 'The Girl with the Dragon Tattoo', NULL, 7, 5);
INSERT INTO BOOKS (BOOKID, BOOKNAME, CATEGORY, COPIES, AUTHORID)
VALUES (10, 'City of Bones', NULL, 6, 2);
CREATE TABLE Author (
AuthorID NUMBER PRIMARY KEY NOT NULL,
AuthorName VARCHAR2 (100 CHAR)
);
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (2, 'Suzanne Collins');
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (5, 'F. Scott
Fitzgerald');
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (4, 'Gillian Flynn');
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (1, 'Harper Lee');
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (3, 'Stieg Larsson');
INSERT INTO AUTHOR (AUTHORID, AUTHORNAME) VALUES (6, 'George Orwell');
CREATE TABLE Members (
MemberID NUMBER PRIMARY KEY NOT NULL,
MemberFirstName VARCHAR2(20 char) NOT NULL,
MemberLastName VARCHAR2(20 char) NOT NULL,
Gender VARCHAR2(6 char) NOT NULL,
MemberLocation VARCHAR2(100 char)
);
INSERT INTO MEMBERS (MEMBERID, FIRSTNAME, LASTNAME, GENDER,
LOCATION) VALUES (2, 'Wendell', 'Cruz', 'M', 'Las Pinas');
INSERT INTO MEMBERS (MEMBERID, FIRSTNAME, LASTNAME, GENDER,
LOCATION) VALUES (5, 'Alex', 'Santos', 'M', 'Manila');
INSERT INTO MEMBERS (MEMBERID, FIRSTNAME, LASTNAME, GENDER,
LOCATION) VALUES (1, 'Jenra', 'Santos', 'F', 'Makati');
INSERT INTO MEMBERS (MEMBERID, FIRSTNAME, LASTNAME, GENDER,
LOCATION) VALUES (3, 'Roy', 'Dela Cruz', 'M', 'Makati');
INSERT INTO MEMBERS (MEMBERID, FIRSTNAME, LASTNAME, GENDER,
LOCATION) VALUES (4, 'Anna', 'Mendoza', 'F', 'Alabang');
CREATE TABLE Borrow (
BorrowID NUMBER PRIMARY KEY NOT NULL,
BookID NUMBER NOT NULL,
MemberID NUMBER NOT NULL,
CONSTRAINT Borrow_FK1 FOREIGN KEY (BookID) REFERENCES Books(BookID),
CONSTRAINT Borrow_FK2 FOREIGN KEY (MemberID) REFERENCES
Members(MemberID)
);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (4, 1, 2);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (6, 5, 1);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (1, 3, 1);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (3, 2, 5);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (5, 7, 3);
INSERT INTO BORROW (BORROWID, BOOKID, MEMBERID) VALUES (2, 1, 4);
SELECT
[Link],
[Link]
FROM
BOOKS
JOIN
AUTHOR
ON
[Link] = [Link];
SELECT
[Link],
[Link]
FROM
Borrow
JOIN
Members
ON
[Link] = [Link]
JOIN
Books
ON
[Link] = [Link];