Question

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.

Answer 1

select * from book where Publish_date >'1978-1-1' and Book_title LIKE '%Sea%';

UPDATE book SET Quantity= 0 WHERE Publish_date<'1975-1-1';

select AVG(Quantity) from book where Publish_date<'1980-1-1';

select Quantity number,Autor from book;

select Author from book where COUNT(Quantity number)>1;


select loan_id from loan_event,customer where customer.customer_id=loan_event.customer_id and customer.customer_name='john';

select loan_date from loan_event,customer where customer.customer_id=loan_event.customer_id and customer.customer_name='john';

select loan_event.loan_id,customer.customer_id from loan_event,customer where customer.customer_id=loan_event.customer_id ;

select book.Book_title from book,customer,loan_detail where customer.customer_id = loan_detail.customer_id and
loan_detail.book_id = book.Book_id;

SELECT customer.customer_name,
SUM(loan_event.paymentnumber)
FROM customer,loan_event
WHERE customer.customer_id=loan_event.customer_id
GROUP BY customer.customer_name,loan_event.paymentnumber;