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.
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?
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.
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.
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.
Using the same parameters as Problem Four, rewrite the query to provide only the list of “Inactive” employees.