Conditions

To easily add a condition, on the results page we can click on SQL-query: Edit, which brings the Query window popup. We add a condition on the country:

SELECT `city_name` , `population` FROM `cities` WHERE country_code = 'zh' ORDER BY `population`  DESC

which displays all cities located in China (ok, we were a bit lazy with data entry, but you get the picture).


Conditions can be expressed using a rich array of operators and functions. Here are two examples:


Finding the Canadian cities with a population over 100000:

WHERE population > 100000 AND country_code = 'ca'

Finding the cities whose name starts with A:

WHERE city_name like 'A%'

Aggregate functions

Summary information may be generated by grouping on a specific column. Here we ask the average city population per country:

SELECT country_code, AVG(population) FROM cities GROUP BY country_code

Other possible aggregate functions are MIN(), MAX(), SUM() and COUNT(), which compute respectively the minimum value, maximum value, sum of values, and number of values. For example, we could get the number of cities per country with:

SELECT country_code, count(city_name) FROM cities GROUP BY country_code


Joins

Normally, a relational database involves many tables, linked on common keys. We may need at times to run queries on more than one table. Linking, or joining, tables can be done using different techniques; we will focus on a simple method involving key comparison.


In the following query, the FROM clause contains a comma-separated list of tables. In the columns list, we use the table name and a dot as a prefix before each column name (not strictly necessary if each column name is only present in one table).

SELECT cities.city_name, cities.population, countries.country_name

FROM cities, countries

WHERE cities.country_code = countries.country_code LIMIT 0,30