read and understand these pages:
Basic Project Requirements (Read This)
Week 2 – Keeping Data Separate from Analysis
Project Instructions:
When the assignment asks for analysis, description, or explanation – please put this in a separate MS Word (*.doc or *.docx) document and submit in addition to the main assignment file.
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 (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.)Links to an external site. function in the select statement
To keep the queries more straightforward, use aliases.
Section A
Create SQL Scripts that:
Selects ALL the BusinessEntityID, LastName, and FirstName fields for the Person’s table
Fields should be in the above order
Add a sort to the query so LastName is alphabetized A – Z
Add the person’s email address to Query #A1
Add the person’s phone number to Query #A2, include the PhoneNumberType Name field (etc. cell, home, work)
Add in the AddressLine1, City, StateProvinceID, and PostalCode fields to Query #A3
Note: two different people could have the same address. This database is designed to store each unique address individually, so SQL Server Management Studio 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.
Note2: You will have to bring in two different Address tables to get this to portion to work.
Modify the Query from #A4 to include the StateProvinceCode, CountryRegionCode, and Name
Section B
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 Subcatgories ( Production.ProductSubcategory), include ProductSubcategoryID and Name in the query
Modify the #B2 query to count the number of products from Production.Products by Subcategory, call the column “ProductCount”
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).
Add a column for the total product inventory quantity for each ProductSubcategory, make sure to name it as well.
Section C
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.
You can copy and paste the SQL Statement in the Connection window (see below for connection details). This is the suggested (and easiest) approach.
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.
Are there any patterns or clusters that you can see in the mapped addresses? If so, what are a few of them?
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?
Use the best visualization that shows a count of people by state, show only those in the United States.
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:
You will need the IP address of your Immersion lab (set up in week 5) and the username and password to accomplish Section C.
Remember to log into the Aloft Virtual Machine (VM) you need the following URL: https://maryville.okta.com/ (Links to an external site.)Links to an external site.
Once you are logged into the VM, you will need the following information to connect Power BI to SQL Server:
Your Immersion lab IP address
Database: AdventureWorks2016
The username and password are the default:
username: sa
password: pa55word
Make sure you have “Database” selected in your Authentication method in Power BI’s Access a SQL Server Database connection screen. Otherwise, the system won’t recognize your login.
Note: You cannot log into SQL Server from Power BI that may be installed on your computer. You can only connect to this instance of SQL Server from Power BI that is within the Virtual Machine
ASSIGNMENT COMPLETED AT https://capitalessaywriting.com
MAKE YOUR ORDER AND GET THE COMPLETED ORDER
