Database: Bookbiz Query Exercises - Ch 7 Inner Joins

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 publishers are there in each state?
   Answer:

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

2. List editor phone numbers and how many editors share that number, but don't list those lines where there is only one editor with that number.
   Answer:

   SELECT phone, COUNT(*)
     FROM editors
     GROUP BY phone
     HAVING COUNT(*) > 1;

3. What's the average advance for each type of title? Sort the list showing types that get the highest value at the top. Dob't include the NULL type in the output.
   Answer:

   SELECT type, AVG(advance)
     FROM titles
     WHERE type IS NOT NULL
     GROUP BY type
     ORDER BY AVG(advance) DESC;