w3resource

SQL Challenges-1: Big Cities

SQL Challenges-1: Exercise-17 with Solution

A city is big if it has an area bigger than 50K square km or a population of more than 15 million.
From the following table, write a SQL query to find big cities name, population and area.

Input:

Table: cities_test

Structure:

FieldTypeNullKeyDefaultExtra
city_namevarchar(255)YES
countryvarchar(255)YES
city_populationint(11)YES
city_areaint(11)YES

Data:

city_namecountrycity_populationcity_population
TokyoJapan135152712191
DelhiIndia167532351484
ShanghaiChina248708956341
Sao PauloBrazil122520231521
Mexico CityMexico92099441485
CairoEgypt95000003085
MumbaiIndia12478447603
BeijingChina2189309516411
OsakaJapan2725006225
New YorkUnited States8398748786
Buenos AiresArgentina3054300203
ChongqingChina3205415982403
IstanbulTurkey155192675196
KolkataIndia4496694205
ManilaPhilippines178014843

Sample Solution:

SQL Code(MySQL):

CREATE TABLE cities_test (city_name varchar(255), country varchar(255), city_population int, city_area int );

INSERT INTO cities_test VALUES ('Tokyo	 		','Japan		',	13515271,	2191	);	
INSERT INTO cities_test VALUES ('Delhi	 		','India		',	16753235,	1484	);	
INSERT INTO cities_test VALUES ('Shanghai	 	','China		',	24870895,	6341	);	
INSERT INTO cities_test VALUES ('Sao Paulo	 	','Brazil		',	12252023,	1521	);	
INSERT INTO cities_test VALUES ('Mexico City	','Mexico		',	9209944,	1485	);	
INSERT INTO cities_test VALUES ('Cairo	 		','Egypt		',	9500000,	3085	);	
INSERT INTO cities_test VALUES ('Mumbai	 		','India		',	12478447,	603		);	
INSERT INTO cities_test VALUES ('Beijing	 	','China		',	21893095,	16411	);	
INSERT INTO cities_test VALUES ('Osaka	 		','Japan		',	2725006,	225		);	
INSERT INTO cities_test VALUES ('New York	 	','United States',	8398748,	786		);	
INSERT INTO cities_test VALUES ('Buenos Aires	','Argentina	',	3054300,	203		);	
INSERT INTO cities_test VALUES ('Chongqing	 	','China		',	32054159,	82403	);	
INSERT INTO cities_test VALUES ('Istanbul	 	','Turkey		',	15519267,	5196	);	
INSERT INTO cities_test VALUES ('Kolkata	 	','India		',	4496694,	205		);	
INSERT INTO cities_test VALUES ('Manila	 		','Philippines	',	1780148,	43		);	
SELECT * FROM cities_test;
SELECT * FROM  cities_test WHERE city_population>=15000000 OR city_area>50000;

Sample Output:

city_name   |country |city_population|city_area|
------------|--------|---------------|---------|
Delhi       |India   |       16753235|     1484|
Shanghai    |China   |       24870895|     6341|
Beijing     |China   |       21893095|    16411|
Chongqing   |China   |       32054159|    82403|
Istanbul    |Turkey  |       15519267|     5196|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Salesperson that makes maximum number of sales amount.
Next: Orders items 5 or more times.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8