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.

Latihan ini adalah bagian dari kursus

Data Manipulation in SQL

Lihat Kursus

Petunjuk latihan

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.

Latihan interaktif praktis

Cobalah latihan ini dengan menyelesaikan kode contoh berikut.

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

Edit dan Jalankan Kode