SQL combined SELECT statement

Question

This is actually from an interactive site SQLzoo.net which I was using to brush up my SQL knowledge.

The problem is

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 to produce the solution

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

I didn't want to copy and edit the output as it is extremely tedious - this can be checked by going into Number 7. on this page - http://www.sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

I am trying to understand what I am writing wrong. I did the other questions okay, but this one seems to be a bit tricky (or possibly, more nested SELECT queries than I thought).

Any suggestion/explanation is appreciated.

Answer 1

The following code worked for me:

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

Answer 2

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 
   );

Answer 3

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

The 'ALL' part compares the population of all the countries in a continent with 25000000 and if it less than 25000000, it prints names, population of all countries in it.

Answer 4

I've written SQL for a long, long time and almost never use ALL, SOME or ANY.

To me, the obvious way to write this query is to use window functions:

SELECT name, continent, population 
FROM (SELECT w.*, MAX(population) OVER (PARTITION BY continent) as maxpop
      FROM world w
     ) w
WHERE maxpop < 250000000;

If you don't like that solution, use an explicit join and aggregation:

SELECT name, continent, population 
FROM world w JOIN
     (SELECT continent
      FROM world
      GROUP BY continent
      HAVING max(pop) < 250000000
     ) c
     ON w.continent = c.continent;

Answer 5

I wrote this and it worked. It was getting complicated when I use the given condition as it is so I used a negation. If a continent has got at least one country with more population that 25M, you would want skip it from the select. Your second select doesn't follow this logic. Hence the error.

Here it goes :

select name,continent,population from world
where not continent in
(select distinct continent from world where population >25000000)

Above code gets the continents which have atleast one country with more than 25M population and first select gets all the countries which are not from that continent. If you notice we don't have to check for population again as all the countries would obviously be less populous than or equal to 25M.

Answer 6

This is one way:

Essentially, the continent must be in a list of continents whose count of countries is the same as the count of countries that have a population less than or equal to that amount.

The list is determined by a subquery.

The count of countries that have a population less than that amount is determined by conditional aggregation.

select name, continent, population
  from world
 where continent in
       (select continent
          from world
         group by continent
        having count(*)
             = sum(case when population <= 25000000 then 1 else 0 end))

On a side note, minus will work in Oracle or SQL Server:

select name, continent, population
  from world
 where continent in (select continent
                       from world
                     minus
                     select continent
                       from world
                      where population > 25000000)

Answer 7

I think this would be simple one...

select name,continent, population from world where continent not in ( select continent from world where population >= 25000000)

Simply filter out the continents with population greater that 25M, you get the rest..

Answer 8

This is an easy way and work indeed

select name,continent ,population from world 
    where continent in( select continent from world group by
         continent having MAX(population)<=25000000 )

Answer 9

SELECT name, continent, population FROM world WHERE continent NOT IN (SELECT continent FROM world WHERE population > 25000000)

Answer 10

Below query worked for me

Select name, continent ,population 
from world 
where continent not in 
(
    Select continent  
    from world 
    where population >= 25000000
)