SQL combined SELECT statement

Question

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?

Pedaiah

Answer 1

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

Sinuosity

Answer 2

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

Pedestrian

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.

Hautesavoie

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;

Pew

Answer 5

It was getting complicated when I used the given condition 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.

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

That gets the continents which have at least one country with more than 25M population and the first select gets all the countries which are not from that continent. We don't have to check for population again as all the countries would be less populous than or equal to 25M.

Telson

Answer 6

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

Filter out the continents with population greater that 25M.

Untenable

Answer 7

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

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)

Tad

On second thought you actually don't need distinct as there is one row per country. There are multiple ways to solve this, you could just as easily -- or more easily in fact -- use max as suggested in the other answer. – Tad

I think minus is working in oracle not in SQL Server. – Lob

Answer 8

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

Calx

Answer 9

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

Modla

Thanks for contributing your first answer, however, please consider explaining in what it differs from the existing answers, if it does, and try to explain what the code does as well as a general rule so it is clear how it solves the question each time. – Downer

Answer 10

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

Np

Answer 11

SELECT name, continent, population FROM world
WHERE continent IN (
SELECT distinct  continent FROM world
GROUP BY continent HAVING MAX(population)<=25000000
)

Discernment

Answer 12

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

Salvia

Answer 13

SELECT name, continent, population FROM world WHERE continent = (SELECT continent
FROM world x
WHERE population <= 25000000
GROUP BY continent
HAVING COUNT(name) = (SELECT COUNT(name) FROM world y WHERE x.continent = y.continent GROUP BY continent))

Maharaja

Code only answers are discouraged. Please add some explanation as to how this solves the problem, or how this differs from the existing answers. From Review – Ancipital

Answer 14

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

Interval

Answer 15

select name, continent, population
FROM 
 (
 select continent, name, population, 
 SUM(indicator) OVER (PARTITION BY continent) total
 FROM
  (select 
  continent, name, population,
  case when population > 25000000 THEN 1 ELSE 0 END indicator
  from world
  )sub1 
 )sub2
WHERE total = 0
ORDER BY name

Dimer

Answer 16

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

Rhyme

Answer 17

WITH  MY_TABLE AS (
    SELECT CONTINENT, NAME, POPULATION,
        ROW_NUMBER() OVER (PARTITION BY CONTINENT ORDER BY POPULATION DESC) AS RANK
    FROM WORLD)
SELECT NAME, CONTINENT, POPULATION
FROM WORLD 
WHERE CONTINENT IN (
    SELECT CONTINENT
    FROM MY_TABLE
    WHERE RANK = 1 AND POPULATION <= 25000000)

Uriisa

Answer 18

select name, continent, population
from world
where continent in
    (
    select continent
    from world x
    where 25000000 > ALL
        (select population from world y
        where x.continent = y.continent
        and population >0)
    )

Agripinaagrippa

Answer 19

SELECT name
      ,continent
      ,population
FROM world
WHERE continent IN
    (
    SELECT continent AS f_country
    FROM world
    WHERE population <= 25000000
    GROUP BY continent
    HAVING COUNT(name) IN
        (
        SELECT COUNT(name) AS t_country 
        FROM world 
        GROUP BY continent
        )
    );

Pelagic

Answer 20

SELECT name, continent, population FROM world
WHERE continent IN (SELECT continent FROM
  (SELECT continent, MAX(population) as MP FROM world 
  GROUP BY continent 
  HAVING MAX(population) <=25000000) AS A)

In each continent's max population country is found, then conditioned for being smaller 25000000

Tetryl