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. List all titles (title) followed by their publishers (pub_name). Alphabetize the titles. Use SQL1 syntax (join in the WHERE clause).
   Answer:

   SELECT title, pub_name
     FROM titles, publishers
     WHERE publishers.pub_id = titles.pub_id
     ORDER BY title;

2. As above, but use NATURAL JOIN.
   Answer:

   SELECT title, pub_name
     FROM titles NATURAL JOIN publishers
     ORDER BY title;

3. As above, but list only the business books using SQL1 syntax.
   Answer:

   SELECT title, pub_name
     FROM titles, publishers
     WHERE type='business'
       AND publishers.pub_id = titles.pub_id
     ORDER BY title;

4. Display the editors (ed_lname, ed_fname) and titles (title) for Anne Ringer's books? Use whichever join syntax you prefer. Hint: This joins 5 tables. There should be 4 lines of output involving three separate editors.
   Answer:

   SELECT ed_lname, ed_fname, title
     FROM editors NATURAL JOIN titleditors
                  NATURAL JOIN titles
                  NATURAL JOIN titleauthors
                  NATURAL JOIN authors
     WHERE au_lname='Ringer' and au_fname='Anne';

5. Show the number of titles they've edited followed by the editor's name (ed_id, ed_lname, ed_fname). Put this in order of the editor who has done the most work (Dennis McCann has worked on 10 titles), to least (Bernard Samualson has worked on 2). Note: This requires both a join and a GROUP BY. Altho there is an equijoin on ed_id, you will have to qualify this field in both the SELECT clause and the GROUP BY clause, otherwise ASA will give you an error about lacking a "correlation name".
   Answer:

   SELECT COUNT(*), editors.ed_id, ed_lname, ed_fname
     FROM editors NATURAL JOIN titleditors
     GROUP BY editors.ed_id, ed_lname, ed_fname
     ORDER BY COUNT(*) DESC;