169|SQL – Workforce Wisdom

Challenges
, , ,
61

Time to Complete: 10 minutes
Difficulty: Easy
Query 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;
Query 2:
SELECT E.[Employee#] AS EmpNum, E.PositionID, E.HourlyRate
FROM Employees AS E
WHERE PositionID = 1 AND HourlyRate < 40

62

Time to Complete: 13 minutes
Difficulty: Beginner-Intermediate

Solutions:
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 p.PositionName, e.DeptID;

2.)SELECT [e.Employee#] AS EmpNum, p.PositionID, e.HourlyRate
FROM Employees e
INNER JOIN Positions p ON e.PositionID = p.PositionID
WHERE e.HourlyRate < 40.00 AND p.PositionID = 1;

63

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below

1:
SELECT Employees.DeptID, Positions.PositionName, COUNT([Employees.AvgHours]) AS QTY
FROM Employees
INNER JOIN Positions ON Employees.PositionID = Positions.PositionID
WHERE Employees.AvgHours >= 55
GROUP BY Employees.DeptID, Positions.PositionName;

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

64

time to complete: 10min
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

  1. SELECT [Employee#] AS EmpNum, PositionID, HourlyRate

FROM Employees

WHERE PositionID=1 AND HourlyRate<40

65

Time to Complete: 15 Min
Difficulty:Easy

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 [Employee#] AS EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.HourlyRate < 40 AND e.PositionID=1

66

Time to Complete: 20 minutes
Difficulty: Medium
Q1
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;
Q2
SELECT [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE PositionID=1 AND HourlyRate<40;

67

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

Q2
SELECT e.EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.PositionID = 1 AND e.HourlyRate < 40;