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 home goals in a stage is higher than the overall average for home goals in a match.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Calculate the average home goals and average away goals from the match table for each stage in the FROM clause subquery.
Add a subquery to the WHERE clause that calculates the overall average home goals.
Filter the main query for stages where the average home goals is higher than the overall average.
Select the stage and avg_goals columns from the s subquery into the main query.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

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 = ___);

Edit and Run Code