Nested simple subqueries
Nested subqueries can be either simple or correlated.
Just like an unnested subquery, a nested subquery's components can be executed independently of the outer query, while a correlated subquery requires both the outer and inner subquery to run and produce results.
In this exercise, you will practice creating a nested subquery to examine the highest total number of goals in each season, overall, and during July across all seasons.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Complete the main query to select the season and the max total goals in a match for each season. Name this
max_goals. - Complete the first simple subquery to select the max total goals in a match across all seasons. Name this
overall_max_goals. - Complete the nested subquery to select the maximum total goals in a match played in July across all seasons.
- Select the maximum total goals in the outer subquery. Name this entire subquery
july_max_goals.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Select the season and max goals scored in a match
___,
___(___ + away_goal) AS ___,
-- Select the overall max goals scored in a match
(SELECT ___(___ + away_goal) FROM match) AS ___,
-- Select the max number of goals scored in any match in July
(SELECT ___(___ + away_goal)
FROM match
WHERE id IN (
SELECT ___ FROM match WHERE EXTRACT(MONTH FROM ___) = 07)) AS ___
FROM match
GROUP BY season;