2018

--- Q1 ---
create table BookLoan (
	loanID int,
	borrowerName varchar(25),
	bookTitle varchar(25),
	returnDate date
	primary key (loanID),
	foreign key (borrowerName) references Borrower (name),
	foreign key (bookTitle) references Book (title)
);

--- Q2 ---
select borrowerName
from BookLoan
where BookLoan.bookTitle = Book.title
and Book.auther = 'Hardy';

--- Q3 ---
update BookLoan 
set returnDate = '17-APR-18'
where LoanID = 7;

--- Q4 ---
select BookLoan.borrowerName, sum(Book.cost)
from BookLoan
where BookLoan.bookTitle = Book.title
group by BookLoan.borrowerName
having sum(Book.cost) > 40;

--- Q5 ---
create procedure loanDetails (aloanID BookLoan.loanID%type) as
cursor list is
select BookLoan.borrowerName, BookLoan.bookTitle
from BookLoan
where BookLoan.loanID = aloanID;
aname BookLoan.borrowerName%type
atitle BookLoan.bookTitle%type
begin
open list;
loop
	fetch list into aname, atitle;
	exit when list%notfound;
	dbms_output.put_line('name: '||aname||' title:'||atitle);
end loop;
close list;
end;
/

--- Q6 ---
create trigger updateNumLoans
after insert on BookLoans
for each row
begin
	update Book set noOfLoans = noOfLoans + 1
	where bookTitle = title;
end;
/ 

--- Q7 --- 
create type OverdueLoan as object (
	dateBorrowed date,
	returnDate date,
	member function daysOverDue return decimal
) not final;
/

create type InterLibraryOverdueLoan under OverdueLoan (
	libraryName varchar(25)
) final;
/

--- Q8 ---
2000 // borrowerName = 'Booch'
ans + 40 + (40 * 100) // title = bookTitle
ans + (40 * 100) // project columns
ans = 10040

--- Q9 ---
create view userView as
select loanID, bookTitle, author
from Book, BookLoan
where title = bookTitle
AND borrowerName = ‘Booch’;

grant select on userView to userName;