Filtering using scalar subqueries
Subqueries are incredibly powerful for performing complex filters and transformations. You can filter data based on single (scalar) values using a subquery in ways you cannot by using WHERE statements or joins. Subqueries can also be used for more advanced manipulation of your dataset. You will likely encounter subqueries in any real-world setting that uses relational databases.
In this exercise, you will generate a list of matches where the total goals scored (for both teams in total) is more than 3 times the average for games in the matches_2013_2014 table, which includes all games played in the 2013/2014 season.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Calculate triple the average home + away goals scored across all matches in a subquery.
- Filter the main query for matches where the total goals (home + away goals) exceed the value in the subquery.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select the date, home goals, and away goals scored
date,
home_goal,
away_goal
FROM matches_2013_2014
-- Filter for matches where total goals exceeds 3x the average
WHERE (___ + ___) ___
(SELECT ___ * AVG(___ + ___)
FROM ___);