Major Cities

Problem Statement

Table: World
Each row in this table represents a city, detailing its name, the country it's in, its area in square kilometers, and its population.

+-----------------+---------+
| Column Name     | Type    | 
+-----------------+---------+
| city_name       | varchar | 
| country         | varchar |
| area            | int     |  -- in square kilometers
| population      | int     | 
+-----------------+---------+
city_name is the primary key for this table.
This table contains information about the city name, the country it is in, its area in square kilometers, and its population.

Develop a solution to identify the names of major cities that either have an area greater than 3000 square kilometers or a population exceeding 5 million people. The output should be sorted in any order.

Example

Input:

World table:
+-----------+---------+-------+------------+
| city_name | country | area  | population |
+-----------+---------+-------+------------+
| Tokyo     | Japan   | 6223  | 13929286   |
| Delhi     | India   | 1484  | 11034555   |
| Shanghai  | China   | 6341  | 24256800   |
| New York  | USA     | 783   | 8336817    |
| Paris     | France  | 105   | 2148271    |
+-----------+---------+-------+------------+

Expected Output:

+-----------+
| city_name |
+-----------+
| Tokyo     |
| Delhi     |
| Shanghai  |
| New York  |
+-----------+

Tokyo, Shanghai, and New York are identified as major cities either due to their large area or high population.

Try it YourSelf

Solution

To find major cities based on their area or population, we'll execute SQL queries to filter and retrieve the needed information.

1. Select City Names: Begin with a SELECT statement to fetch the city_name from the World table.
2. Apply Conditions: Use a WHERE clause to filter cities either with an area > 3000 or population > 5000000.

SQL Query

Here's the SQL query that addresses this:

SELECT city_name
FROM World
WHERE area > 3000 OR population > 5000000;

Step by Step Approach

Step 1: Select City Names

Start by selecting the city_name from the World table, which will form the core of our output.

SELECT city_name
FROM World

Output After Step 1:

+-----------+
| city_name |
+-----------+
| Tokyo     | 
| Delhi     | 
| Shanghai  |
| New York  |
| Paris     |
+-----------+

Step 2: Apply Conditions

Refine the selection by applying conditions to include only cities that are either significantly large in area (area > 3000) or have a substantial population (population > 5000000).

SELECT city_name
FROM World
WHERE area > 3000 OR population > 5000000

Final Output:

+-----------+
| city_name |
+-----------+
| Tokyo     | 
| Delhi     |
| Shanghai  |
| New York  |
+-----------+

For your reference, here is the complete final query:

SELECT city_name
FROM World
WHERE area > 3000 OR population > 5000000