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.
In this lesson, you will build a final query across 3 exercises that will contain three subqueries -- one in the SELECT
clause, one in the FROM
clause, and one in the WHERE
clause. In the final exercise, your query will extract data examining the average goals scored in each stage of a match. Does the average number of goals scored change as the stakes get higher from one stage to the next?
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Extract the average number of home and away team goals in two
SELECT
subqueries. - Calculate the average home and away 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 ___;