Microsoft Access and SQL Homework

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

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.

1. Several employees believe that their paychecks were calculated incorrectly. Management has asked that you produce a report that shows all employees hired after 2/15/2018 living in Oklahoma in the accounting executive department that made more than $55,000 in the 3rd Quarter of 2018. Report the employee_id, last name, department name, total gross salary. This report should be in alphabetical order from A – Z.

2. What were the top 75 seasonal employees that were not seasonal that worked for Smart-Buy more than 180 hours in December of 2018? Report the city and state in which they worked, their hourly_rate, total number of hours worked, and marital status, in descending order based upon the number of hours worked.

3. Smart-Buy wants to understand if most of their seasonal workers worked more than 4 periods during the busy season (4th quarter). List (by employee id and last name) the number of periods worked and the total gross_pay for seasonal retail workers in the 4th quarter of 2017 and the 4th quarter of 2018. If the results of this query are more than 100 records, only report the first 10 in your write-up.

the number of periods -> use COUNT(Calender.period) GROUP BY

if group by doesn’t work with both SUM and COUNT

SELECT …, a.total_gross_pay

FROM (

SELECT … FROM … GROUP BY ) a, EmplInfo b

WHERE a.employee_id = b.employee_id

“If the results of this query are more than 100 records, only report the first 10” (Maybe Microsoft Access specific syntax -> Google)

4. If smart-buy wants to relocate headquarters, where should they do this? Please use at least 2 queries to answer this question. Take into consideration where the employees that work jobs that would be 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 of the location of the most lucrative stores.

SELECT cities, state, jobs, count(employee)

From ,…..

Order by state, count(employee)

Select stores, sum(revenue)

From ….

· Turn in a final report in a word document. You will need to have at least 2 visualizations. One visualization is required for #4 and you can choose one other query to visualize. You may do your visualization in Excel or Tableau, but the picture must be included in the word document.

· This document must list the question and then the answer to the question.