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, Delhi, 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

Pattern: disjunctive filter (OR predicate)

Name: disjunctive (OR) row filter — keep a row if any of several independent predicates is true.

Predicate truth table (sample rows):

Delhi is the teaching row: small area, large population — OR is what includes it; AND would exclude it.

Why OR can defeat indexes: a single B+tree index on area cannot alone answer area > 3000 OR population > 5e6 without also checking the other limb. Planners may bitmap-OR two index scans, or fall back to a seq scan. When each limb is highly selective and each column is indexed, a UNION of two indexed seeks can be clearer and faster:

SELECT city_name FROM World WHERE area > 3000
UNION
SELECT city_name FROM World WHERE population > 5000000;
-- UNION (not ALL) de-duplicates cities that match both limbs (Tokyo, Shanghai)

When-NOT: if the problem were AND (both conditions), use AND — not OR. If you need every matching limb's cities without deduping identical names from different sources, use UNION ALL carefully.

Wrong approach: WHERE area OR population > 5000000 (invalid / wrong precedence). Always write full comparisons on each side of OR.

Drill: Rewrite to return cities with area > 3000 and population ≤ 5e6 only. Which sample cities remain? (Tokyo, Shanghai — both clear area; New York/Delhi fail the AND.)