Oracle Database Assignment Help | Practice Set

a) Find the names of all authors of books published by "Penguin" that cost more than $10. Make sure each name is included only once in the result.

b) Find the title, publisher and price for all books with an unknown author.

c) The library is setting a summer reading program, where each book values a number of points, based on its length; the formula to determine points for a book is number of pages / 100, rounded up to the nearest integer. Calculate the number of points for each book and list it together with the book id and title. Name the calculated column "no_of_points".

d) Find the title and publisher of all books that are priced between 3 and 6 dollars. Requirements:

- All titles from each publisher must be reported consecutively, and sorted alphabetically from A to Z.

- Publisher names must be all shown in capital letters.

- The report must have the 3 columns: publisher, title, and a new calculated column that should be named "Selling price". A partial result is shown below:

e) How many copies of the book titled "The Lost Tribe" are owned by the library branch whose name is "Newport Branch"? Report the library branch id, name, and number of copies of the book. Separate join conditions from where conditions.

f) Find the card number and name of all members who have borrowed books from the Alexandria library branch. Requirements:

- List borrowers ordered by their card number.

- Make sure each name is included only once in the result.

- Separate join conditions from where conditions.

g) Find the card numbers of all library members who have not borrowed any books from the library. You must use set operations to write this query.

Solution:

Query a): select author

from book

where publisher='Penguin' AND price>10;

output:

AUTHOR

Stephen King

Silas Lambert

Stephen King

Query b):

select TITLE,PUBLISHER,PRICE from book where AUTHOR='unknown';

output:

Query c):

Select book_id, title, (no_of_pages/100) as no_of_points from book;

output:

Query d):

Select Upper(publisher), title,'Selling_price' || ' ' ||'$' || price as Selling_price from book where price between 3 and 6;

output:

Query e):

select book_copies.branch_id, book.title, book_copies.no_of_copies from book

inner join book_copies on book.book_id = book_copies.book_id

inner join library_branch on library_branch.branch_id = book_copies.branch_id where library_branch.branch_name = 'Newport Branch' and book.title = 'The Lost Tribe';

output:

There is no data present for branch name branch_name = 'Newport Branch';

So no row will selected for this.

Query f):

select Borrower.card_no, Borrower.name

from Borrower

inner join book_loans on book_loans.card_no = borrower.card_no

inner join book_copies on book_copies.book_id = book_loans.book_id and book_copies.branch_id = book_loans. branch_id

inner join library_branch on library_branch.branch_id = book_copies.branch_id

where library_branch.branch_name='Alexandria';

output

CARD_NO NAME

6 Tim Tegulpas

Last Query (g) you can try itself.

Their are many website that are providing database assignment help not have experts that have deep knowledge in specific subjects. We are totally focused on coding related projects, assignments and homework.

If you need any database related help then send your requirement details at:

realcode4you@gmail.com

Here you get all database related script without any plagiarism issues.