For the record, this is not a homework question. I am a professional brushing up on my SQL skills.
Given a table called
world that looks like this,
I want to find the continents where all countries have a population <= 25000000. Specifically, I want the name, continent, and population of the countries associated with these continents.
I wrote this query, and it appears to be functionally correct:
SELECT name, continent, population FROM world x WHERE ( SELECT COUNT(name) FROM world y WHERE y.population <= 25000000 AND x.continent = y.continent ) = ( SELECT COUNT(name) FROM world z WHERE x.continent = z.continent ) ORDER BY continent, population DESC
At first glance, that query looks unnecessarily redundant. I'd like to simplify the query (or improve it in other ways), but I'm unsure how to do that. Can someone show some better alternatives?
MySQL or Postgres are preferable feature sets, if it matters.
The problem is exactly as it appears in exercise 9 of this page in sqlzoo. Queries can be tested and iterated upon there as well.