Basic Correlated Subqueries

Correlated subqueries are subqueries that reference one or more columns in the main query. Correlated subqueries depend on information in the main query to run, and thus, cannot be executed on their own.

Correlated subqueries are evaluated in SQL once per row of data retrieved -- a process that takes a lot more computing power and time than a simple subquery.

In this exercise, you will practice using correlated subqueries to examine matches with scores that are extreme outliers for each country -- above 3 times the average score!

This is a part of the course

“Data Manipulation in SQL”

View Course

Exercise instructions

Select the country_id, date, home_goal, and away_goal columns in the main query.
Complete the AVG value in the subquery.
Complete the subquery column references, so that country_id is matched in the main and subquery.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT 
	-- Select country ID, date, home, and away goals from match
	___.country_id,
    ___,
    ___.home_goal, 
    ___
FROM match AS main
WHERE 
	-- Filter the main query by the subquery
	(home_goal + away_goal) > 
        (SELECT AVG((___ + sub.away_goal) * 3)
         FROM match AS sub
         -- Join the main query to the subquery in WHERE
         WHERE main.___ = sub.___);

Edit and Run Code