Worksheet 4

These questions are on the use of the aggregate or summary functions of MySQL, along with GROUP BY and HAVING.

(Q1) What SQL query will produce a 2-column table listing the number of countries in each continent?

+---------------+----------+
| Asia          |       51 |
| Europe        |       46 |

(Q2) What SQL query will produce the same 2-column table, but only listing the continents with more than 45 countries.

(Q3) What SQL query will produce a 2-column table listing the country codes along with the total number of languages spoken in that country (official or unofficial)?

+-------------+----------+
| ABW         |        4 |
| AFG         |        5 |
| AGO         |        9 |

(Q4) What SQL query will produce a 2-column table listing the country codes along with the total number of official languages in that country?

+-------------+----------+
| ABW         |        1 |
| AFG         |        2 |
| AIA         |        1 |

(Q5) What SQL query will produce a list of country codes together with the total population of all the cities with that country code?

+-------------+-----------------+
| ABW         |           29034 |
| AFG         |         2332100 |
| AGO         |         2561600 |

(Q6) What SQL query will produce a list of the country names together with the total population of all the cities in that country? (not the population of the country, but the sum of the city populations)

+-------------+----------------------+
| Afghanistan |              2332100 |
| Albania     |               270000 |
| Algeria     |              5192179 |

(Q7) What SQL query will produce a list of the number of countries in each region of each continent?

+-----------+---------------------------+----------+
| Asia      | Eastern Asia              |        8 |
| Asia      | Middle East               |       18 |
| Asia      | Southeast Asia            |       11 |
| Asia      | Southern and Central Asia |       14 |

(Q8) What SQL query will produce a list of the number of countries in each region of each continent, along with the total number in each continent?

+-----------+---------------------------+----------+
| Asia      | Eastern Asia              |        8 |
| Asia      | Middle East               |       18 |
| Asia      | Southeast Asia            |       11 |
| Asia      | Southern and Central Asia |       14 |
| Asia      | NULL                      |       51 |

(Q9) What SQL query will produce a list of the number of English-speaking countries (official language) in each region of each continent, along with the total number in each continent? (Regions with no English-speaking countries will simply be absent from the list; at this stage do not try to produce a list containing the name of the region alongside 0.)

+-----------+--------------+----------+
| Asia      | Eastern Asia |        1 |
| Asia      | NULL         |        1 |

(Q10) What SQL query will produce a list of the continents together with the number of cities with at least one million people in that continent, but only for continents that have at least 25 million-person cities?

+---------------+----------+
| Asia          |      126 |
| Europe        |       36 |
| South America |       27 |
+---------------+----------+

Written with StackEdit.