Using the HenryBooks Database Concepts of Database Management Do this in SQL Ser

Question

Using the HenryBooks Database

Concepts of Database Management

Do this in SQL Server Management

1. (Joins and Calculations)
   Henry Books wants to increase
   the price of all Fiction Books.
   Write a query that returns the
   Book Code, Title, original Price,
   and NewPrice (calculated at 10% above
   the Original Price)

2. (Joins, SubQuery and Aggregation)
   The Book store wants to increase the
   overall quality of it's offerings.
   It wants to remove all less than good
   quality copies for the books that
   have Excellent copies available.
   Write a query that finds all the Bookcodes
   for copies that have excellent quality
   and then return the information about the
   Book to include the Book Title and the number
   of copies held of the Book.

3. (Joins, aggregations, and sorting)
   Find the Author First and Last Name,
   the number of books they have copies
   of from each publisher by Branch.
   You must include the Author First and
   Last Name, the BranchName, the PublisherName,  
   and the count of all copies in your results.

In sql

Explanation / Answer

Two tables can be created with the following fields and try executing the query

Henrybooks(bookcode,title,originalprice,au_lname,au_fname,au_id,pub_name)

Branch(au_id, Branchname, copies)

1.Use henrybooks

Select bookcode, title, originalprice, originalprice*0.1 as newprice from henrybooks where booktype=’fiction’

2.Use henrybooks

Select distinct title, copies from henrybooks where bookcode IN(Select bookcode from henrybooks where quality=’excellent’)

3.Use henrybooks, branch

Select au_lname, au_firstname, pub_name from henrybooks where au_id IN(Select copies from branch group by branchname)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.