1. Major Cities

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

1. Major Cities

Table of Contents

Problem Statement

Example

Try it YourSelf

Solution

SQL Query

Step by Step Approach

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

MYSQL

. . . .

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

Mark as Completed

Table of Contents

Problem Statement

Example

Try it YourSelf

Solution

SQL Query

Step by Step Approach