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.

Deze oefening maakt deel uit van de cursus

Data Manipulation in SQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- Set up your CTE
___ match_list ___ (
  -- Select the league name, 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 ___;

Code bewerken en uitvoeren