Write SQL for each question by using the tables below .

CUSTOMERS TABLE

OFFICES

ORDERS

PRODUCTS

SALESREPS

1) List the names of the sales offices, their targets and their actual sales?

2) List the East region sales offices and their targets and sales?

3) List the East region offices whose sales exceeded their targets, in alphabetical order by city name?

4) List all salespeople's names, offices, and dates of hire?

5) What is the name, quota and sales amount of employee number 107?

6) What is the average of the sales amounts of all salespeople?

7) List the names and hiring dates of salespeople with sales over $500000?

8) List salespeople, quotas and managers?

9) List the location, region and sales of each sales office? CALCULATED FIELD

10) List the city, region and amount above/below the target for each office. (Calculation required) 11) Show stock value (holding cost) for each product. (Price transaction with stock quantity)

12) If I increased each salesperson's quota by 3 percent of their annual sales, what would their new quota be?

13) List the sales amounts for each city?

14) List all sales office managers with their employee numbers?

15) List the offices whose sales are above their targets?

16) Show the name, sales and quota of employee 105?

17) List employees managed by Bob Smith (employee #104)? COMPARATIVE QUESTIONS

18) List salespeople hired before 2006?

19) List offices with sales below 80 percent of target?

20) List offices not managed by employee number 108?

21) List salespeople who have exceeded their quota?

22) List the items that have sales below or up to their quota?

23) Find the orders placed in the last quarter of 2007. (You must use the Year and Month functions, the month must be greater than 9)

24) List orders that fall into various quantity ranges. (Group By)

25) List salespeople whose sales are not between 80 percent and 120 percent of their quota?

26) List salespeople who work in New York, Atlanta, or Denver?

27) List all orders placed on Fridays in January 2008?

28) Find all orders placed by the four private salespeople of your choice?

29) Smithson Corp. Show your customer named credit limit?

30) List the products whose product code consists of 5 characters, the code starts with any number, the second character is "A", the third and fourth characters are any numbers, and the character is any letter?

* (New)