Search code examples
sqlsubquery

SQL combined SELECT statement

Interactive exercise 9 Difficult Questions That Utilize Techniques Not Covered In Prior Sections at https://sqlzoo.net:

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

What I have done:

SELECT name, continent, population 
FROM world x 
WHERE population <= ALL(SELECT population 
                        FROM world y 
                        WHERE y.continent = x.continent 
                        AND population > 25000000)

What am I writing wrong?

Solution 
  • SELECT name, continent, population 
FROM world w
WHERE NOT EXISTS (                  -- there are no countries
   SELECT *
   FROM world nx
   WHERE nx.continent = w.continent -- on the same continent
   AND nx.population > 25000000     -- with more than 25M population 
   );