database to be used is the MS SQL AdventureWorks Database 2014

Project Instructions:

In order to complete this assignment you will need to log into the virtual machine and use the Adventure Works database within SQL Server Management Studio.

For each question include:

  • the text version of the SQL script itself
  • a screenshot of the SQL script in action
    • Your screenshot should include both the query and the results
    • Make sure your screenshot includes the results from the GETDATE() function
  • Each script must include the GETDATE()(Links to an external site.)Links to an external site. function in the select statement

To keep the queries more straightforward, use aliases.


Section A

Create SQL Scripts that:

  1. Selects ALL the BusinessEntityID, LastName, and FirstName fields for the Person’s table
    1. Fields should be in the above order
    2. Add a sort to the query so LastName is alphabetized A – Z
  2. Add the person’s email address to Query #A1
  3. Add the person’s phone number to Query #A2, include the PhoneNumberType Name field (etc. cell, home, work)
  4. Add in the AddressLine1, City, StateProvinceID, and PostalCode fields to Query #A3
    1. Note: two different people could have the same address. This database is designed to store each unique address individually, so while in this case this detail for this address would only exist once in the database, it would be assigned to all individuals with that address.
    2. Note2: You will have to bring in two different Address tables to get this to portion to work.
  5. Modify the Query from #A4 to include the StateProvinceCode, CountryRegionCode, and Name

Section B

  1. Create a query that calculates a count of Departments by Group Name from the HumanResources.Department table.

Create a completely different query that shows the distinct listing of Product


Prime Essay Services , written from scratch, delivered on time, at affordable rates!