Filtering with a JOIN
When adding a filter to a query that requires you to reference a separate table, there are different approaches you can take. One option is to JOIN to the new table and then add a basic WHERE statement.
Your goal is to create a report with 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 JOIN 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. - Use a
JOINand aWHEREstatement to filter for athletes ages 16 and 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 ____ AS s
JOIN ____ AS a
ON ____
-- Filter for athletes age 16 or below
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.