169|SQL – Workforce Wisdom

Andrew_Wilson · April 9, 2024, 11:03pm

BYU Student Author: @Andrew_Wilson
Reviewers: @Marta_Ellsworth, @Jimmy_Han
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

SQL

Need a program? Click here.

Overview
Congratulations!!! You just got hired as the payroll specialist at Employers Inc. Employers Inc is a new company and we just hired 100 new employees. When they were hired, we collected the following information about their previous jobs:

Average Hours Worked per week
Position at the company
Department they worked in
Previous Hourly Wage

One of your first tasks is to analyze the data collected and check for any discrepancies. Here at Employers Inc we care about our employees and their lifestyle. We try very hard to pay a competitive wage and make sure our employees are working a reasonable number of hours per week. I need you to generate some reports about our new employees and their working history.

Instructions
Download the database and creaet the following queries:

Query 1: How many of our employees, based on position and department, were working 55 or more hours per week based on average hours per week? Your headers should be as following:

Query 2: How many Department chairs were being paid less than $40.00/ hour? Your headers should be as following:

Data Files

Challenge169_Data.accdb

Suggestions and Hints

55 or more suggests you should use >=

Solution

Challenge169_Solution.accdb
Solution Video: Challenge 169|SQL– Workforce Wisdom

Emily_Cook · February 22, 2025, 6:46pm

Time to Complete: 15 minutes
Difficulty: Easy

SELECT e.DeptID, p.positionname, COUNT(e.avghours) AS QTY
FROM Employees e INNER JOIN Positions p on e.PositionID=p.PositionID
WHERE e.avghours>=55
GROUP BY e.DeptID, p.PositionName
ORDER BY COUNT(e.avghours) DESC;
SELECT EmpNum, PositionID, HourlyRate
From Employees
WHERE PositionID=1 AND HourlyRate<40

Nolan_Brewer · February 22, 2025, 7:20pm

Time to Complete: 15 minutes
Difficulty: Easy

Q1
SELECT e.DeptID, p.PositionName, Count([e.AvgHours]) as QTY
FROM Employees e
INNER JOIN Positions p on e.PositionID = p.PositionID
WHERE e.AvgHours >= 55
GROUP BY e.DeptID, p.PositionName;
Q2
SELECT [Employee#] as EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

Cannon_Hamilton · February 24, 2025, 3:08am

Q1) SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QTY
FROM Employees AS e INNER JOIN Positions AS p ON e.PositionID=p.PositionID
WHERE e.AvgHours>= 55
GROUP BY e.DeptID, p.PositionName;

