Tag: Aggregation

Population Density Difference

Problem Statement

Given a City table, whose fields are described as

+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+

print the difference between the maximum and minimum city populations.

Solution

  
select max(Population)-min(Population) from City;

Japan Population

Problem Statement

Given a City table, whose fields are described as

+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+

you have to print the sum of population of all the cities of Japan. The CountryCode for Japan is “JPN”.

Solution

  
select sum(Population) from City where CountryCode= 'JPN';

Average Population

Problem Statement

Given a City table, whose fields are described as

+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+

you have to print the average population of all cities, rounded down to the nearest integer.

Solution

  
select ROUND(avg(Population),0) from City;