For this assignment, you will construct queries using both static and dynamic word problems for each of five scenarios.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

The purpose of this assignment is to demonstrate query building skills. For this assignment, you will construct queries using both static and dynamic word problems for each of five scenarios. You will use Microsoft SQL Server 2016 Developer Edition and the content from the Adventure Works 2016 and Scripts for SQL Server 2016 CTP3 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 In your new job, you realize you will be calling up the "HumanResources.Employee" table on a frequent basis to answer questions from your manager. Using what you have learned thus far about stored procedures, create an executable "Employees" dataset that will allow you to quickly reference all records in that dataset.

 

However, note that a stored procedure cannot be used in a SELECT statement after it has been created; it simply allows for a quick command to call up a preset data query you have made. Problem Two While a stored procedure is an effective method to review a static dataset, you realize the requirements for questions posed to you change frequently. Your manager often will request information specific to a given job title. You realize you need to create a function that allows a variable to be changed in your SELECT statement each time you run it. Note: To develop the answer to this problem, set the variable to "Design Engineer" and call the Function @JobTitle.

 

Problem Three Your manager wants to build an "Employee Time Off" calendar for the year for easy reference and has asked that you compile a list of vacation hours for each employee under his supervision. This task relates to the one you completed in Problem Two of this assignment, but will require that you restructure the code you developed from Problem Two to build a more complex query in or order to complete the task successfully.

 

 As with Problem Two, your manager will request this information often, and on an ongoing basis throughout the year. You realize you need to create a function that allows a variable to be changed in your query statement each time you run it so that you do not need to write new code for every request. The fields you need to bring in are "First and Last Name" and "Sick and Vacation Hours." Note: To develop the answer to this problem, create a join from the person table First and Last Name.

 

 Problem Four Your manager would like you to classify employee work areas in the building using a color coding system that he developed. Since no such system exists in the company database, he has asked you to upload his personal Excel worksheet into the database as a new table.

 

Using SQL Server Import and Export Wizard, load the "Roster" tab from the Excel file titled "Color Coding." Write a SELECT statement once loaded to confirm that it loaded correctly. Problem Five Now that your color coding system is in place, your manager wants to know which employees sit in the "Brown" section, as he will be relocating their desks in the future when the department is expanded. To provide him with this information, use the color coding system you developed for Problem Four, making sure to include the "Color Code," "Job Title," and concatenate "Last Name" "First Name" separated by a comma, calling the field "Full Name." General Requirements Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of