Category: Competitive Coding

Asian Population

Problem Statement

Given two tables, City and Country whose descriptions are given below. Find the sum of population of all the Cities that lies in “Asia” continent.

City

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

Country

+----------------+-------------+
| Field          | Type        |
+----------------+-------------+
| Code           | char(3)     |
| Name           | char(52)    |
| Continent      | char(50)    |
| Region         | char(26)    |
| SurfaceArea    | float(10,2) |
| IndepYear      | smallint(6) |
| Population     | int(11)     |
| LifeExpectancy | float(3,1)  |
| GNP            | float(10,2) |
| GNPOld         | float(10,2) |
| LocalName      | char(45)    |
| GovernmentForm | char(45)    |
| HeadOfState    | char(60)    |
| Capital        | int(11)     |
| Code2          | char(2)     |
+----------------+-------------+

PS: City.CountryCode and Country.Code is same key.

Solution

  
Select sum(City.Population) from City Inner Join Country ON City.CountryCode = Country.Code and Continent='Asia';

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;

Japanese Cities’ Name

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 name of all the cities of Japan. The CountryCode for Japan is “JPN”.

Solution

  
Select Name from City where CountryCode = 'JPN';

Japanese Cities’ Detail

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 all the details of all the cities of Japan. The CountryCode for Japan is “JPN”.

Solution

  
Select * from City where CountryCode = 'JPN';

Select by ID

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 all the details of the city with ID is 1661.

Solution

  
Select * from City where ID = '1661';

\

Select All

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)  |
+-------------+----------+

write a query that will fetch all columns for every row in the table.

Solution

  
Select * from City;

Ruby Tutorial – Object Methods

Problem Statement

Each object in Ruby may have methods associated with it. To demonstrate this, we want you to print whether a number is even or odd. A number has an even? method associated with it, which returns true or false based on the parity of the number.

Assuming a variable number is already defined, check whether a given number is even or not.

Hint

Type in the code-editor

return number.even?

or

number.even?

Solution

  
# add your code here
return number.even?