Tampa Autoparts Corp. (TAC) is a medium-sized company located in Tampa, Florida. The company manufactures ball bearings, axles, washers, screws, locknuts, and various other auto parts. Sales for the fiscal year ended September 30, 2019 amounted to $92.5 million. The company's accounting system is housed in a SQL Server database. TAC's business processes relating to product sales are indicated below. TAC has two basic types of customers: automobile service stations and auto parts stores. All sales are on credit (no cash sales). Auto parts stores customers receive a discount on products ordered, while service stations do not. Only approved customers may place sales orders for products in TAC's finished goods inventory. Customers place orders over the phone and provide their unique customer number at that time. Regarding customers, TAC keeps track of their name, address, balance, credit limit, and the date the firm became a customer. Every sales order is processed by one salesperson and a salesperson can obviously have processed many orders. The shipping date for the order must be entered. The shipping date should be after the order date, but not more than one month after the order date. Orders are shipped either via UPS or by Tampa Trucking. A sales order specifies one or more products. Each product has a product number, product name, cost price, standard selling price, and quantity on hand. Many products can be on an order and a product can be on many different orders. Each product falls in one category, as indicated by the CategoryID field. Each product category has a manager and a standard overhead cost per unit for products in that category. A manager can be assigned to more than one categories. At the time of order entry, the salesperson selects the products desired by the customer and also enters the selling price and quantity ordered of each product on each order. Since only products in stock can be ordered, there is exactly one shipment associated with every sales order. Products ordered before noon are usually shipped to the customer the same business day; products ordered after noon are usually shipped the next business day. A clerk in the warehouse department first retrieves the order details from the system, picks the products on each order from the warehouse shelves, and packages them to be shipped to the customer. The employee then creates an invoice and a shipping document indicating the products packaged and the shipper who will actually deliver the products to the customer. As indicated above, TAC uses either UPS or Tampa Trucking for the deliveries to customers. When the products are delivered to the customer, the invoice accompanying the shipment indicates the amount to be paid by the customer.

Imagine that you are a consultant hired by TAC and are tasked with providing management with a better understanding of sales performance and contribution margins of its products. You have been asked to work with Ms. Janice Roberts, the controller at TAC. After discussions with Ms. Roberts, you have determined that there are a set of specific questions that need to be answered. Additionally, you seek to perform additional queries for the purpose of generating overall analytics to help management understand product sales and profitability performance. You have been granted "read only" access to TAC's SQL database. The database diagram follows. You are to perform SQL queries to answer the specific questions posed by Ms. Roberts and also generate various analytics that might shed light on product sales performance and profitability along several dimensions that would be of interest to management.

3. We need a listing of all sales orders that have not been invoiced. Please show the order number, the order date, the customer name, the expected shipping date, and the salesperson name for these un-invoiced sales orders.

For your analytic queries in #3 above, note that often less is more. If you include too many group by fields, the query result will become unwieldy and less useful. Be judicious in the use of group by fields. You are better off running separate queries rather than grouping by too many fields in one query. Thus, instead of having a single query that groups by product category and salesperson, you are better off with separate queriesone that groups by product category and one that groups by salesperson. Page 4 of 4 Follow the instructions in the "SQL demo" file posted Canvas to obtain the Azure Data Studio program. You will need to connect to the TACSQL SQL Server database the same way as you connected to the Northwind database for the in-class demo. After you have finalized each SQL query, you should save the query to a SQL file and the results of each query to an Excel file. See the saving results section of the SQL demo file (page 8). In addition, take a screenshot of your first and last specific questions queries, and highlight your username in the query tab (your SQL username will be in parentheses in the query tab title).

PRODUCTS ORDERED ORDERNO 9 PRODUCTNO PRICE QTY DISC_PERCENT ORDERS ORDERNO ORDER_DATE EXP_SHIP_DATE BID CUSTOMERNO SHIPPERNO COMMENTS EMPLOYEES EID NAME PHONE NO DEPARTMENT RANK DATE EMPLOYED SALARY 00 PRODUCTS PRODUCTNO PRODUCT_DESCRIPTION CATEGORYID COST_PRICE STANDARD_SELLING_PRICE QTY ON HAND AVG ORDER QTY CUSTOMERS CUSTOMERNO Name Address CATEGORIES CATEGORYID DESCRIPTION MANAGER OVERHEAD State Zip Type Balance Cr_limit Customer since INVOICES INVOICENO SHIP_DATE ORDERNO EID SHIPPERS 7 SHIPPERNO NAME PHONE PRODUCTS ORDERED ORDERNO 9 PRODUCTNO PRICE QTY DISC_PERCENT ORDERS ORDERNO ORDER_DATE EXP_SHIP_DATE BID CUSTOMERNO SHIPPERNO COMMENTS EMPLOYEES EID NAME PHONE NO DEPARTMENT RANK DATE EMPLOYED SALARY 00 PRODUCTS PRODUCTNO PRODUCT_DESCRIPTION CATEGORYID COST_PRICE STANDARD_SELLING_PRICE QTY ON HAND AVG ORDER QTY CUSTOMERS CUSTOMERNO Name Address CATEGORIES CATEGORYID DESCRIPTION MANAGER OVERHEAD State Zip Type Balance Cr_limit Customer since INVOICES INVOICENO SHIP_DATE ORDERNO EID SHIPPERS 7 SHIPPERNO NAME PHONE