Exercise

Subquery inside SELECT

As explored in the video, there are often multiple ways to produce the same result in SQL. You saw that subqueries can provide an alternative to joins to obtain the same result.

In this exercise, you'll go further in exploring how some queries can be written using either a join or a subquery.

In Step 1, you'll begin with a LEFT JOIN combined with a GROUP BY to select the nine countries with the most cities appearing in the cities table, along with the counts of these cities. In Step 2, you'll write a query that returns the same result as the join, but leveraging a nested query instead.

Instructions 1/2

undefined XP
    1
    2
  • Write a LEFT JOIN with countries on the left and the cities on the right, joining on country code.
  • In the SELECT statement of your join, include country names as country, and count the cities in each country, aliased as cities_num.
  • Sort by cities_num (descending), and country (ascending), limiting to the first nine records.