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 is a 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