Correlated sub-query

Sub-queries are used to retrieve information from another table, or query, that is separate to the main query.

A friend is working on a project looking at earthquake hazards around the world. She requires a table that lists all countries, their continent and the average magnitude earthquake by country. This query will need to access data from the Nations and Earthquakes tables.

This exercise is part of the course

Improving Query Performance in SQL Server

View Course

Hands-on interactive exercise

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

SELECT UNContinentRegion,
       CountryName, 
        (SELECT ___(___) -- Add average magnitude
        FROM Earthquakes e 
         	  -- Add country code reference
        WHERE n.___ = e.Country) AS AverageMagnitude 
FROM Nations n
ORDER BY UNContinentRegion DESC, 
         AverageMagnitude DESC;

Edit and Run Code