1. 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

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

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

Select City Names: Begin with a SELECT statement to fetch the city_name from the World table.
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