Question

What is the SQL query to select the name, country code, and urban area population from the cities table where the name is in the list of capitals from the countries table, ordered by urban area population in descending order?

Answer 1

The SQL query to select the name, country code, and urban area population for capital cities from the 'cities' table, and order the results by the urban area population in descending order, is a JOIN operation that combines the 'cities' and 'countries' tables based on the city name matching the capital in the 'countries' table.

Step-by-step explanation:

The question pertains to constructing an SQL query to retrieve data from a 'cities' table based on a condition that involves another table, namely 'countries'. In this particular case, we are interested in selecting the name, country code, and urban area population of cities that are capitals, as listed in the countries table. Furthermore, the results must be ordered by the urban area population in descending order.

The SQL query that would achieve this might look something like this:

SELECT c.name, c.country_code, c.urban_area_population FROM cities c JOIN countries co ON c.name = co.capital ORDER BY c.urban_area_population DESC;

This query makes use of a JOIN operation to combine rows from the 'cities' and 'countries' tables on the condition that the city's name matches the capital name in the countries table.