In order to
complete this assignment you will need to log into the virtual
machine and use the Adventure Works database within SQL Server
the text version
of the SQL script itself
a screenshot of
the SQL script in action
should include both the query and the results
your screenshot includes the results from the GETDATE() function
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.
Selects ALL the
BusinessEntityID, LastName, and FirstName fields for the Person’s
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
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
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
Create a query
that calculates a count of Departments by Group Name from the
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
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 create a
table or matrix for these visuals.
For Section C:
You will need the
SQL Server address and user information to accomplish Section C.
Make sure you
have Database selected and not Windows. Otherwise the system won’t
recognize your login:
Let’s block ads! (Why?)