MS 610 GCU Aggregate Functions and Null Values Worksheet

The purpose of this assignment is to examine the use of aggregate functions, analyze queries that contain NULL values, and use CASE logic to resolve the data and arrive at a solution.

For this assignment, you will perform a set of exercises in which you will analyze data and develop corresponding aggregate functions to obtain the required information. You will use Microsoft SQL Server and the content from the AdventureWorks databases, as directed by your instructor, to complete the assignment.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.

Query Scenarios

Problem One

The sales manager asks you to provide the average “Pretax Sales” amount throughout the years. Using the “Sales.SalesOrderHeader” table, what answer will you provide to the sales manager?

Problem Two

As part of an internal competition, the CEO requests that you provide the name of the employee who made the highest total single sale in 2014. This data needs to include the filter “Tax & Freight” using “OrderDate” for the year. The CEO has also requested that you provide the winner’s e-mail address so he can send a notification of congratulations.

Problem Three

Jo Berry’s manager indicated that Jo was taking paid time off this month. He wants to ensure the system reflects this information. Import the “Hours” spreadsheet file, located in Course Materials, into the AdventureWorks database. Add Jo Berry’s hours together for the month, and title the field “Monthly Hours.” Use the knowledge you have learned about NULL values to provide the solution to the manager.

Problem Four

The human resources manager wants to classify employees who were absent during the entire month as “Inactive.” Using a CASE statement, write a query that classifies employees as “Active” or “Inactive” in a column titled “Status” with “Full Name” (First and Last) also listed.

Problem Five

Using the same parameters as Problem Four, rewrite the query to provide only the list of “Inactive” employees.

Why US?

100% Confidentiality

Information about customers is confidential and never disclosed to third parties.

Timely Delivery

No missed deadlines – 97% of assignments are completed in time.

Original Writing

We complete all papers from scratch. You can get a plagiarism report.

Money Back

If you are convinced that our writer has not followed your requirements, feel free to ask for a refund.