Subqueries in WHERE clause

The following example finds cities (from the cities example) whose population is below the average temperature (obtained via a sub-qquery):

SELECT name, pop2000 FROM cities 
WHERE pop2000 < (SELECT avg(pop2000)  FROM cities);

Here: the subquery (SELECT avg(pop2000) FROM cities) is used to specify conditions in the WHERE clause. The result is:

| name | pop2000 | |—————– | ––––| | San Francisco | 776733 | | ST LOUIS | 348189 | | Kansas City | 146866 |