text: -- Working with sub-queries
  
  -- 1. Uncorrelated sub-query
  SELECT UNStatisticalRegion,
         CountryName 
  FROM Nations
  WHERE Code2 -- Country code for outer query 
           IN (SELECT Country -- Country code for sub-query
               FROM Earthquakes
               WHERE Depth >= 400 ) -- Depth filter
  ORDER BY UNStatisticalRegion;
  
  -- 2. Correlated sub-query
  SELECT UNContinentRegion,
         CountryName, 
          (SELECT AVG(Magnitude) -- Add average magnitude
          FROM Earthquakes e 
           	  -- Add country code reference
          WHERE n.Code2 = e.Country) AS AverageMagnitude 
  FROM Nations n
  ORDER BY UNContinentRegion DESC, 
           AverageMagnitude DESC;
  
  -- 3. Sub-query Vs INNER JOIN
  SELECT
  	n.CountryName,
  	 (SELECT MAX(c.Pop2017) -- Add 2017 population column
  	 FROM Cities AS c 
                         -- Outer query country code column
  	 WHERE c.CountryCode = n.Code2) AS BiggestCity
  FROM Nations AS n; -- Outer query table
  
  -- Using an INNER JOIN can help with query performance
  SELECT n.CountryName, 
         c.BiggestCity 
  FROM Nations AS n
  INNER JOIN -- Join the Nations table and sub-query
      (SELECT CountryCode, 
       MAX(Pop2017) AS BiggestCity 
       FROM Cities
       GROUP BY CountryCode) AS c
  ON n.Code2 = c.CountryCode; -- Add the joining columns
  