Get startedGet started for free

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

View Course

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 ___); 
Edit and Run Code