1. Learn
    2. /
  2. Courses
    3. /
  3. Improving Query Performance in PostgreSQL

Exercise

Filtering to freezing with a subquery

From the first Olympics in 1904 through the 2016 games, African countries have sent 7,845 athletes. However, only 55 of those athletes competed in the Winter Olympics.

One-quarter of Africa is covered by the Sahara, and the non-desert areas have year-round heat. Perhaps this lack of cold weather and snow limits the training opportunities for Winter Olympians.

Here, you will explore climate data to see if all African countries lack winter sports conditions. The World Bank collects average temperatures and precipitation for each country. Monthly and annual 40-year averages are preloaded with temperature in degrees Celsius (0 is freezing) and precipitation in millimeters.

Examine the climate data, looking for countries below freezing all year. Are there any in Africa?

Instructions

100 XP
  • Limit to athletes participating only in the Winter Olympics, using the season column.
  • Use a subquery to select only those countries from the oclimate table with an average annual temperature (temp_annual) below freezing (0 degrees).
    • Select the country codes, olympic_cc from the oclimate table to filter the country_code from the athletes_recent table.