How to aggregate and count avg value by groups from 2 tables in sql?

Question

I have 2 tables , department and employee. I need to get department name and average age of it.

create table Dep(name_dep char, id_dep int);

insert into Dep values("econ", 1);
insert into Dep values("credit", 2);
insert into Dep values("energy", 3);
insert into Dep values("retail", 4);
insert into Dep values("manufactury", 5);

create table Emp(id_emp int, id_dep int, age int, person_name char, salary int );
insert into Emp values(1, 1, 23, 'john', 200);
insert into Emp values(3, 2, 3, 'dalbai', 100);
insert into Emp values(6, 3, 53, 'borat', 300);
insert into Emp values(7, 1, 63, 'erjan', 1600);
insert into Emp values(9, 2, 73, 'sergey', 1000);
insert into Emp values(8, 5, 83, 'lucy', 20);
insert into Emp values(90, 4, 93, 'mike', 1200);

How to select dept name and avg age of employees in that dept?

SELECT name_dep, average_age
FROM Emp e
INNER JOIN
(
    SELECT name_dep, AVG(age) AS average_age
    FROM Dep d
    GROUP BY id_dep
) d
    ON e.id_dep = d.id_dep

Answer 1

YOu can try below -

DEMO

select name_dep,avg(age)
from emp a inner join dep b on a.id_dep=b.id_dep
group by a.id_dep,name_dep