Hello, I'm attempting to teach myself SQL from the book "Essence of Databases" by F.D. Rolland. I've stumbled across an exercise which has me tearing my hair out a little.

Given a table STAFF
with fields StaffID, Sname, Job, Sal, BranchID

Attempting to identify all staff whose Salary (SAL) is greater than the AVG(SAL) of their respective Branch (identified by BranchID)

So, finding the average salary for all staff at each branch is simple enough -

SELECT AVG(sal) FROM staff GROUP BY branchid
/

So I tried something along these lines

SELECT sname Surname, sal Salary FROM staff
WHERE sal > ( SELECT AVG(sal) FROM staff
GROUP BY branchid )
/

However, this is an error, because the > operator cannot be used in a situation where a nested subquery returns more than one tuple

So. knowing that there were only 3 Branches, identified by 10, 20 and 30, I attempted the following

SELECT sname Surname, sal Salary FROM staff
WHERE SAL > ( SELECT AVG(sal) FROM staff
WHERE BRANCHID = 10
GROUP BY branchid )
OR SAL > ( SELECT AVG(sal) FROM staff
WHERE branchid = 20
GROUP BY branchid )
OR SAL > ( SELECT AVG(sal) FROM staff
WHERE branchid = 30
GROUP BY branchid )
ORDER BY branchid
/

Of course, this looks up all staff whose salary is merely less than the lowest average sal.

Somehow I need to be able to use the > operator on grouped data, or I'm missing something (Such as, how to get a nested query to run multiple times with a different branchid, and a different avg salary for that particular branch).

Has anyone got any other ideas? Thanks for reading.