Database: Bookbiz Query Exercises - Ch 4

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. Run the database to check your answer.

1. Show all columns and rows in the authors table. You should get all 23 rows.
   Answer:

   SELECT * 
     FROM authors;

2. Show the first and last names of all authors. You will get all 23 rows.
   Answer:

   SELECT au_fname, au_lname
     FROM authors;

3. Show the first and last names of all authors, but this time make the columns labels at the top "FirstName" and "LastName" (no blanks). Use column aliases for this. You will get 23 rows.
   Answer:

   SELECT au_fname AS FirstName, au_lname AS LastName
     FROM authors;

4. Show the first and last names of all authors, but this time make the column labels at the top "First" and "Last" (no blanks). Use column aliases for this. Hint: The difference between this problem and the previous is that the names collide with SQL keywords. How do you avoid that? You will get 23 rows.
   Answer:

   SELECT au_fname AS "First", au_lname AS "Last"
     FROM authors;

5. Which authors live in Walnut Creek? Show all columns You should get 1 row (Akiko Yakomoto).
   Answer:

   SELECT * 
     FROM authors
     WHERE city='Walnut Creek';

6. Which orders are incomplete? List the title_ids and the number of titles that still have to be shipped to complete orders. Show only rows where not as many titles have been shipped as ordered. Use the salesdetails table for this. There should be 5 rows in your result.
   Answer:

   SELECT title_id,  qty_ordered-qty_shipped
     FROM salesdetails
     WHERE qty_shipped < qty_ordered;

7. Which editors don't have a boss? List all editors (first and last names) without a boss (NULL in ed_boss field). There will be two rows in the result.
   Answer:

   SELECT ed_fname, ed_lname
     FROM editors
     WHERE ed_boss IS NULL;

8. Which editors do NOT have 993-86-0420 as a boss? List all editors (last names) and their bosses. HINT: This may not be quite as easy as it looks because of NULLs. If you did it right, you'll have a 5 row result.
   Answer:

   SELECT ed_lname, ed_boss
     FROM editors
     WHERE ed_boss IS NULL 
        OR ed_boss <> '993-86-0420';

9. Which non-business books cost between $20 and $30? Give the title, type, and price. There are 5 rows in the result.
   Answer:

   SELECT title, type, price
     FROM titles
     WHERE type <> 'business' AND price BETWEEN 20 AND 30;

10. List the last names of all authors who have a letter 'k' in their last name? Is ASE case sensitive? ASE give three matching rows.
    Answer:

    SELECT au_lname 
      FROM authors
      WHERE au_lname LIKE '%k%';