# SQL Notes: Exercise 6: Subqueries: Answers

Name: Answers

Write SELECT statements which produce the appropriate results. Put each clause (FROM, WHERE, ...) on a separate line. Use subqueries for all questions unless otherwise instructed. Use the "Sales" database from SQL: The Complete Reference.

1. (6 points) List all salespeople by name who work in the 'Los Angeles' office. Write three different answers: join using SQL1 notation, join using SQL2 notation, and using a subselect.
   - Using SQL1 join:
     Answer:

                SELECT name
                  FROM Salesreps, Offices
                  WHERE Salesreps.rep_office = Offices.office
                        AND city = 'Los Angeles';

   - Using SQL2 join:
     Answer:

                SELECT name
                  FROM Salesreps JOIN Offices ON rep_office = office
                  WHERE city = 'Los Angeles';
                -- Note: the city test should be in the WHERE, not the JOIN

   - Using subquery:
     Answer:

                SELECT name
                  FROM Salesreps
                  WHERE rep_office = (SELECT office
                                        FROM Offices
                                        WHERE city = 'Los Angeles');

2. (2 points) In the future there may be more than one office in Los Angeles (with different "office" codes of course). List all salespeople by name who work in any of the 'Los Angeles' offices.
   Answer:

              SELECT name
                FROM Salesreps
                WHERE rep_office IN (SELECT office
                                       FROM Offices
                                       WHERE city = 'Los Angeles');

   Another possibility is to use " = ANY " in place of " IN ".
3. (2 points) List all the salespeople by name who have never placed an order.
   Answer:

              SELECT name
                FROM Salesreps
                WHERE empl_num NOT IN (SELECT rep
                                         FROM Orders);

4. (2 points) List all orders (order number, customer's company name, and amount) that are twice or more the average (of all orders) order amount. Order the output by ascending amount.
   Answer:

              SELECT order_num, company, amount
                FROM Orders JOIN Customers ON cust = cust_num
                WHERE amount >= 2.0 * (SELECT AVG(amount)
                                         FROM Orders)
                ORDER BY amount;

5. (2 points) What's the cheapest product (manufacturer id, product code. and description) that we currently have on hand.
   Answer:

              SELECT mfr_ID, product_ID, description
                FROM Products
                WHERE qty_on_hand > 0
                      AND price = (SELECT MIN(price)
                                     FROM Products
                                     WHERE qty_on_hand > 0);

6. (2 points) Let's check the database for referential integrity (all foreign keys have actual existing primary key records). Show all salespeople by name and their office code, who have an office code that doesn't appear in the Offices table.
   Answer:

              SELECT name, rep_office
                FROM Salesreps
                WHERE NOT EXISTS (SELECT *
                                    FROM Offices
                                    WHERE office = rep_office);
              --- or the more common answer ---
              SELECT name, rep_office
                FROM Salesreps
                WHERE rep_office NOT IN (SELECT office
                                           FROM Offices);

7. (2 points) List all salespeople by name and average order, whose average order is greater than their manager's average order.
   Answer:

              -- Thanks to Delia for this solution -------
              SELECT name, AVG(amount)
                FROM Salesreps AS emp, Orders
                WHERE empl_num = rep
                      AND (SELECT AVG(amount)
                             FROM Orders
                             WHERE rep = empl_num)
                          >
                          (SELECT AVG(amount)
                             FROM Orders
                             WHERE rep = manager)
                GROUP BY name;

8. (2 points) List the customers (company) who have ordered something from 'ACI' from a salesperson who works in 'Chicago'.
   Answer:

              --- A 4-SELECT solution from Brad, Lori, Delia ! ----------
              SELECT company
                FROM Customers
                WHERE cust_num IN
                      (SELECT cust
                         FROM Orders
                         WHERE mfr = 'ACI' AND rep IN
                              (SELECT empl_num
                                 FROM Salesreps
                                 WHERE rep_office IN
                                       (SELECT office
                                          FROM Offices
                                          WHERE city = 'Chicago')));
              --- Robert, Connie had joins with one subselect
              --- Here is one of their answers.
              SELECT company
                FROM Customers JOIN Orders on cust_num = cust
                WHERE mfr = 'ACI' AND rep IN
                      (SELECT empl_num
                         FROM Salesreps JOIN Offices ON rep_office=office
                                 WHERE city = 'Chicago');