Add a subquery in FROM
In the previous exercise, you created a data set listing the average home and away goals in each match stage of the 2012/2013 match season.
In this next step, you will turn the main query into a subquery to extract a list of stages where the average total goals in a stage is higher than the overall average for total goals in a match.
Questo esercizio fa parte del corso
Data Manipulation in SQL
Istruzioni dell'esercizio
- Calculate the average total goals from the match table for each stage in the
FROMclause subquery. - Add a subquery to the
WHEREclause that calculates the overall average total goals. - Filter the main query for stages where the average total goals is higher than the overall average.
- Select the
stageandavg_goalscolumns from thessubquery into the main query.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
SELECT
-- Select the stage and average goals from the subquery
___,
ROUND(___,2) AS avg_goals
FROM
-- Select the stage and average goals in 2012/2013
(SELECT
___,
___(___ + away_goal) AS avg_goals
FROM ___
WHERE season = ___
GROUP BY stage) AS s
WHERE
-- Filter the main query using the subquery
s.avg_goals __ (SELECT ___(___ + away_goal)
FROM match WHERE season = ___);