SQL query. How to choose the number of cities by region?

Question

MySQL

Hello.
There is a country table with fields Code (records like: RUS, BEL, KAZ) and Region (records like: Asia, Europe, Africa). There is also a city table with fields ID, Name (records like: Moscow, Minsk, Beijing) and CountryCode (records like: RUS, BEL, KAZ).
How to display the resulting table, where in one column are the names of the regions, and in the second the number of cities in each region?
Well, that is:
Region | Cities
---------+----------
Europe |... 100
Africa ..|.... 80
Asia .....|.... 300

Answer 1

Select R.Code as Region, count(C.ID) as Cities
from Region R
Inner join city C
on R.Code = C.CountryCode
group by R.Code