hello guys , hope you are having a nice day
i got a problem solving a question on my assignment , hope to find some1 that can help me on it .
the question is :
For each department-- if its average salary is less than the average salary of all departments-- display its number, name and number of its employees.
that was my sql code but i ended up with an error :
SELECT Dnum, Dname, count(e.SSN) AS [Number of employees]
FROM departments AS d, employee AS e
GROUP BY dnum, dname
HAVING (select avg(salary) from employee group by dno) < avg(salary);
(not quite up to best practice, though, because of the "naked" columns in the query -- you have qualified some of the columns, e.g. e.SSN and d.Dnum, but in any query involving more than one table, you should qualify ~all~ columns)
on the right side of the less than sign, you have AVG(salary) -- where does that come from?
and in the HAVING subquery, what's the purpose of the GROUP BY?