use the Adventure Works database

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…

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:

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

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
product 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.

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:
Let’s block ads! (Why?)

Do you need any assistance with this question?
Send us your paper details now
We’ll find the best professional writer for you!

 



error: Content is protected !!