Reputation: 6862

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?

Upvotes: 15

Answers (20)

Mynk Prk

Reputation: 11

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

Upvotes: 1

Brian DeMilia

Reputation: 13248

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)

Upvotes: 4

bhanuprathap

Reputation: 71

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.

Upvotes: 6

GeshmanXD

Reputation: 431

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

Upvotes: 20

user3440295

Reputation: 115

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

Upvotes: 2

Abhishek Bansal

Reputation: 51

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.

Upvotes: 5

ping zhang

Reputation: 1

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

Upvotes: 0

sunakshi132

Reputation: 87

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

Upvotes: 2

Volkan

Reputation: 1

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

Upvotes: 0

Akshat Agrawal

Reputation: 1

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
    )

Upvotes: 0

user15504061

Reputation: 1

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

Upvotes: 0

David Soares Rodrigues

Reputation: 11

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

Upvotes: 0

Tamer Gad

Reputation: 3

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)

Upvotes: 0

M. John

Reputation: 99

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

Upvotes: 0

Kunal Virmani

Reputation: 1

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

Upvotes: 0

wildplasser

Reputation: 44250

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

Upvotes: 15

Kalyani

Reputation: 11

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

Upvotes: 0

SHRADHA CHHABRA

Reputation: 1

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

Upvotes: 0

Piyush

Reputation: 111

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.

Upvotes: 11

Gordon Linoff

Reputation: 1271131

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;

Upvotes: 6

SQL combined SELECT statement

Answers (20)

Related Questions