Get startedGet started for free

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

View Course

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 ___;
Edit and Run Code