Purpose Assignment Examine Use Aggregate Functions Analyze Queries Contain Null Values Use Q43858459
The purpose of this assignment is to examine the use ofaggregate functions, analyze queries that contain NULL values, anduse CASE logic to resolve the data and arrive at a solution.
For this assignment, you will perform a set of exercises inwhich you will analyze data and develop corresponding aggregatefunctions to obtain the required information. You will useMicrosoft SQL Server 2016 Developer Edition and the content fromthe AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3 tocomplete the assignment.
Please note that when SQL queries are run, results are generatedin the form of data. This data should be exported and saved toExcel for a visual check of accuracy.
Create a Word document that includes the SQL queries used toexplore the database tables, and answer the followingquestions.
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 youprovide the name of the employee who made the highest total singlesale in 2014. This data needs to include the filter “Tax &Freight” using “OrderDate” for the year. The CEO has also requestedthat you provide the winner’s e-mail address so he can send anotification of congratulations.
Problem Three
Jo Berry’s manager indicated that Jo was taking paid time offthis month. He wants to ensure the system reflects thisinformation. Import the “Hours” spreadsheet file into theAdventureWorks database. Add Jo Berry’s hours together for themonth, and title the field “Monthly Hours.” Use the knowledge youhave learned about NULL values to provide the solution to themanager.
Problem Four
The human resources manager wants to classify employees who wereabsent during the entire month as “Inactive.” Using a CASEstatement, write a query that classifies employees as “Active” or”Inactive” in a column titled “Status” with “Full Name” (First andLast) also listed.
Problem Five
Using the same parameters as Problem Four, rewrite the query toprovide only the list of “Inactive” employees.
General Requirements
Compile the Excel data file and Word document containing the SQLqueries and answers to the questions into a .zip file and submit toyour instructor.
APA style is not required, but solid academic writing isexpected.
This assignment uses a rubric. Please review the rubric prior tobeginning the assignment to become familiar with the expectationsfor successful completion.
You are not required to submit this assignment toLopesWrite.
Expert Answer
Answer to The purpose of this assignment is to examine the use of aggregate functions, analyze queries that contain NULL values, a…
OR