sql sub-queries

user_311130 0
Viewed 26803

Can anyone help me with the following:

Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions.

my try:

select x.name, x.region
from bbc x
where x.population >all
(select population*3
from bbc y
where y.region = x.region)

syntax is correct but no records are returned (should return 3 rows)

Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population.

my try:

select name, region, population
from bbc
where region not in 
(select distinct region from bbc 
where population >= 25000000)

I used "not in". Is there a way to use "in" ?

subquery sql
26 Answers
select x.name, x.continent
from world x
where x.population > 3 * (select y.population from world as y where x.continent = y.continent and x.name <> y.name order by y.population desc limit 1)
user_110309700

this query will help you.

select name,continent from world a where population >all(select population*3 from world b where a.continent=b.continent and a.name!=b.name)

user_102030070

The correct answer shows only 3 country results in the entire world. Maybe I'm missing something. There should be many countries that has 3x+ population to their neighbor, assuming neighbor is defined only as a country in the same continent.

IE, Afghanistan has 25million population, while Bahrain in Asia has 1.2 mil, which makes Afghanistan way bigger than 3x population compared to its Bahrain neighbor. So that should be a correct answer selection too right?

user_45525830

this query will return the desired output

SELECT countryName, continent FROM ( SELECT sum(CASE WHEN w1.population > w2.population * 3 THEN 1 ELSE 0 END) as finaloutput, count(*) as totalNeighbours, w1.name as countryName, w1.continent as continent FROM world w1 INNER JOIN world w2 ON w1.continent= w2.continent AND w1.name != w2.name GROUP BY country.name ) as tbl WHERE totalNeighbours = finaloutput

user_60649950 
Select name , continent 
from world x 
where population >  All(
  Select population* 3 from world y 
  where x.continent = y.continent and x.name != y.name);
user_132022310

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.

solution

select name, continent, population
from world x where 25000000>= 
all(select population from world z where x.continent = z.continent);
user_114022660

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.

This is my solution and it seems to be the simplest one from all the above:

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