Create and execute queries that will answer the following 15 questions. You may use the graphical design view of Access to write your queries for this assignment. Save each of the queries to the database file using the naming convention of Q01, Q02, Q03 Q15 corresponding to the question number. Questions 1 10: 2 points each. 1. Working in the Accounting area of the business, you are interested in getting a list of Customers along with their outstanding balance (cust_balance). Order the data by the customer last name. Format the balance to display with a currency format. 2. You find that listing all customers does not make it easy to see who to focus our collection efforts on, re-write Q1 to display only those customers with a balance greater than $3,000 and order the data in descending sequence by the customer's balance. 3. You were just given your sales territory (Connecticut) and you need to get a list of all of your customers in that state with all of their vital information. 4. As a sales manager, you are interested in getting a list of all invoices for the 4th Quarter of 2017 (October 1 December 31). Include the invoice number, date and total from the invoice table, also display the employee name (first and last) from the Employee table and calculate and include the commission earned using the formula of (inv_total * emp_comm). Format the display of any currency type data to display as such. BUAD 3050 Assignment #5 Database Queries With Microsoft Access 40 Points 5. As a salesperson, you want a list of all product SKU, description and prices for our Exterior (prod_type), Water based (prod_base), Primer (prod_category) products. 6. As a salesperson, you need to get all of the pertinent information for all varnish type products before talking with one of your customers. A Varnish type product has the word varnish somewhere in the produce description. Order the data by product description. 7. As the inventory manager, you need to see any products that need replenishment. A product needs replenishment if the prod_QOH is less than the prod_MIN column. Display the product's SKU, Description, QOH and MIN. 8. As an HR analyst, you have been tasked with reaching out to your most junior employees and need a list of employees hired after Dec 31st, 2015 who have a title of Clerk I or Clerk II. You need their full names along with their emails. Order the data by last name. Do NOT display the employee's date of hire nor their title. 9. As an HR analyst, you need to know who the managers of the company are (by having the word "Manager" as part of the employee's title. Create a list that displays the Employee number, name and title. 10. As a Financial analyst who is interested in cash flow analysis, you are interested in the total of all outstanding customer balances. Create a query to get a sum of the Cust_balance column of the customer table. Questions 11 15, 3 points each. 11. As the newly hired VP of marketing, you need to get a count of customers in each of the states covered by your company. Order the data by State. 12. To find out which states provide the most sales, you need to get a total of all sales (inv_total) for each of the states. Order the data by sales in descending sequence. 13. You need to identify your 5 best salespersons for the year 2017 and how much they sold. 14. The Marketing department is trying to reduce the cost of sending it's mailers by identifying any potential duplicate customers. A customer may be a duplicate if the address, city and state are duplicated in another row. Display all columns of data for any potential duplicates. 15. To reduce the number of vendors that you use, you need to identify which vendor provides no products. Display all data for the unmatched vendors.