ENTERPRISE ACCOUNTING SYSTEMS
Adventure Works Cycles is a large multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base.
Adventure Works Cycles uses Microsoft SQL Server to help capture data about their operations including Human Resources, Sales, Purchasing and Production. There are approximately 70 different tables contained within this database.
OBJECTIVE
The objective of this assignment is to use your SQL skills to answer various questions regarding Adventure Works operations.
ASSIGNMENT
You will submit your saved SQL file to blackboard. Please use the SQL Template provided on Blackboard as a template for completing this assignment. Please save this file as your netid and SQL SPR 2018 e.g. mkupch1.SQL 2018.
1) How many different job titles does Adventure Works have?
Table(s): Employee
2) How many employees have each of the job titles listed in question #1?
Table(s): Employee
3) How many total vendors does Adventure Works have?
Table(s): Vendor
4) How many of the vendors names contain the word bike?
Table(s): Vendor
5) Identify the sales person that belongs to each Sales Territory Name. Include the following columns for each Sales Person in the following order and sort by Last Name Ascending:
a. Sales Territory Name
b. Business Entity ID
c. First Name
d. Last Name
e. Sales Person Sales Quota
f. Commission Percentage
g. Bonus
h. Sales YTD
i. Sales Last Year.
Table(s): SalesPerson, Person, SalesTerritory
6) For each Sales Person calculate the following:
a. Total Sales Amt (not including any freight or tax charges)
b. Total Order Count
c. Average Sales Amt
d. Commission Percentage
e. Total Commission Amount (Total Sales Amt X Commission Percentage)
Table(s): SalesOrderHeader, SalesPerson
Note: Format the Total Sales, Average Sales and Total Commission Amount as Currency using the Format Function Format the Commission Percentage as a percent using the Format Function.
7) Identify all Vendors who Adventure Works did not purchase from. Include the following fields:
a. VendorID
b. AccountNumber
c. Name
d. CreditRating
e. PreferredVendorStatus
f. ActiveFlag
Table(s): Vendor, PurchaseOrderHeader
8) Identify all Products who have had a cost change history greater than 2 times?
Table(s): ProductCostHistory
9) Determine the Maximum and Minimum Standard Cost by Each ProductID. Remove any ProductID were the Maximum and Minimum are the same.
Table(s): ProductCostHistory
10) Determine the total sales by Store. Include the following:
a. BusinessEntityID (StoreID)
b. Store Name
c. Sales Person ID
d. Customer ID
e. Total Sales
Table(s): Store, Customer, SalesOrderHeader
Note: Format the Total Sales as Currency using the Format Function. Order the results in Descending order by Total Sales