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.
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 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
- 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 listingof 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 product Name (A-Z).
- Add a column for the total product inventory quantity for each ProductSubcategory, make sure to name it as well.
- 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 createa table or matrix for these visuals.
For Section C:
You will need the SQL Server address and user information to accomplish Section C.
- Server: SQL
- Database: AdventureWorks2014
Make sure you have Database selected and not Windows. Otherwise the system won’t recognize your login:
Prime Essay Services , written from scratch, delivered on time, at affordable rates!