Write SQL for each question by using the tables below .

CUSTOMERS TABLE

OFFICES

ORDERS

PRODUCTS

SALESREPS

31) Find the salesperson who has not yet been assigned to an office? 32) List the salespeople assigned to an office? 33) List salespeople who are selling under their quota or under $300,000? 34) List all salespeople who are under quota but whose sales are not less than $150,000? 35) List the sales of the offices, sorted alphabetically by region in reverse (Z-A) and alphabetically by city (A-Z) for each region? 36) Sort offices in descending order of sales so that the offices with the largest sales appear first? 37) Rank the offices in descending order of sales performance so that the offices with the best performance (by percentage of target realization) appear first? 38) List all products whose price exceeds $2000? 39) List all items ordered over $30000 in a single order? 40) List all products with the price of the product exceeding $2,000 and ordered more than $30,000 in a single order, in descending order of order amount? 41) List all products where the product price exceeds $2,000 or the product is ordered more than $30,000 in a single order, by manufacturer and product number? 42) List the offices, the names and titles of their directors? 43) List offices and executive information with targets over $600,000? 44) List all orders showing quantities and product descriptions? 45) List orders over $25,000, including the name of the salesperson who took the order and the name of the customer who placed the order? 46) List orders over $25,000 showing the name of the customer who placed the order, the customer's salesperson, and the salesperson's office? 47) Find all orders received on the day a new salesperson was hired? 48) List all combinations of salespeople and offices where the salesperson's quota exceeds the target of any one office, regardless of which office the salesperson works in? 49) Show all salespeople who have made more than $350,000 in sales? SELF JOIN 50) List the names of salespeople and managers? 51) List salespeople with higher quotas than their managers? SUMMARY QUESTIONS 52) What are the average quotas and average sales of our salespeople? 53) What is the total quota and total sales? 54) What is the sum of the orders Bill Adams has received? 55) Calculate the average price of the products of the manufacturer ACI (MANUFACTURER)? 56) Acme Mfg. Calculate the average amount of an orders placed by (customer number 2103)? 57) What are the smallest and largest quotas assigned? 58) What is the first order date in the database? (Only this date should be listed) 59) What is the highest sales amount for each salesperson? (according to the order amount) 60) How many customers are in the database?

* (New)