Prepare  SQL Scripts that:

1. Select 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. Prepare a query that calculates a count of Departments by Group Name from the HumanResources.Department table.
2. Prepare a completely different query that shows the distinct listing of Product Subcatgories (Production.ProductSubcategory), include ProductSubcategoryID and Name in the query.
3. Modify the #B2 query to count the number of products from Production.Product by Subcategory, call the column "ProductCount".
4. Modify the #B3 query to include the Average StandardCost and the Average ListPrice, make sure to name the columns respectively. Order the results by the SubCategory Name (A-Z).
5. Add a column for the total product inventory quantity for each ProductSubcategory, make sure to name it as well.

 

Section C

1. Take your final query from #A5, modify the original query to filter out any people without an address, and load that query into Power BI.
2. You can copy and paste the SQL Statement in the Connection window (see below for connection details). This is the suggested (and easiest) approach.
3. Map those people by addresses in a Power BI visualization. Make sure the size of the bubble is indicative of the number of people in that city/state.
   1. Are there any patterns or clusters that you can see in the mapped addresses? If so, what are a few of them?
   2. Are there a significant number of people in the data set who live in countries outside of the US? If so, which continents include significant portions of people?
4. Use the best visualization that shows a count of people by state, show only those in the United States.
5. Take your final query from #B4 and load that query into Power BI. Create the best visualization(s) that shows each subcategory name, the price average and the cost average. Create a second visualization that shows the subcategory name and the product count. Do not simply create a table or matrix for these visuals.

Connection Information for Section C:

• Use localhost for the server name, and input the appropriate database name.  Select Windows authentication for the credentials. Power BI Connection.docx
• Once you are logged into the VM, connect Power BI to SQL Server.
For each question include:
• the text version of the SQL script itself (in the Word Doc as mentioned above)
• a screenshot of the SQL script in action (in the Word Doc as mentioned above)
  • 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.) function in the select statement

To keep the queries more straightforward, use aliases.

Answer rating: 100% (QA)

SQL Scripts for Section A Query A1 sql SELECT BusinessEntityID LastName FirstName EmailAddress FROM PersonPerson ORDER BY LastName ASC Query A2 sql SELECT pBusinessEntityID pLastName pFirstName pnPhon... View the full answer