USE SQL_HR;
SELECT
Employee_ID,
CONCAT(First_Name,’ ', Last_Name) AS Employee,
Office_ID,
E.Salary,
(SELECT AVG(Salary)
FROM Employees E1
WHERE E1.Office_ID = E.Office_ID) AS Average_Salary,
(SELECT E.Salary - AVG(Salary)
FROM Employees E2
WHERE E2.Office_ID = E.Office_ID) AS Difference
FROM Employees E
WHERE Salary >
(SELECT AVG(Salary)
FROM Employees
WHERE Office_ID = E.Office_ID)
ORDER BY Office_ID, Salary DESC, Difference DESC;
| Employee_ID | Employee | Office_ID | Salary | Average_Salary | Difference |
|---|---|---|---|---|---|
| 56274 | Keriann Alloisi | 1 | 110150 | 91701 | 18449 |
| 37851 | Sayer Matterson | 1 | 98926 | 91701 | 7225 |
| 40448 | Mindy Crissil | 1 | 94860 | 91701 | 3159 |
| 67009 | North de Clerc | 2 | 114257 | 74008 | 40249 |
| 67370 | Elladine Rising | 2 | 96767 | 74008 | 22759 |
| 76196 | Mirilla Janowski | 3 | 119241 | 96977 | 22264 |
| 72540 | Guthrey Iacopetti | 3 | 117690 | 96977 | 20713 |
| 84791 | Hazel Tarbert | 4 | 93760 | 81262 | 12498 |
| 95213 | Cole Kesterton | 4 | 86119 | 81262 | 4857 |
| 115357 | Ivy Fearey | 5 | 92710 | 70083 | 22627 |
| 98374 | Estrellita Daleman | 5 | 70187 | 70083 | 104 |