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.
Questo esercizio fa parte del corso
Data Manipulation in SQL
Istruzioni dell'esercizio
- 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.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
SELECT
date,
home_goal,
away_goal
FROM matches_2013_2014
-- Filter for matches where total goals is greater than 3x the average
WHERE (___ + ___) ___
(SELECT ___ * AVG(___ + ___)
FROM ___);