Question: Instruction: Design, develop, and implement the specified queries for the following problems. Experiment: Consider the following schema for a Library Database: BOOK(Book_id, Title, Publisher_Name, Pub_Year)

Instruction: Design, develop, and implement the specified queries for the following problems.

Experiment:

Consider the following schema for a Library Database:
BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_idu, Programme_id, No-of_Copies)
BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME(Programme_id, Programme_Name, Address)

Write SQL queries to-
1.Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each Programme, etc.

2.Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2020 to Jun 2021.

2.Delete a book in the BOOK table. Update the contents of other tables to reflect this data manipulation operation.

4.Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.

5.Create a view of all books and the number of copies that are currently available in the Library.

Step by Step Solution

★★★★★

3.50 Rating (153 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

SOLUTION Retrieve details of all books in the library id title name of publisher authors number of c... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Document Format (2 attachments)

63640f0fb7a3d_238985.pdf

180 KBs PDF File

63640f0fb7a3d_238985.docx

120 KBs Word File

Students Have Also Explored These Related Databases Questions!

For each book title with more than 3 copies, list the names of library members who have borrowed them.

Write an SQL query to list each customer who bought at least one product that belongs to product line Basic in March 2015. List each customer only once.

Create a SQL query to satisfy each of the tasks below. Save each SQL query using the name associated with the task in the table below You must create an sql code for each tasks and ensure when...

Design an MPC controller for the reactor/flash unit plant and test it using a simulation of the linearized model of this plant. For purposes of this exercise, first design and implement a PI...

It's amazing how much difference there is in the way proposals are presented at two different firms," said John Woods to his assistant, Pete Madsen, as he pointed to the stack of capital investment...

The finishing department is the last of four sequential production departments for Kowalski Graphics, Inc. The companys other production departments are design, layout, and printing. The finishing...

How does an audit manager or organizational management design, develop, and implement career development plan?

Tuff Wheels was getting ready to start its development project for a new product to be added to its small motorized vehicle line for children. The new product is called the Kiddy Dozer. It will look...

"You mentioned culture! Now there's a human relations cult if I ever heard one. Human resource and organization design people are great, and out of their work have come some of the most valuable new...

On June 6, 2020, Lindhardt Chocolates Ltd. purchased assembly line equipment for $426,000. Lindhardts management estimated that the equipment had a useful life of four years and an estimated residual...

The spread of the bank is 2.00%, the cost rate on funds is 3.20%, If the banks eaming assets are $1,000,000, then the interest income of the bank is DONT USE COMMA SEPARATORS. PRESENT YOUR ANSER...

1. (20 points) Consider the dataset given in the table below. The data corre- sponds to US national unemployment rates for males (y;) and females (x). Males Females 2.9 4 7.7 7.4 4.9 5 7.9 7.2 9.8...

BiggerBetter Widgets Co. makes World-Class Widgets using a relatively simple process (see flow chart below) with the following parameters. Their factory works from 9 AM to 5 PM; all operations shut...

You invest $138 today. After 11 years will you have $229. What simple interest rate did you earn on your investment? Answer as a decimal. Round to the nearest ten thousandth.

2. (10 points) The curve is the intersection of the plane x = -1 and the cone z = x + y. Find the curvature of y at the point (-1,2,5). 22

[6] Question 4: [20 Marks] The signal x(t) has the Laplace transform X(s) = 14 s(s+4) 2 [6] (a) Find the LT of y(t) = x(21-3) [8] (b) Find the LT of y(1) = tex(1) (c) Find the LT of y(t) = 1...

Company A is 30% financed by debt. It issues bonds with a 7% coupon, fifteen year maturity, $1,000 par value, and a market price of $1,170. Equity beta is 1.2, the market risk premium is equal to...

Cleaning Service Company's Trial Balance on December 31, 2020 is as follows: Account name Debit Credit Cash 700 Supplies Pre-paid insurance Pre-paid office rent Equipment Accumulated depreciation -...

Consider the following relation for published books: BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher) Author_affil referes to the affiliation of the author. Suppose the...

Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS, ...) and LOANS (CAR_LOANS, HOME_LOANS, ...)....

On are cent interest payment date, a bonds price exceeded its face value. If the prevailing market rate of return does not change thereafter, will the bonds premium be different on later interest...

Assuming that the bond issuer does not default on any payments, is it possible to lose money on a bond investment? Discuss briefly.

A $255,000 amount from an RRSP is used to purchase an annuity paying $6000 at the end of each quarter. The annuity provides an annually compounded rate of return of 6%. a. What will be the amount of...