MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table works(
employee_name varchar (100) primary key,
company_name varchar (100),
salary int (6) );
INSERT INTO works VALUES ('a','comp A',100),('a2','comp A',150),('a3','comp A',250),('a4','comp B',100)
,('a5','comp B',300),('a6','comp B',200)
select employee_name,company_name,salary
from works w1
where salary >= (select avg(salary) avgsal from works w2 WHERE w1.company_name = w2.company_name 
group by company_name
)
;


employee_name
company_name
salary


a3
comp A
250

a5
comp B
300

a6
comp B
200


select avg(salary) avgsal from works w2  
group by company_name


avgsal


166.6667

200.0000