UAS

CREATE table author(
author_id char(5) constraint pk_aut primary key,
author_name varchar2(30),
birth_date date,
birth_place varchar2(30)
);

CREATE table book(
book_id char(5) constraint pk_book primary key,
book_title varchar2(30),
publish_date date,
genre varchar2(40),
pages varchar2(30),
stock char(5) constraint ck_stock check(stock > 0),
author_id char(5) constraint fk_aut references author(author_id),
publisher_id char(5) constraint fk_pub references publisher(publisher_id)
);

CREATE TABLE publisher(
publisher_id char(5) constraint pk_pub primary key,
publisher_name varchar2(100),
headquarter varchar2(100),
founded_year char(4),
founder varchar2(100)
);

CREATE TABLE borrower(
borrower_id char(5) constraint pk_bor primary key,
borrower_name varchar2(30),
birth_date date,
address varchar2(30),
phone varchar2(20) constraint bor_phone unique,
email varchar2(30) constraint bor_email unique
);

CREATE TABLE transaction(
transaction_id char(5) constraint pk_tran primary key,
transaction_date date default sysdate,
return_date date default sysdate+7,
borrower_id char(5) constraint fk_bor references borrower(borrower_id),
book_id char(5) constraint fk_book references book(book_id),
librarian_id char(5) constraint fk_lib references librarian(librarian_id)
);

CREATE TABLE librarian(
librarian_id char(5) constraint pk_lib primary key,
librarian_name varchar2(30),
birth_date date,
address varchar2(30),
phone varchar2(20) constraint lib_phone unique,
email varchar2(30) constraint lib_email unique
);

create sequence author_author_id_seq
 increment by 1 start with 1 nocache nocycle;

create sequence publisher_publisher_id_seq
 increment by 1 start with 1 nocache nocycle;

create sequence book_book_id_seq
 increment by 1 start with 1 nocache nocycle;

create sequence borrower_borrower_id_seq
 increment by 1 start with 1 nocache nocycle;

create sequence librarian_librarian_id_seq
 increment by 1 start with 1 nocache nocycle;

create sequence transaction_transaction_id_seq
 increment by 1 start with 1 nocache nocycle;


insert into Author
 values('AU'||lpad(author_author_id_seq.nextval,3,0),'Robert Muir','23-MAY-62','Frenchs Forest');
insert into Author
 values('AU'||lpad(author_author_id_seq.nextval,3,0),'Bruce Weinstein',NULL,NULL);
insert into Author
 values('AU'||lpad(author_author_id_seq.nextval,3,0),'Craig Glenday','31-MAY-73','Dundae');
insert into Author
 values('AU'||lpad(author_author_id_seq.nextval,3,0),'Stephenie Morgan','24-DEC-73','Hartford');
insert into Author
 values('AU'||lpad(author_author_id_seq.nextval,3,0),'Jeffrey Kinney','19-FEB-71','Fort Washington');


 insert into publisher
 	values ('PU'||LPAD(publisher_publisher_id_seq.nextval,3,0),'William Morrow and Company','New York','1926','William Morrow');
 insert into publisher
 	values ('PU'||LPAD(publisher_publisher_id_seq.nextval,3,0),'Jim Pattison Group','Vancouver','1961','Jim Pattison, Glen Clark');
 insert into publisher
 	values ('PU'||LPAD(publisher_publisher_id_seq.nextval,3,0),'Little, Brown and Company','New York','1837','Charles Coffin Little, James Brown');
 insert into publisher
 	values ('PU'||LPAD(publisher_publisher_id_seq.nextval,3,0),'Amulet Books','New York','2004','Harry N. Abrams, Howard Reeves, Susan Van Metre');
 insert into publisher
 	values ('PU'||LPAD(publisher_publisher_id_seq.nextval,3,0),'Penguin Group','City of Westminster','1935','Allen Lane');


insert into Borrower
 values('BR'||lpad(borrower_borrower_id_seq.nextval,3,0),'Jeffry Steward Witarsah','24-JUL-93','Gading Serpong','08170110049','jeffrysw@yahoo.com');
insert into Borrower
 values('BR'||lpad(borrower_borrower_id_seq.nextval,3,0),'Stevian Hebie Valentino','22-SEP-92','Tangerang','08150310074','stevianhv@gmail.com');
insert into Borrower
 values('BR'||lpad(borrower_borrower_id_seq.nextval,3,0),'Vincencius Kevin','04-SEP-92','Bengkulu','08780110064','vincenciusk@yahoo.com');
insert into Borrower
 values('BR'||lpad(borrower_borrower_id_seq.nextval,3,0),'Billy Permana Halim','21-OCT-92','Bumi Serpong Damai','08190110061','billyph@gmail.com');
insert into Borrower
 values('BR'||lpad(borrower_borrower_id_seq.nextval,3,0),'Idul Futra','06-APR-92','Riau','08160110001','idulf@hotmail.com');


