Get Started

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_games are 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”

View Course

Exercise instructions

  • Create a query that pulls total bronze_medals, silver_medals, and gold_medals from summer_games.
  • Use a JOIN and a WHERE statement 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 ____;
Edit and Run Code

This exercise is part of the course

Reporting in SQL

IntermediateSkill Level
4.8+
20 reviews

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 JOIN
Exercise 13: Filtering with a subqueryExercise 14: Report 2: Top athletes in nobel-prized countries

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free