169|SQL – Workforce Wisdom

[Dylan_Heap]

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

[Jesurun_Ritter]

time to complete 15
difficulty easy

Q1:
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
ORDER BY COUNT(Employees.avghours) DESC;

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

[Matthew_Herrod]

Time to Complete: 15 minutes
Rating: Intermediate
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;

[Aubrey_Millar]

time: 20 minutes
beginner
1.SELECT deptid, p.positionname, COUNT (avghours) as qty
FROM employees as e
INNER JOIN Positions AS p ON e.PositionID=p.PositionID
WHERE avghours >= 55
GROUP BY deptid, p.positionname
ORDER BY COUNT(avghours) desc
2.SELECT[Employee#] as EmpNum, PositionID, HourlyRate
FROM Employees
WHERE PositionID=1 AND HourlyRate<40
ORDER BY HourlyRate DESC;

[Benjamin_White]

Time to Complete: 5 minutes
Level: 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 PositionID = 1 AND HourlyRate < 40;

[Camryn_Russon]

Time to Complete: 10 minutes
Level: Easy

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

[Alycia_Larimer]

Time to Complete: 10 mins
Rating: Easyish
Solution:

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

2. SELECT Employee# AS EmpNum, PositionID, HourlyRate
   FROM Employees
   WHERE PositionID = 1 AND HourlyRate<40

[Michael_Coy]

Time to Complete: 15 minutes
Difficulty: Easy

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
   ORDER BY COUNT(e.avghours) DESC;
2. SELECT EmpNum, PositionID, HourlyRate
   From Employees
   WHERE PositionID=1 AND HourlyRate<40

[Aishwarya_Ramesh]

Time to Complete: 10 minutes
Difficulty: Easy
Solution:
Q1
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;

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

[Austin_Koslow]

Time to Complete: 10 minutes
Difficulty: Easy

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

[Shiro_Contreras]

Time to Complete: 10 min
Rating: Easy
Solution:

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
2. SELECT Employee# AS EmpNum, PositionID, HourlyRate
   FROM Employees
   WHERE PositionID = 1 AND HourlyRate<40;

[Josh_Graham]

Time 10
Difficulty Intermediate
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
ORDER BY COUNT(e.avghours) DESC;
2).SELECT [Employee#] as EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

[Abigail_Woodbury]

Time to Complete: 15
Difficulty: Medium
Solution:

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
2. SELECT [Employee#] as EmpNum, PositionID, HourlyRate
   From Employees
   where HourlyRate < 40 AND PositionID = 1

[Ryan_Knapp]

Time to Complete: 10 minutes
Difficulty: Easy

Solutions:

1. SELECT e.DeptId, p.PositionName, COUNT(e.AvgHours) AS QTY
   FROM PositionID AS p
   INNER JOIN Employees AS e ON p.PositionID=e.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;

[Erica_Huang]

Time to complete: 15mins
Rating: Intermediate
1.
SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QTY
FROM Employees e
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 PositionID = 1 AND HourlyRate < 40

[Trevin_Elliott]

Time to Complete: 15 Minutes
Difficulty: Beginner
1:
SELECT e.DeptID, p.PositionName, Count([e.AvgHours]) as QTY
FROM Employees as e
INNER JOIN Positions as p on p.PositionID = e.PositionID
WHERE e.AvgHours >= 55
GROUP BY e.DeptID, p.PositionName
2:
SELECT [Employee#] as EmpNum, PositionID, HourlyRate
FROM Employees
WHERE PositionID = 1 AND HourlyRate < 40

[Nicole_Jackman]

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

[Chase_Torgesen]

Time to Complete- 10 mins
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

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

[Braden_Kofford]

Time 20 Minutes
Rating Easy

1. SELECT e.DeptID, p.positionname, COUNT(e.avghours) AS QTY
   FROM Employees AS 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
2. SELECT EmpNum, PositionID, HourlyRate
   FROM Employees
   WHERE PositionID=1 AND HourlyRate<40

[Crew_Jensen]

Time to Complete: 15 Minutes
Difficulty: Moderate
1.
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;
2.
SELECT [Employee#] as EmpNum, PositionID, HourlyRate
From Employees
Where PositionID = 1 AND HourlyRate < 40