Database: Bookbiz Query Exercises - Ch 6 GROUP BY

Name: _________________________________

The following queries apply to the Bookbiz database that comes with the Practical SQL Handbook. The relationship diagram can be found on page 47 of the book. Use the database to check your answer.

1. How many authors are there in each state? Show two columns of output: the state code and the number of authors in that state. Note that there is a separate authors.state field; it isn't combined with the city as the diagram on page 47 shows.
   Answer:

   SELECT state, COUNT(*) 
     FROM authors
     GROUP BY state;

2. How many of each title have been shipped (salesdetails.qty_shipped)? Show the title_id and the total. There will be 16 lines of output and the largest total will be 808 and the smallest 5.
   Answer:

   SELECT title_id, SUM(qty_shipped) AS Total
     FROM salesdetails
     GROUP BY title_id;

3. Same as above, but in addition sort the books from the most shipped to the least.
   Answer:

   SELECT title_id, SUM(qty_shipped) AS Total
     FROM salesdetails
     GROUP BY title_id
     ORDER BY total DESC;

4. Same as sorted output above, but in addition only include books where more than a total of 10 books were shipped. There should be 11 lines of output.
   Answer:

   SELECT title_id, SUM(qty_shipped) AS Total
     FROM salesdetails
     GROUP BY title_id
     HAVING Total > 10
     ORDER BY total DESC;

5. How many of each title have been shipped (salesdetails.qty_shipped)? Show the title_id, the total shipped, and the number of orders. Show only books that have been ordered more than once. There will be three lines of output.
   Answer:

   SELECT title_id, SUM(qty_shipped) AS Total, COUNT(*)
     FROM salesdetails
     GROUP BY title_id
     HAVING COUNT(*) > 1;

6. For each title, what is the average number of books that are ordered (salesdetails.qty_ordered)? List in order of title_id. Don't include any orders where the qty_shipped is greater than the qty_ordered (yes, there is one!). Also, only include average orders of more than 20. This should produce 8 lines of output, and the average order for PS2091 should be 29.333333. Hint: this has both WHERE and HAVING clauses.
   Answer:

   SELECT title_id, AVG(qty_ordered)
     FROM salesdetails
     WHERE qty_ordered >= qty_shipped
     GROUP BY title_id
     ORDER BY title_id
     HAVING AVG(qty_ordered) > 20;