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.