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?

Deze oefening maakt deel uit van de cursus

Data Manipulation in SQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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

Code bewerken en uitvoeren