Expanding The Scope Of Queries-Start the VM for assignment two. Inside the VM, click Start, type SSMS (to open SQL Server Management Studio), and select Database Engine as your server type, then click Connect. Select Databases and use the most current version of AdventureWorks for this assignment.

In this assignment, students will build refined queries and utilize transactional statements.

The human resources manager has asked you to obtain some specific information from the database. Use what you have learned in Topics 1 and 2 to build a refined query that answers each of the questions below using the WHERE statement and the specified fields so you can report the requested information back to the human resources manager.

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

Create a Microsoft Word document that includes the SQL query code used to explore the database tables and complete the following criteria. Make sure to provide a screenshot for each.

1. Provide a list of all of the records pertaining to the FirstName “John” in the Person table. Include the full name of all the records and state the total number of records.
2. The human resources manager indicated that John T. Moore was an erroneous entry and has requested that record be removed from the database. , provide the full SQL transaction (write-out/type the query) that you would use to delete that specific record using his BusinessEntityID.
3. In order to clear old contact lists from the Outlook address books, the human resources manager has requested a full list of the e-mail address for each employee. Using the most current version of AdventureWorks, find the related table and write a join query to include the full name and the e-mail address for each employee. (Note: Concatenate first and last names for each employee in one column under the label FullName)
4. Using the query written for question three, identify the full name for the following e-mail address, [email protected]. Add to the question three query with a WHERE clause to display this record.

Submit the Word document (including screenshots).

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

Answer Preview-Expanding The Scope Of Queries-SYM-400