# SQL Notes: Exercise 6: Subqueries

Name: _________________________________

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:
   - Using SQL2 join:
   - Using subquery:
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.
3. (2 points) List all the salespeople by name who have never placed an order.
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.
5. (2 points) What's the cheapest product (manufacturer id, product code. and description) that we currently have on hand.
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.
7. (2 points) List all salespeople by name and average order, whose average order is greater than their manager's average order.
8. (2 points) List the customers (company) who have ordered something from 'ACI' from a salesperson who works in 'Chicago'.