LoslegenKostenlos loslegen

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?

Diese Übung ist Teil des Kurses

Improving Query Performance in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

  • 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.

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

-- Countries cold enough for snow year-round
SELECT country_code
  , country
  , COUNT (DISTINCT athlete_id) AS winter_athletes -- Athletes can compete in multiple events 
FROM athletes
WHERE country_code IN (___ ___ ___ ___ WHERE temp_annual < 0)
AND ___ = 'Winter'
GROUP BY country_code, country;
Code bearbeiten und ausführen