Question: Consider a 'Book-Shop' database of three tables as given below: Books (isbn, title, author, qty_in stock, price, year published) Customers (cid, cname, address) Orders

Consider a 'Book-Shop' database of three tables as given below: Books (isbn,

Consider a 'Book-Shop' database of three tables as given below: Books (isbn, title, author, qty_in stock, price, year published) Customers (cid, cname, address) Orders (ordernum, isbn, cid, cardnum, aty, order date, ship_date) Assume your own data for the above tables and write queries as given below: 1. Write SQL statements for creation of all the three tables. 2. Write SQL statement to search from the table books, view only the columns- author name, title and ISBN 3. Write SQL statement view customer name and the books he got in a horizontal manner like CustomerName - All books [with titles] each customer one got 4. Write SQL statement view the orders for different authors in a horizontal manner like OrderNum- Authors 5. Write SQL statement view customer name, address, order qty, order date, ship date 6. Write SQL statement view total books bought by each customer [with customer name] 7. Write SQL statement view to find total books published in year 2018 [assume 2018 to be one of the field values in Books table] 8. Write SQL statement to view all the books which have been shipped. 9. Write SQL statement view customer name, book title, book price, year_published 10. Write SQL statement to find out qty of books ordered for each author

Step by Step Solution

★★★★★

3.46 Rating (159 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Create Database BookShop 1 Create Table BooksISBN Varchar25Title Varchar50 Author Varchar25Qtyinstoc... 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

Students Have Also Explored These Related Computer Engineering Questions!

Run the MantelHaenszel test for the above data and interpret your results.

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.

Columns 1 through 4 in the table below show the marginal utility, measured in utils, that Ricardo would get by purchasing various amounts of products A, B, C, and D. Column 5 shows the marginal...

Assume that Marcia has created a database with the tables described at the end of Chapter 7: CUSTOMER (Customer, First Name, Last Name, Phone, Email) INVOICE (Invoice Number, CustomerID, Date In,...

Refer tot the annual report for CVS Corporation and the financial statements for Southwest Airline Co. in the Supplement to Chapter 5. Compute the 2008 and 2007 book value per share for both...

The General Mills 2002 Annual Report is reproduced in Appendix B at the end of the text. Required A. Answer the following questions about the General Mills Consolidated Statements of Earnings: 1....

Consider the following information from Priceline.com. Required: 1. Calculate the stockholder payout ratios. 2. Calculate the stockholder profitabilityratios. $43.61/share Stock price Common...

1. Develop DFD fragments for all of the events not documented in Figure. 2. Develop a single DFD that shows processing for all events, using one process for each subsystem and showing all needed data...

Donnen Designs, Inc. is a small manufacturer of womens casual-wear jewelry, including bracelets, necklaces, earrings, and other moderately priced accessory items. Most of their products are made from...

FIFO and LIFO Harrisburg Company is considering changing its inventory valuation method from FIFO to LIFO because of the potential tax savings. However, the management wishes to consider all of the...

Precision Parts is a job shop that specializes in producing electric motor shafts. The average shaft size for the E300 electric motor is 0.55 inch, with a standard deviation of 0.10 inch. It is...

Consider the following examples of economic activities: Purchasing of groceries Massive layoff of employees Decrease in taxes Describe how each of these activities affects government, households, and...

Compute the slope of the line that passes through: ( 4 , - 3 ) and ( 2 , 9 ) .

Labeau Products, Ltd., of Perth, Australia, has $35,000 to invest. The company is trying to decide between two alternative uses for the funds as follows: The company's discount rate is 18%. Required:...

Katayama Company produces a variety of products. One division makes neoprene wetsuits. The divisions projected income statement for the coming year is as follows: Sales (65,000 units) .......

Sturdy Bike Company makes the frames used to build its bicycles. During 2018, Sturdy made 20,000 frames; the costs incurred follow: Unit-level materials costs (20,000 units $35.00)..........$...

Hammond Company runs a driving range and golf shop. The budgeted income statement for the coming year is as follows. Sales ....................................... $1,240,000 Less: Variable expenses...

Consider the following information on four independent companies. Required: Calculate the correct amount for each questionmark. S ? $9,000 Sales Less: Variable costs $10,000 8,000 S 2,000 11,700...

right a short 250 word paragraph in a casual tone. We know that scopes are an integral part of a firearm, as they can help with improving aim accuracy and hitting the target. For this assignment, you...

Please test your code using MySQL Compiler and send a screenshot that the code actually runs. Send me copyable text. Regarding my code for 08 in step 1, pls find below. This has a lot of mistakes,...

ARGENT in 30 min Complete the Four missing steps in reserve.php" file, which is activated when the form in "BooksForm.html" page is submitted. BooksForm.html" page with a sample input is shown in the...

Write a database trigger to enforce the following business rules in the sample Bookshop database as shown in Figure 1. Make sure that appropriate and informative feedback is given to the user if the...

*****Please note: 'WeekStarting' in 'DutyRosterHistory' should be DATE not VARCHAR(15). Write a database trigger to enforce the following business rules in the sample Bookshop database as shown in...

13-31. Consider the SUPPLIER, PART, and SHIPMENT rela- tions and distributed database mentioned in the section on query optimization in this chapter. a. Write a global SQL query (submitted in...

PLEASE HELP! Lab is due tomorrow Step 1: Insert Functions Select the Search worksheet and then the Pet Deposit column. Create a formula to determine the required pet deposit for each unit. If the...