Now that the contracts are signed, we are providing more detailed guidance for the team that will...

Question

Now that the contracts are signed, we are providing more detailed guidance for the team that will help us with ourMerger Project. This memo addresses the following topics:

Analysis
Final Deliverable

Analysis

Given the new make-up of the organization Smart-Buy wants to understand some specifics of the new employee base. Below are a few questions that will aid in that endeavor.

Which departments in which states had the highest total payroll (gross pay) costs in 2018?List only top 10.

Several accounting department employees believe that their paychecks were calculated incorrectly. Management has asked that you produce a report that shows all accounting department employees grouped by state, with the employee_id, last name, first name, total gross salary. This report should be in alphabetical order from A – Z.INDICATE HOW MANY ROWS OF DATA DOES YOUR TABLE HAVE.

Smart-Buy wants to understand how many periods their seasonal workers worked during the busy season (4thquarter of 2017) and what was their total pay. In your output provide Employee ID, Last Name, total pay for each employee, number of periods. Order by total gross pay.INDICATE HOW MANY ROWS OF DATA DOES YOUR TABLE HAVE.

If smart-buy wants to relocate headquarters, where should they do this? HINT: It is easier to use at least 2 queries to answer this question. Take into consideration where the employees associated with headquarters (for example: accountants, IT workers, executives, and possibly others) currently live. Also consider the location of our most lucrative stores.

Figure out how to provide a heatmap (use excel) of the location of the most lucrative stores, however you can be creative and use other tools if you like.

Final Deliverable

For the purposes of insuring data integrity, all the analysis should be run from the same database that was created during previous ETL assignment.

Final report should be a word document.
This document must list the question. State your analysis (if any, describing your approach to solving the questions).
For each question, you need to have a copy of your SQL queryaccompanied bythe result of the query (a clear picture of the table with final number of rows and columns visible to naked eye).
One visualization is required for #4. You may do your visualization in Excel or Tableau, but the picture must be included in your word document. If used anything but Excel, provide the original files/documents too.
For questions #4 provide your analysis.

RUBRIC – Total 50 points:

Queries 1-3: 10 points each (query, table with correct rows)

Query 4: 15 points

Query 4 Visual: 5 point

database-bx5eugnv-oys00xrr.docx

Bikram · Accepted Answer

1. 1Which departments in which states had the highest total payroll (gross pay) costs in 2018?List only top 10.
select top 10 d.department,p.gross_pay,a.state
from
employee_info e,
(
select p.employee_id , sum(p.gross_pay)  as gross_pay from payroll_register P
where  p.period between #01/01/2018# and #12/31/2018#
group by p.employee_id 
)P,
addresses a,
departments d
where e.employee_id=p.employee_id
and e.address_id=a.address_id
and e.department_id=d.department_id
order by p.gross_pay desc
2. Several accounting department employees believe that their paychecks were calculated incorrectly. Management has asked that you produce a report that shows all accounting department employees grouped by state, with the employee_id, last name, first name, total gross salary. This report should be in alphabetical order from A – Z.INDICATE HOW MANY ROWS OF DATA DOES YOUR TABLE HAVE.
select a.state,e.employee_id,e.first_name,e.last_name,p.

Now that the contracts are signed, we are providing more detailed guidance for the team that will help us with our Merger Project . This memo addresses the following topics: Analysis Final Deliverable...

Analysis

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment