Filtering with a subquery

Another approach to filter from a separate table is to use a subquery. The process is as follows:

Create a subquery that outputs a list.
In your main query, add a WHERE statement that references the list.

Your goal is to create the same report as the last exercise, which contains the following characteristics:

First column is bronze_medals, or the total number of bronze.
Second column is silver_medals, or the total number of silver.
Third column is gold_medals, or the total number of gold.
Only summer_games are included.
Report is filtered to only include athletes age 16 or under.

In this exercise, use the subquery approach.

This exercise is part of the course

Reporting in SQL

View Course

Exercise instructions

Create a query that pulls total bronze_medals, silver_medals, and gold_medals from summer_games.
Setup a subquery that outputs all athletes age 16 or below.
Add a WHERE statement that references the subquery to filter for athletes age 16 or below.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Pull summer bronze_medals, silver_medals, and gold_medals
SELECT 
	____, 
    ____, 
    ____
FROM ____
-- Add the WHERE statement below
WHERE ____ IN
    -- Create subquery list for athlete_ids age 16 or below    
    (SELECT ____
     FROM ____
     WHERE ____);

Edit and Run Code