# SQL Notes: Exercise 4 - SELECT Summaries

Name: ANSWERS

Write a SELECT statement which produces the results. Put each clause (FROM, WHERE, ...) on a separate line. Unless otherwise specified, use the example database from the book SQL: The Complete Reference.

1. How many offices are there?

           SELECT COUNT(*)
              FROM Offices;

2. How many office managers are there? Remember that some managers may manage more than one office/person.

           SELECT COUNT(DISTINCT mgr)
              FROM Offices;

3. When was Sue Smith's first sale?

           SELECT MIN(order_date)
              FROM Orders, Salesreps
              WHERE Orders.rep = Salesrep.empl_num
                AND Salesrep.name = 'Sue Smith';

4. What's the biggest order each salesrep has taken? List the rep (number) and the amount.

           SELECT rep, MAX(amount)
              FROM Orders
              GROUP BY rep;

5. Who are the reps (name) who have made exactly one sale?

           SELECT Salesreps.name
              FROM Orders, Salesreps
              WHERE Orders.rep = Salesrep.empl_num
              GROUP BY rep
              HAVING COUNT(*) = 1;