169|SQL – Workforce Wisdom

[Samantha_Cassell]

Time to complete: 10 minutes
Difficulty: Easy
Solution:

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 HourlyRate<40 AND PositionID>=1;

[Lainey_Jackson]

Time to complete: 15 minutes
Difficulty: Beginner/Intermediate
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 [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;

[Hazel_Bell]

Time to Complete: 7 min
Difficulty: Easy
Solutions:

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

[Taina_Carvalho]

Time to complete: 10
Rating: Beginner
Solution:
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#], PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID =1;

[Maddie_Riley]

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

[Matthew_Pope]

Time to Complete: 20 Minutes
Difficulty: Moderate
Solution:
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
ORDER BY COUNT(e.AvgHours) DESC;
Q2:
SELECT [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40
AND PositionID = 1;

[Lucas_Spillett]

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

[aefolsom]

Time to complete: 15mins
Difficulty: Medium
Solution:
1:
SELECT d.DeptID, p.PositionName, COUNT(e.AvgHours) as QTY
FROM (Departments as d INNER JOIN Employees as e ON e.DeptID = d.DeptID) INNER JOIN Positions as p ON p.PositionID = e.PositionID
WHERE AvgHours >= 55
group by d.DeptID, p.PositionName;
2:
SELECT e.[Employee#] as EmpNUM, e.PositionID, e.HourlyRate
FROM Employees as e
WHERE e.PositionID = 1 AND e.HourlyRate < 40.00;

[Ava_Lassiter]

Time to complete: 20 minutes
Difficulty: Easy
Solution:

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) desc;

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

[Eliza_Simister]

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

[Ethan_Anderson]

Time to complete: 10 minutes
Difficulty: Easy

1. select departments.DeptID, positions.PositionName, count([employees.Employee#]) as QTY
   from (Departments
   inner join employees on departments.DeptID = employees.DeptID)
   inner join positions on employees.PositionID = positions.PositionID
   where AvgHours >= 55
   group by departments.DeptID, positions.PositionName

2. select [Employee#] as EmpNum, PositionID, HourlyRate
   from employees
   where PositionID = 1
   and HourlyRate < 40

[Jackson_Frank]

Time to complete: 10 Minutes
Difficulty Easy
queries:

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
ORDER BY COUNT(e.avghours) DESC

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

[Harry_Kirk]

10 minutes
Easy-Medium

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

SELECT e.employee# AS EmpNum, e.positionid, e.hourlyrate
FROM Employees e
JOIN Positions p on e.positionid = p.positionid
WHERE e.positionid= 1 AND e.hourlyrate < 40

[Soleil_Hansen]

Time to complete: 15 minutes
Rating: Beginner
Solutions:
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 [Employee#] AS EmpNum, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40 AND PositionID = 1;

[Nolan_Ward]

Time: 12 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, PositionID, HourlyRate
FROM Employees
WHERE PositionID= 1 AND HourlyRate< 40;

[Vincent_Hamer]

Time: 15 min
Difficulty: Easy
Solution
1
SELECT e.DeptID, p.PositionName, COUNT(e.AvgHours) AS QT
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 e.[Employee#] AS EmpNum, e.PositionID, e.HourlyRate
FROM Employees AS e
WHERE e.PositionID = 1 AND e.HourlyRate < 40;

[Grace_Jeppson]

time to complete: 15
difficulty: intermediate
solutions:
1.
SELECT e.DeptName, p.PositionName, COUNT(e.AvgHours) AS QTY
FROM Employees e INNER JOIN Positions p ON e.positionID = p.positionID
WHERE e.QTY >= 55
GROUP BY e.DeptName, p.PositionName;
2.
SELECT EmpName, PositionID, HourlyRate
FROM Employees
WHERE HourlyRate < 40.00 AND PositionID = 1;

[Katie_S_Richards]

Time to Complete: 12 Min
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;

[Helina_Uibel]

Time to complete: 10 minutes
Difficulty: 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 e.Employee# AS EmpNum, e.PositionID, e.HourlyRate
   FROM Employees AS e
   WHERE e.HourlyRate < 40 AND e.PositionID = 1

[Addison_Kingston]

Time to Complete: 20 Minutes
Difficulty: Easy
Solution:

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 HourlyRate < 40 AND PositionID = 1