Determine the total sales by store

Assignment Help Database Management System
Reference no: EM131940855

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

Reference no: EM131940855

Questions Cloud

Explain three concerns that your organization of interest : Research and organization or use an organization from your experience and explain three concerns that your organization of interest will encounter.
Identify consequences of application in clinical practice : Provide a critique Kristen Swanson's Theory of Caring, making sure to identify benefits, consequences and feasibility of application in clinical practice.
How to evaluate the tradeoffs between risk and profitability : How to use "Current cash debt coverage ratio" and "Cash debt coverage ratio"). How to evaluate the tradeoffs between risk and profitability
Tax avoidance requirement of the government : Comment on the appropriateness of this tax avoidance requirement of the government. Propose another alternative to the use of poisons to deal
Determine the total sales by store : ACTG 494 – ENTERPRISE ACCOUNTING SYSTEMS - How many different job titles does Adventure Works have and How many employees have each of the job titles
Describe the role of seniors in the chosen minority groups : Describe the role of seniors in the chosen minority groups. Explain the changes in the role of seniors over the years.
Describe the overall approach and goal of each model : Research current change models used by organizations to facilitate change. Compare and contrast two models.
Calculate the cell potential and the equilibrium constant : Calculate the cell potential and the equilibrium constant for the following reaction at 298 K: +
Explain the concepts of historical accounting principle : Explain the concepts of historical accounting principle and the current value accounting principle

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd