Largest cities of a list of countries

H2Read onlyScripts Gallery

A join of two tables of the WORLD sample with a list parameter and a default threshold.

Verification status. Written from the BroadSQL documentation and the H2 documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Practice a Script with two parameters of different kinds.
Compare the largest cities of a few countries.

The Script

Download world-big-cities.sql

-- @description: Largest cities of a list of countries
-- @tags: world, sample, geo, list
-- @status: draft
-- @access: read
-- @dialect: H2
-- @param codes LIST OF TEXT "Three-letter country codes"
-- @param min_population INTEGER = 1000000 "Smallest city population listed"
SELECT CI.NAME AS CITY, CO.NAME AS COUNTRY, CI.POPULATION
  FROM CITY CI
  JOIN COUNTRY CO ON CI.COUNTRYCODE = CO.CODE
 WHERE CO.CODE IN (${codes}) AND CI.POPULATION >= ${min_population}
 ORDER BY CI.POPULATION DESC;

Parameters

Parameter	Type	Default	Meaning
`codes`	`LIST OF TEXT`	Required	Three-letter country codes
`min_population`	`INTEGER`	`1000000`	Smallest city population listed

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/world-big-cities.sql.
Open the sample database: CONNECT WORLD;.
Check what it declares: LIB SHOW gallery/world-big-cities.sql;
Run it: @gallery/world-big-cities.sql codes=('FRA', 'DEU') min_population=500000;

What you get

The cities of the listed countries with at least min_population inhabitants, largest first, with their country name.

Limits

It uses the columns CITY.NAME, CITY.COUNTRYCODE, CITY.POPULATION, COUNTRY.CODE and COUNTRY.NAME of the WORLD sample, as the documentation's examples do.

Found something wrong or unclear on this page? Tell us, with the page address.