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.

 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme