Homework H3 – with SQL Solutions

1. Which books have been published in paperback? List author last name and book title. Sort by last name.

2. Find the average price of each type of book. Format price as currency.

3. For each branch store, list the number of books on hand.

4. List all fiction (FIC) books published in New York. Just show the book title and publisher name.

5. Find the average price of all books for which there are more than 2 on hand in inventory.

6. The president of Henry Books would like to know the average price of books at each of the four branches. But she wants the prices
broken down by type.  And she only wants the prices for fiction (FIC), horror (HOR), mystery (MYS), and science fiction (SFI).
Format your results using currency.

7. (challenging) List all publishers that do not have any books at the Henry Brentwood branch.

8. (more challenging) Which author has the most number of books on hand in inventory?  List author last name and number of books on hand.

Q1 – Paperbacks

SELECT Author.AuthorLast, Book.Title

FROM Author INNER JOIN (Book INNER JOIN Wrote ON Book.BookCode = Wrote.BookCode) ON Author.AuthorNum = Wrote.AuthorNum

WHERE (((Book.Paperback)=Yes))

ORDER BY Author.AuthorLast;

Q2 – AvgPriceType

SELECT Book.Type, Avg(Book.Price) AS [Average Price]

FROM Book

GROUP BY Book.Type;

Q3 – BranchOnHand

SELECT Branch.BranchName, Sum(Inventory.OnHand) AS [Books On Hand]

FROM Branch INNER JOIN Inventory ON Branch.BranchNum = Inventory.BranchNum

GROUP BY Branch.BranchName;

Q4 - NewYork

SELECT Book.Title, Book.Type, Publisher.PublisherName, Publisher.City

FROM Publisher INNER JOIN Book ON Publisher.PublisherCode = Book.PublisherCode

WHERE (((Book.Type)="FIC") AND ((Publisher.City)="New York"));

Q5 - AvgPrice

SELECT Round(Avg([Price]),2) AS [Avg Book Price]

FROM Book INNER JOIN Inventory ON Book.BookCode = Inventory.BookCode

WHERE (((Inventory.OnHand)>2));

Q6 - TypexBranch

TRANSFORM Avg(Book.Price) AS AvgOfPrice

SELECT Book.Type

FROM Branch INNER JOIN (Book INNER JOIN Inventory ON Book.BookCode = Inventory.BookCode) ON Branch.BranchNum = Inventory.BranchNum

WHERE (((Book.Type)='FIC' Or (Book.Type)='HOR' Or (Book.Type)='SFI' Or (Book.Type)='MYS'))

GROUP BY Book.Type PIVOT Branch.BranchName;

Q7 - NotHenryBrentwood

SELECT PublisherName

FROM Publisher

WHERE PublisherName NOT IN

(SELECT Publisher.PublisherName

FROM Branch INNER JOIN ((Publisher INNER JOIN Book ON Publisher.PublisherCode = Book.PublisherCode) INNER JOIN Inventory ON Book.BookCode = Inventory.BookCode) ON Branch.BranchNum = Inventory.BranchNum

WHERE (((Branch.BranchName)="Henry Brentwood")));

Q8 – MakeTblTemp1 and MaxOnHand

Solution 1:

The MakeTblTemp1 query finds, for each author, the total number of books they have on hand in inventory. It saves these numbers in a table called Temp1. Then, the second query, MaxOnHand, just picks out the author having the maximum of the numbers listed in table Temp1.

A disadvantage of this solutions is this: Suppose you want to know the answer to the query tomorrow, or a week from tomorrow, or 7 months from tomorrow. You would always have to remember to run the MakeTblTemp1 query first, so that you are finding the Max of the most current data.

MakeTblTemp1

SELECT Sum(Inventory.OnHand) AS SumOnHand INTO Temp1

FROM (Book INNER JOIN (Author INNER JOIN Wrote ON Author.AuthorNum = Wrote.AuthorNum) ON Book.BookCode = Wrote.BookCode) INNER JOIN Inventory ON Book.BookCode = Inventory.BookCode

GROUP BY Author.AuthorLast;

MaxOnHand

SELECT Sum(OnHand) AS MaxOnHand, AuthorLast

FROM Author, Wrote, Book, Inventory

WHERE Author.AuthorNum=Wrote.AuthorNum

AND Wrote.BookCode=Book.BookCode

AND Book.BookCode=Inventory.BookCode

GROUP BY AuthorLast

HAVING Sum(OnHand) =

(SELECT MAX(SumOnHand)

FROM Temp1);

Solution 2:

This solution gets around the disadvantage mentioned above by making MakeTblTemp1 a subquery. Note that the code starting with the final SELECT is essentially the same as the code for MakeTblTemp1. The reason for giving
Solution 1 is pedagogical, i.e., it’s a bit easier to explain and understand.

SELECT Sum(OnHand) AS MaxOnHand, AuthorLast

FROM Author, Wrote, Book, Inventory

WHERE Author.AuthorNum=Wrote.AuthorNum

AND Wrote.BookCode=Book.BookCode

AND Book.BookCode=Inventory.BookCode

GROUP BY AuthorLast

HAVING Sum(OnHand) =

(SELECT MAX(SumOnHand)

FROM

(SELECT Sum(Inventory.OnHand) AS SumOnHand

FROM (Book INNER JOIN (Author INNER JOIN Wrote ON Author.AuthorNum = Wrote.AuthorNum) ON Book.BookCode = Wrote.BookCode) INNER JOIN Inventory ON Book.BookCode = Inventory.BookCode

GROUP BY Author.AuthorLast)

);