Ray Henry owns Henry Books, a bookstore chain.

Ray Henry owns Henry Books, a bookstore chain. In running a chain of bookstores, Ray gathers a variety of information on branches, publishers, authors, and books. For this assignment, you will work in a database called Henry Books to create SQL queries to answer business questions.

In MySQL Workbench, create a database named FirstName_LastName_HenryBooks. As an example

Run the MySQL-Henry file (attached bellow) to add the tables and data to the HenryBooks database. For answers, please use SQL_Ansewrs_sheet (attached bellow) and rename it as FirstName_LastName_IT310Assignment3.

Write SQL statements for the following questions.

Business Questions

Question #

Question Text

Question #1

What are the titles and publisher codes for all books that belong to one of the following types: SFI, HOR, POE?

Question #2

What are all of the book types, in alphabetical order, listing each book type only once, sold by the bookstore?

Question #3

For each type of book available in the bookstore, what is the type of book and the average price for a book of that type?

Question #4

What are the titles and prices of all books that sell for at least $20, but no more than

$30? The answer should be sorted from the highest price book to the lowest price book.

Question #5

What is the title and price of all books published by Basic Books with a price more than $10?

Question #6

How many books have been written by Toni Morrison? Your answer must reference the string values Toni and Morrison.

Question #7

What is the title of every FIC book that has been published by Penguin USA?

Question #8

Ray wants to check the inventory of all four branches. Provide a report of every book in the database including the book code, title, price, the number in stock, and the branch number.

Question #9

Show every author in the database including the first name, last name, and the book code of book(s) they have authored. For an author who does not have any authored books, the value for book code should be NULL. Order the results by last name in alphabetic order.

Extra credit (5 points)

What are the title(s) and price(s) of the least expensive book(s) in the database? (Extra credit can only be granted when you retrieve the required information in ONE query.)

How Will This Assignment Be Graded?

Capture-3.PNG

Sample Student Submission (Just the first two questions)

/**

Question 1: List all information known about all books.

**/

SELECT *

FROM book;

/**

Question 2: List all the publishers located in New York. Sort the results in alphabetic order.

**/

SELECT Publisher_Name

FROM publisher

WHERE city = ‘New York’

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.