Q2) SELECT [e.Employee#] AS EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.HourlyRate < 40 AND e.PositionID = 1;

Aaron_Greenwood · February 25, 2025, 4:44am

Time to complete: 10 mins
Difficulty: Easy
Solution:

SELECT e.DeptID, p.positionname, COUNT (e.avghours) AS QTY
FROM Employees e INNER JOIN Positions p on e.PositionID=p.PositionID
WHERE e.avghours>=55
GROUP BY e.DeptID, p.PositionName;

SELECT [Employee#], PositionID, HourlyRate
From Employees
WHERE PositionID=1 AND HourlyRate<40

Finlay_Lofthouse · February 25, 2025, 5:05pm

Time: 7 min
Difficulty: Easy/mid
Solutions
Q1
SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QTY

FROM Employees as e

INNER JOIN Positions as p ON e.PositionID = p.PositionID

WHERE e.AvgHours >= 55

GROUP BY e.DeptID, p.PositionName;
Q2
SELECT e.[Employee#] AS EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.PositionID = 1 AND e.HourlyRate < 40;

Josue_Quilantan · February 25, 2025, 5:41pm

Time to Complete: 15 minutes
Difficulty: Beginner

SELECT e.DeptID, p.PositionName, Count(e.AvgHours) AS QTY

FROM Employees e

INNER JOIN Positions p ON e.PositionID=p.PositionID

WHERE e.AvgHours>=55

GROUP BY e.DeptID, p.PositionName

SELECT [Employee#] AS EmpNum, PositionID, HourlyRate

FROM Employees

WHERE PositionID=1 AND HourlyRate<40

Braden_Jones · February 25, 2025, 6:40pm

Time to Complete: 25 mins
Difficulty: Medium-Hard
Solutions:

SELECT E.DeptID,
P.PositionName,
COUNT(*) AS QTY
FROM Employees AS E
INNER JOIN Positions AS P
ON E.PositionID = P.PositionID
WHERE E.AvgHours >= 55
GROUP BY E.DeptID, P.PositionName;

SELECT [E.Employee#] AS EmpNum,
E.PositionID,
E.HourlyRate
FROM Employees AS E
INNER JOIN Positions AS P
ON E.PositionID = P.PositionID
WHERE P.PositionName = “DepartmentChair”
AND E.HourlyRate < 40;

Andrew_Wells · February 25, 2025, 10:15pm

Time to Complete: 20 minutes
Rating: moderate
Solutions:
1.
SELECT Employees.DeptID, Positions.PositionName, COUNT([Employees.AvgHours]) AS QTY
FROM Employees
INNER JOIN Positions on Employees.PositionID = Positions.PositionID
WHERE Employee.AvgHours >= 55
GROUP BY Employee.DeptID, Positions.PositionName;
2.
SELECT [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

Colter_Sill · February 25, 2025, 11:22pm

Time to Complete: 15 Mins
Difficulty: Beginner
Solutions:
1.SELECT e.DeptID, p.PositionName, Count(AvgHours) AS QTY
FROM Employees AS e INNER JOIN Positions AS p ON e.PositionID = p.PositionID
WHERE e.AvgHours >= 55
GROUP BY e.DeptID, p.PositionName
ORDER BY count(AvgHours);
2.SELECT e.[Employee#] AS EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.PositionID = 1 and e.HourlyRate < 40
ORDER BY e.HourlyRate ;

Elisabeth_Ware · February 25, 2025, 11:23pm

Time to Complete: 10 minutes
Difficulty: Easy
Solutions:

SELECT e.DeptID, p.PositionName, Count(e.AvgHours) as QTY
FROM Employees e
INNER JOIN Positions p on e.PositionID = p.PositionID
WHERE e.AvgHours >= 55
GROUP BY e.DeptID, p.PositionName;

2.SELECT [Employee#] as EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

Dayna_Ranta · February 25, 2025, 11:45pm

Time to Complete: 5 minutes
Level: Easy
Solutions:

SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QTY
FROM (Employees AS e INNER JOIN Positions AS p ON e.PositionID=p.PositionID)
WHERE e.AvgHOURS >= 55
GROUP BY e.DeptID, p.PositionName;
SELECT EmpNum, PositionID, HourlyRate
FROM Employees
WHERE PositionID = 1 AND HourlyRate < 40;

Elizabeth_Bisel · February 26, 2025, 2:49am

Time to complete: 20 minutes
Difficulty: Easy

SELECT E.DeptID, P.PositionName, Count(AvgHours) as QTY
FROM Employees as E INNER JOIN Positions as P ON E.PositionID=P.PositionID
WHERE E.AvgHours>= 55
GROUP BY E.DeptID, P.PositionName
ORDER BY COUNT(AvgHours) desc;
SELECT [Employee#], PositionID, HourlyRate
FROM Employees
WHERE PositionID = 1 AND HourlyRate<40
ORDER BY HourlyRate DESC;

Emma_Fromm · February 26, 2025, 6:22pm

Time to Complete: 15 minutes
Rating: Beginner

Query 1 Solution:
SELECT DeptID, PositionName, COUNT(AvgHours) AS QTY
FROM Employees INNER JOIN Positions ON Employees.PositionID = Positions.PositionID
WHERE AvgHours >= 55
GROUP BY DeptID, PositionName
ORDER BY COUNT(AvgHours) DESC;
Query 2 Solution:
SELECT [Employee#], PositionID, HourlyRate
FROM Employees
WHERE PositionID = 1 AND HourlyRate < 40
ORDER BY HourlyRate DESC;

Tyson_Burrup · February 26, 2025, 6:52pm

Time to Complete: 15 minutes
Difficulty Beginner

Query 1
SELECT e.DeptID, p.PositionName, Count(e.AvgHours) AS QTY
FROM Employees e
INNER JOIN Positions p ON e.PositionID=p.PositionID
WHERE e.avghours>=55
GROUP BY e.DeptID, p.PositionName;

Query 2
SELECT [Employee#] as EmpNum, PositionID, HourlyRate
From Employees
Where PositionID = 1 and HourlyRate < 40

Clara_Fletcher · February 26, 2025, 9:18pm

Time: 20 mins
Difficulty: Medium
Solution:

SELECT d.deptid, p.positionname, COUNT(e.AvgHours) as QTY
FROM (Departments AS d INNER JOIN employees AS e ON d.deptid=e.deptid)
INNER JOIN Positions AS p ON e.positionid=p.positionid
WHERE e.AvgHours>=55
GROUP BY d.deptid, p.positionname;
SELECT [Employee#] AS empnum, positionid, HourlyRate
FROM employees
WHERE employees.HourlyRate<40 AND employees.positionid=1;

Canon_Rogers · February 26, 2025, 9:18pm

SELECT Departments.DeptID, Positions.PositionName,Count(Employees.AvgHours) as QTY
From ( Departments Inner Join Employees on Departments.DeptID=Employees.DeptID)
Inner Join Positions on Positions.PositionID=Employees.PositionID
Where Employees.AvgHours>=55
Group by Departments.DeptID, Positions.PositionName
2)SELECT Employees.[Employee#] as EmpNum, Employees.PositionID, Employees.HourlyRate
From Employees
Where Employees.HourlyRate<40
And Employees.PositionID=1
Rating: Intermediate
Time: 18 minutes 59 seconds

Clyn_Hansen · February 26, 2025, 9:21pm

Time: 15 Minutes
Difficulty: Relative Ease

SELECT d.DeptID, p.PositionName, e.AvgHours AS QTY
FROM ((Employees e INNER JOIN Positions p ON e.PositionID = p.PositionID) INNER JOIN Departments d ON d.DeptID = e.DeptID)
WHERE e.AvgHours >= 55;
SELECT [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE PositionID = 1 AND HourlyRate < 40;

Morgan_Woodland · February 26, 2025, 9:31pm

Time to Complete: 10
Difficulty: Easy
Solutions:
1.
SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QTY
FROM Employees AS e
INNER JOIN Positions AS p ON e.PositionID = p.PositionID
WHERE e.AvgHours >= 55
GROUP BY e.DeptID, p.PositionName;

SELECT [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

Michelle_Black · February 26, 2025, 10:44pm

SELECT e.DeptID, p.PositionName, COUNT(e.avghours) AS QTY
FROM Employees e INNER JOIN Positions p on e.PositionID=p.PositionID
WHERE e.avghours>=55
GROUP BY e.DeptID, p.PositionName
ORDER BY COUNT(e.avghours) DESC;
SELECT EmpNum, PositionID, HourlyRate
From Employees
WHERE PositionID=1 AND HourlyRate<40

Topic		Replies	Views
178\|SQL – Unions with Precision Challenges sql , union , intro	319	522	February 27, 2025
223\|EXCEL – Finding Salary Increase Challenges excel , audit , real-world-challenges , salary_report	251	472	March 14, 2025
218\|POWERBI – Payroll Pain Challenges intermediate , powerbi , dax , payroll	1	27	December 10, 2024
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	572	February 27, 2025
28\|SQL – Big Money Budget Challenges intermediate , managerial , sql , budget	22	467	April 9, 2025

169|SQL – Workforce Wisdom

Related topics