Cannot get results to show when querying salary of department based on average salary of department and twice minimum salary of entire company

Question

I am attempting to display the department and average salary of employees in said department if it meets a condition: average salary of department must be twice the minimum salary of the entire firm.

select dname, avg(salary) from department, employee where dno=dnumber group by dname having avg(salary) > min(salary)*2;

I have tables for employee, department. My query is below, however it results in an empty table. I believe it is because salary in the halving clause is referencing within department and within employee - or rather it isn't doing this properly.

To just list average salaries by department I used this query which seems to work fine:

select dname, avg(salary) from department, employee where dno=dnumber group by dname;

Any help would be much appreciated.

Thank you!

Answer 1

min(salary) is the minimum of the department, replace it with (select min(salary) from employee)