COB $204$ Database Project $-$ Documentation

Enter your team number here: Team $8$

Enter every team member's here: Kendyl Ketchum

COB$204$ Company is a retail store that sells and ships goods to their customers. They need your help to answer the following business questions based on their sales transaction data.

Read each business question and develop a query that will produce output to answer the question.

Business Question $1$: Develop a query about sales that were shipped to Brazil, and include the following attributes in this order: the customer last name, the sale ship$-$to city, the sale ship date, and the name of the shipper. Restrict the query to include sales that were shipped to Brazil during the first six months of $2013$ by the shipper Speedy Express. Sort the query output first by the ship$-$to city and then by the ship date.

Copy your query's SQL syntax with comment and paste it here:

How many records are listed in the query output? Enter your answer here:

Business Question $2$: The Finance Manager asks you to develop a query that includes these attributes in this order: the country of the supplier, the product units in stock, units on order, and a column that calculates the total dollar value of the quantity that is in stock for each product and named as $"$In$-$Stock Value." Restrict the query output as follows and in this order: include only those products supplied by suppliers who are in Sweden, Finland, or Norway, and products with units in stock levels of at least $1$ but no more than $20.$ Last, sort the output by the supplier country name and then the column $"$In$-$Stock Value."

Business Question $3$: You work in Procurement and are reviewing customer orders. Develop a query so that the output includes the following attributes in this order: the employee last name, the required date of the order, the Order ID$,$ the customer last name, and a column that shows the gross dollar amount of the order named as "Gross Order Amount." Restrict the query output to include only orders processed by the employee whose last name is Callahan, and further restrict the query to include only orders placed during August, September, or October of $2012.$ Sort the output by the "Gross Order Amount" column, with the largest amount listed first.

Business Question $4$: You work in Marketing and are planning a sales campaign for household products and wish to know details about select products and their sales history. Develop a query that includes these attributes in this order: product name, product unit price, product units in stock, and an aggregate column that represents the total quantity sold on all past sales, named "Historic Product Sale Quantity." Restrict the query output in this order: records that

$1$

include product names that end with the pattern "lamp" or "quilt" or that include the pattern "clock" or "carpet" anywhere within the product name. Sort the output by the "Historic Product Sale Quantity" and then the product name.

Copy your query's SQL syntax with comment and paste it here:

How many records are listed in the query output? Enter your answer here:

Business Question $5$: The Manager of Sales wants to improve customer relationships, and needs some details from you. They request that you develop a query to include these attributes in this order: the country where the customer lives, the customer last name and then first name, and then an aggregate column that represents how many employees have processed a sale for that customer. Remember, the objective is to learn whether or not just one employee processed all sales to a customer, so develop your query to answer that specific question. To prepare to export your output to Word, use the AS keyword to temporarily rename the customer last and first name attributes as "Last Name" and "First Name" and the aggregate column as "Number of Different Employees." They want just a sampling of customers right now, and so want to restrict the query output to include only records where the customer country is Argentina or Canada. Sort the query output by the new aggregate column with the largest value first, then alphabetically by the customer last name.

The output of this query will become the Customer Relationships report.

Copy your query's SQL syntax with comment and paste it here:

How many records are listed in the query output? Enter your answer here:

Business Question $6$: COB$204$ You work in Operations and wish to examine freight and discount costs that are a part of sales and are related to the supplier of the products being sold. Recall that the discount is a calculated function of quantity, sale price, and the discount percentage. The freight charge is an overall charge applied to each sale. Create a query that includes the supplier name, an aggregated column that represents the total dollar amount of the freight, and another aggregated column that is the calculated total dollar amount of the discount. To prepare to export your output to Excel, use the AS keyword to name the columns as $"$