Organizing with CTEs
Previously, you modified a query based on a statement you completed in chapter 2 using common table expressions.
This time, let's expand on the exercise by looking at details about matches with very high scores using CTEs. Just like a subquery in FROM, you can join tables inside a CTE.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Declare your CTE, where you create a list of all matches with the league name.
- Select the league, date, home, and away goals from the CTE.
- Filter the main query for matches with 10 or more goals.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Set up your CTE
___ match_list ___ (
-- Select the league, date, home, and away goals
SELECT
___ AS league,
___,
___,
m.away_goal,
(m.home_goal + m.away_goal) AS total_goals
FROM match AS m
LEFT JOIN league as l ON m.country_id = l.id)
-- Select the league, date, home, and away goals from the CTE
SELECT ___, date, ___, away_goal
FROM ___
-- Filter by total goals
WHERE total_goals ___;