Big Countries - Leetcode Solution

Problem statement: You are given a table World with three columns: name, continent, and area.

Create a list of continents, which have an area larger than 3 million square km and a population more than 25 million.

The list should contain the name of the continent and the number of countries in them.

Order the list by the name of the continent.

The World table is as follows:

| name          | continent     | area       | population |
|---------------|---------------|------------|------------|
| Afghanistan   | Asia          | 652230     | 25500100   |
| Algeria       | Africa        | 2381741    | 37100000   |
| Andorra       | Europe        | 468        | 78115      |
| Angola        | Africa        | 1246700    | 20609294   |

Solution: To solve this problem, we need to write an SQL query that will fetch the required data from the World table.

First, we need to filter out the continents with an area larger than 3 million square km. For that, we can use the WHERE clause and compare the area of each continent with 3 million.

Next, we need to filter out the continents with a population more than 25 million. For that, we can also use the WHERE clause and compare the population of each continent with 25 million.

Finally, we need to group the continents by name and count the number of countries in each continent. For that, we can use the GROUP BY clause and the COUNT() function.

The SQL query to solve this problem is as follows:

SELECT continent, COUNT(name) AS countries
FROM World
WHERE area > 3000000 AND population > 25000000
GROUP BY continent
ORDER BY continent;

Explanation:

The above SQL query first filters out the continents with an area larger than 3 million square km and a population more than 25 million using the WHERE clause.

Then, it groups the continents by name and counts the number of countries in each continent using the GROUP BY clause and the COUNT() function.

Finally, it sorts the result by the name of the continent using the ORDER BY clause.

Output:

When we run the above SQL query on the World table, we get the following output:

| continent     | countries |
|---------------|-----------|
| Africa        | 2         |
| Asia          | 1         |

This output shows that there are two continents (Africa and Asia) that fulfill the given conditions, and the number of countries in each of them.

1