Given:

Create table Book (

Book_id integer,

Book_title varchar(50),

Author varchar(50),

Publish_date date,

Type varchar(30),

Edition number,

Quantity number,

Primary key (Book_id)

);

insert into Book values (1,'The Old Man and the Sea','Hemingway' ,date '1978-1-1','hardcopy',3,2);

insert into Book values (2,'The Old Man and the Sea','Hemingway' ,date '1979-1-1','hardcopy',4,1);

insert into Book values (3,'The Old Man and the Sea','Hemingway' ,date '1980-1-1', 'hardcopy',5,10);

insert into Book values (4,'A Farewell to Arms','Hemingway' ,date '1986-1-1','hardcopy',2,18);

insert into Book values (5,'For Whom the Bell Tolls','Hemingway' ,date '1980-1-1','hardcopy',8,1);

insert into Book values (6,'The Great Gatsby','Fitzgerald' ,date '1978-1-1', 'hardcopy',2,3);

insert into Book values (7,'This Side of Paradise','Fitzgerald' ,date '1973-1-1','hardcopy',1,1);

insert into Book values (8,'Tender is the Night','Fitzgerald' ,date '1979-1-1', 'hardcopy',6,18);

insert into Book values (9,'The Age of Innocence','Wharton' ,date '2012-1-1','hardcopy',3,5);

create table customer (

customer_id integer,

customer_name varchar(50),

primary key (customer_id));

insert into customer values (1,'John');

insert into customer values (2, 'Mary');

insert into customer values (3, 'Jake');

insert into customer values (4, 'Sam');

create table loan_event (

loan_id integer,

customer_id integer,

loan_date date,

payment number,

primary key (loan_id),

foreign key (customer_id) references customer(customer_id));

insert into loan_event values (1,1, date '2016-4-1', 0);

insert into loan_event values (2,1, date '2016-3-1', 3.5);

insert into loan_event values (3,1, date '2016-2-1', 0.5);

insert into loan_event values (4,2, date '2016-1-1', 0);

insert into loan_event values (5,3, date '2016-4-11', 0);

create table loan_detail (

loan_id integer,

book_id integer,

due_date date,

return_date date,

primary key(loan_id, book_id),

foreign key(loan_id) references loan_event(loan_id),

foreign key (book_id) references book(book_id));

insert into loan_detail values (1,1, date '2016-4-10', date '2016-4-5');

insert into loan_detail values (1,4, date '2016-4-10', date '2016-4-5');

insert into loan_detail values (1,5, date '2016-4-10', date '2016-4-10');

insert into loan_detail values (2,2, date '2016-3-10', date '2016-3-15');

insert into loan_detail values (3,3, date '2016-2-10', date '2016-3-15');

insert into loan_detail values (4,6, date '2016-1-10', date '2016-2-15');

insert into loan_detail values (4,7, date '2016-1-10', date '2016-2-15');

insert into loan_detail values (4,8, date '2016-1-10', date '2016-2-15');

insert into loan_detail values (5,9, date '2016-4-10', date '2016-4-15');

Give Sql Oracle statements for the following:

List the titles of books published after January 1, 1978 and contain the word Sea in the title.

Update the Quantity of all books published before January 1, 1975 to 0.

Find out the average quantity of books published before January 1, 1980.

List the number of books published by each author. Please return the number of books and author name. (Here each edition of a book is considered one book)

List the names of authors who have published more than one book in the database. (Here each edition of a book is considered one book)

Return the IDs of the loans made by a customer named John.

Return the loan_date of the loans made by John.

Return the names of the customers and the IDs of their loans, including those who do not have loans.

List the titles of all the books borrowed by John.

List the names of the customers and their total payment. Hint: 1) Total payment of a member can be computed as the sum of the payments for all the loans made by that customer. 2) Use join and group by.