Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Prepare SQL Scripts that: Select ALL the BusinessEntityID, LastName, and FirstName fields for the Person's table Fields should be in the above order. Add a

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.

Step by Step Solution

3.43 Rating (159 Votes )

There are 3 Steps involved in it

Step: 1

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... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Programming With Microsoft Visual Basic 2017

Authors: Diane Zak

8th Edition

1337102121, 9781337517058, 978-1337102124

More Books

Students also viewed these Accounting questions

Question

22. Why is tPA not helpful in cases of hemorrhagepg105

Answered: 1 week ago