How to create such a SQL query?

Question

MySQL

Hello.
There are three tables:
country

city

countrylanguage

It is necessary to display the resulting table like:

I managed to display everything except the number of cities and languages ​​for each region:

SELECT Continent, Region, COUNT(Name) as Countries, ROUND(AVG(LifeExpectancy), 2) as LifeDuration, SUM(Population) as Population
FROM `country`
GROUP BY Region
ORDER BY Continent, Region

How to display 2 more columns (number of cities and languages ​​for each region)?

Answer 1

select
t3.Continent,
t3.Region,
sum(t3.Countries),
sum(t3.LifeDuration),
sum(t3.Population),
sum(t3.Cities),
sum(t4.Languages)
from
(
select
  t1.Code,
  t1.Continent,
  t1.Region,
  t1.Countries,
  t1.LifeDuration,
  t1.Population,
  t2.Cities
  from
   ( 
     select
     Code,
     Continent,
     Region,
     count(Name) as Countries,
     ROUND(AVG(LifeExpectancy), 2) as LifeDuration, 
     SUM(Population) as Population
     from country
     group by Code,Continent,Region
) t1 left join 
(
  select
  CountryCode,
  count(Name) as Cities
  from city
  group by CountryCode 
) t2 on t2.CountryCode = t1.Code
) t3 left join 
(
  select 
  CountryCode,
  count(Language) as Languages
  from countrylanguage
) t4 on t4.CountryCode = t3.Code

group by t3.Continent,t3.Region

Answer 2

Good afternoon, try to look towards JOINs.