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
WHEREstatement 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_gamesare 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”
Exercise instructions
- Create a query that pulls total
bronze_medals,silver_medals, andgold_medalsfromsummer_games. - Setup a subquery that outputs all athletes age 16 or below.
- Add a
WHEREstatement 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 ____);This exercise is part of the course
Reporting in SQL
Learn how to build your own SQL reports and dashboards, plus hone your data exploration, cleaning, and validation skills.
Queries can get large, fast. It's important to take a logical approach when building more complicated queries. In this chapter, you will take a step-by-step approach to plan and build a complex query that requires you to combine tables in multiple ways and create different types of fields.
Exercise 1: Planning the queryExercise 2: Planning the SELECT statementExercise 3: Planning the filterExercise 4: Combining tablesExercise 5: JOIN then UNION queryExercise 6: UNION then JOIN queryExercise 7: Creating custom fieldsExercise 8: CASE statement refresherExercise 9: BMI bucket by sportExercise 10: Troubleshooting CASE statementsExercise 11: Filtering and finishing touchesExercise 12: Filtering with a JOINExercise 13: Filtering with a subqueryExercise 14: Report 2: Top athletes in nobel-prized countriesWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.