ALL the subqueries EVERYWHERE

In soccer leagues, games are played at different stages. Winning teams progress from one stage to the next, until they reach the final stage. In each stage, the stakes become higher than the previous one. The match table includes data about the different stages that each match took place in.

Across the next 3 exercises, you will build one query with a subquery in each of the SELECT, FROM, and WHERE clauses. Does the average number of goals scored change as the stakes get higher from one stage to the next?

The query repeats one subquery so you can practice each placement. Every copy reads the match table again, so in your own work you'd calculate the overall average once, as Chapter 4 shows with a window function.

This exercise is part of the course

Data Manipulation in SQL

View Course

Exercise instructions

Extract the average number of total goals in a SELECT subquery.
Calculate the average total goals for the specific stage in the main query.
Filter both subqueries and the main query so that only data from the 2012/2013 season is included.
GROUP the query by the m.stage column.

Hands-on interactive exercise

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

SELECT 
	-- Select the stage and average goals for each stage
	m.stage,
    ROUND(___(___ + m.away_goal),2) AS avg_goals,
    -- Select the average overall goals for the 2012/2013 season
    ROUND((SELECT ___(___ + away_goal) 
           FROM match 
           WHERE season = ___),2) AS overall
FROM match AS m
-- Filter for the 2012/2013 season
WHERE ___ = ___
-- Group by stage
GROUP BY ___;

Edit and Run Code