insert into Librarian(librarian_id,librarian_name,birth_date,address,phone,email)
values('LB'|| lpad(librarian_librarian_id_seq.nextval,3,0),'Steven Young','26-Dec-91','Bengkulu','08780110062','steveny@gmail.com');
insert into Librarian(librarian_id,librarian_name,birth_date,address,phone,email)
values('LB'|| lpad(librarian_librarian_id_seq.nextval,3,0),'Lisa Amanda','11-Apr-92','Jakarta','08170310021','lisaa@yahoo.com');
insert into Librarian(librarian_id,librarian_name,birth_date,address,phone,email)
values('LB'|| lpad(librarian_librarian_id_seq.nextval,3,0),'Marsel Widjaja','20-Aug-92','Tangerang','08780110090','marselw@gmail.com');
insert into Librarian(librarian_id,librarian_name,birth_date,address,phone,email)
values('LB'|| lpad(librarian_librarian_id_seq.nextval,3,0),'Giam Cen Jiang','01-Dec-92','Pekalongan','08150110122','giamcj@gmail.com');
insert into Librarian(librarian_id,librarian_name,birth_date,address,phone,email)
values('LB'|| lpad(librarian_librarian_id_seq.nextval,3,0),'Joko Haryanto','07-Jan-92','Serang','08160110137','jokoh@yahoo.com');


insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'Diary of a Wimpy Kid: The Third Wheel', '13-NOV-12', 'Comedy', '217', '5', 'AU005', 'PU004');
insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'Guinness World Records 2013', '11-SEP-12', 'Information', '288', '3', 'AU003', 'PU002');
insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'Breaking Dawn', '02-AUG-08', 'Paranormal Romance, Young-Adult Fiction', '756', '2', 'AU004', 'PU003');
insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'Eclipse', '07-AUG-07', 'Young-Adult, Fantasy, Romance', '629', '2', 'AU004', 'PU003');
insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'The Ultimate Ice Cream Book', '19-MAY-99', 'Cookbooks, Food and Drink', '256', '7', 'AU002', 'PU001');
insert into Book
values('BK'||lpad(book_book_id_seq.nextval,3,0), 'Sydney Seafood School Cookbook', '24-OCT-12', 'Food Preparation, Seafood, Recipes', '240', '5', 'AU001', 'PU005');


insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'07-Nov-12','14-Nov-12','BR003','BK005','LB001');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'10-Sep-12','17-Sep-12','BR002','BK002','LB004');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'13-Sep-12','20-Sep-12','BR005','BK003','LB003');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'02-Oct-12','09-Oct-12','BR001','BK003','LB002');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'23-Oct-12','30-Oct-12','BR004','BK006','LB002');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'30-Aug-12','06-Sep-12','BR001','BK004','LB001');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'27-Jul-12','03-Aug-12','BR003','BK006','LB003');
insert into Transaction(transaction_id,transaction_date,return_date,borrower_id,book_id,librarian_id)
values('TR' || lpad(transaction_transaction_id_seq.nextval,3,0),'08-Aug-12','15-Aug-12','BR005','BK002','LB004');

CREATE OR REPLACE VIEW transaction_view AS
 SELECT TO_CHAR(tran.transaction_date,'fmdd Month rrrr') "Transaction Date",
 bor.borrower_name "Borrower Name", book.book_title "Book Title",
 lib.librarian_name "Librarian Name"
 FROM Transaction tran
 JOIN Borrower bor on tran.borrower_id=bor.borrower_id
 JOIN Librarian lib on tran.librarian_id=lib.librarian_id
 JOIN Book book on tran.book_id=book.book_id
 ORDER BY tran.transaction_date;

SELECT * FROM transaction_view;



SELECT librarian_name as "Librarian Name"
FROM librarian
WHERE librarian_id not in (
SELECT librarian_id from transaction
);

insert into Transaction(transaction_id,borrower_id,book_id,librarian_id)
 values('TR'||lpad(transaction_transaction_id_seq.nextval,3,0),'BR003','BK003','LB001');

update Book
 set stock=stock-1
 where book_title='Breaking Dawn';

 CREATE OR REPLACE VIEW cookbooks AS
 SELECT book.book_title "Book Title", aut.author_name "Author Name", pub.publisher_name "Publisher Name"
 FROM Book book
 JOIN Author aut on book.author_id = aut.author_id
 JOIN Publisher pub on book.publisher_id = pub.publisher_id
 WHERE book.genre LIKE 'Food%' or book.genre LIKE '%Food' or book.genre LIKE '%Food%'
 ORDER BY 1 asc;

 DROP VIEW transaction_view;
 DROP VIEW cookbooks;

 DROP SEQUENCE author_author_id_seq;
 DROP SEQUENCE publisher_publisher_id_seq;
 DROP SEQUENCE book_book_id_seq;
 DROP SEQUENCE borrower_borrower_id_seq;
 DROP SEQUENCE librarian_librarian_id_seq;
 DROP SEQUENCE transaction_transaction_id_seq;

 DROP TABLE transaction;
 DROP TABLE book;
 DROP TABLE author;
 DROP TABLE publisher;
 DROP TABLE borrower;
 DROP TABLE librarian;