sql sub-queries

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" ?

---

SELECT name, region 
FROM bbc x 
WHERE population/3 >= ALL
    (SELECT population
     FROM bbc y
     WHERE y.region=x.region
     AND x.name != y.name)

---

Elad, your first answer is almost correct, just missing one very key component:

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

You see when you do the sub-query that checks x.population>3*(all of y.populations for same continent) YOU MUST SPECIFY NOT TO CHECK AGAINST THE SAME COUNTRY; otherwise you are stating to check that x>3x which is mathematically impossible.

---

For the first :

You have to divide the work. Step one, find the neighbours for a country. This have to be an auto join :

SELECT *
FROM bbc country
    INNER JOIN bbc neighbours
        ON country.region = neighbours.region
        AND country.name != neighbours.name

Don't forget to exclude self country from the neighbours !

Second, you can count how much neighbours for a country have the right population :

sum(CASE WHEN country.population > neighbours.population * 3 THEN 1 ELSE 0 END)
(Group by country !)

Compare with the total and you are done !

SELECT countryName 
FROM
(
    SELECT sum(CASE WHEN country.population > neighbours.population * 3 THEN 1 ELSE 0 END) as okNeighbours,
           count(*) as totalNeighbours
           country.name as countryName
    FROM bbc country
        INNER JOIN bbc neighbours
            ON country.region = neighbours.region
            AND country.name != neighbours.name
    GROUP BY country.name
)
WHERE totalNeighbours = okNeighbours

For the second :

SELECT name, region, population
FROM bbc
WHERE region IN (
    SELECT region
    FROM bbc
    GROUP BY region
    HAVING SUM(CASE WHEN population >= 25000000 THEN 1 ELSE 0 END) = 0
)

---

A couple of other solutions, added for interest.

First query:

SELECT name, 
       region 
FROM   bbc x 
WHERE  population > 
       -- this sub query finds each neighbour (not including itself) and returns the max populations multiplied by 3
       (SELECT 3 * MAX(population) 
        FROM   bbc y 
        WHERE  x.region = y.region 
               AND x.name <> y.name) 

Second query:

SELECT name, 
       region, 
       population 
FROM   bbc x 
WHERE  population < ALL 
       -- the ALL keyword allows comparison to be made against all the values in a list 
       -- this sub query finds each country that belongs to a region with populations less than 25 million and returns this as a list
       (SELECT population 
        FROM   bbc y 
        WHERE  y.region = x.region 
               AND population > 25000000) 

---

SELECT name, region
FROM   bbc x
WHERE  population > 3 *
       (SELECT population 
        FROM bbc y 
        WHERE x.region=y.region
        ORDER BY population DESC limit 1,1)

population you are looking after is 3x the value of second highest population, thats limit 1,1. Second question is missing a 0, then its correct.

SELECT name, region, population 
FROM   bbc x
WHERE  (SELECT SUM(population) 
        FROM bbc y
        WHERE x.region=y.region) < 250000000

---

For the second query, "Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population." (ref. sqlzoo.net, 'SELECT within SELECT', question 3b)

SELECT name, region, population FROM bbc
 WHERE region NOT IN
     ( SELECT DISTINCT region FROM bbc
        WHERE population >= 25000000 )
   AND region IN
     ( SELECT DISTINCT region FROM bbc
        WHERE population < 25000000 )

---

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

---

This will also work

       SELECT name, continent 
       FROM world x 
       WHERE population >= 3* (SELECT population FROM world y
       WHERE y.continent=x.continent
       AND population>0 ORDER BY population DESC LIMIT 1 OFFSET 1 )

In the inner query I am selecting the second highest population in the respective continent and in the where I am checking whether second highest one is 3 times smaller or not in population.

---

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)

I've just encountered this problem right now, so sorry if I'm years late. haha! This is how I did it. The purpose of the main select statement is to compare every country then we twist it a little bit in the inner select statement which compares country in the same continent/region only. Also we added a condition where the country from outer select shouldn't be compared with itself.

---

Use this query:

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

---

To find the name of all countries in a continent, where all countries have a population less than 25000000, do the following:

1. Find the max(population) grouped by continent

   (SELECT max(population) 
   FROM world
   GROUP BY continent)
   

Note: since the maximum is less than your number, you know all are

2. Find the continent

   SELECT continent
   FROM world
   WHERE population IN(SELECT max(population) 
   FROM world
   GROUP BY continent)
   AND population <= 25000000)
   

3. Put it all together to get the name, continent, population

   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)
   

---

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)

---

On your second query you can do this without IN or NOT IN:

SELECT name, region, population
from bbc
where population >= 25000000

If you insist on using IN however just reverse the condition:

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

---

If I understand your data correctly, you have only one table. So when you

Select distinct region from bbc where population >=25000000)

you are really getting a list of all the countries that have 25mil and listing their region names. To get a list of regions, you have to SUM the populations

Select region, sum(population) as regionpop from bbc group by region having sum(population)>25000000

Now you can select the countries from those regions and show their information

Select name, region, population from bbc where region in 
(Select region from bbc group by region having sum(population)>25000000)

---

For the first query, would something like this work?

SELECT
a.region  , b.*
FROM 
bbc  a
INNER JOIN 
(SELECT population , (population  * 3) AS PopX3, region  FROM bbc  ) b
ON
a.population  < PopX3
AND
a.region <> b.region

---

For your "3 times population" query, no sub query is required... just a self-join on region, and the first instance population is > second instance population * 3. In the case of a self-join getting to its own country in the same region, it would never be returned since its population would never be greater than 3 times its own value.

Still waiting on feedback of other population question posted as comment in your original question...

select
      b1.Name,
      b1.Region,
      b1.Population,
      b2.Name SmallerCountry,
      b2.Population SmallerPopulation
   from
      bbc b1
         join bbc b2
             on b1.Region = b2.Region
            AND b1.Population > b2.Population * 3

---

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

So first need to find continent where any country should not have population > 25000000.

SELECT name, continent, population from world x
WHERE continent in

 (SELECT continent FROM world y

    WHERE 25000000 > 
      (select max(population) from world z
           where y.continent = z.continent))

---

For the first question, the SQL script is :

SELECT w1.name,w1.continent
FROM world w1
WHERE w1.population > 3 * (
SELECT max(w2.population)
FROM world w2
WHERE w2.continent = w1.continent
AND w2.name <> w1.name
GROUP BY w2.continent
)

---

I've been going through these little challenges on SQLZOO as well, and these two particular questions I found tricky.

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

My first attempt was:

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

Which seemed logical to me. Until I realised that if I was checking every population against every other population within the continent, including itself, then I'd never get any results back, as the population of a country will never be more than itself times three. So you need to check every other country, excluding the country you're checking against.

There are also two countries in the table that have a population of NULL, so you also have to exclude these to satisfy the question.

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)
AND population > 0

---

For the first question : you have to add another line of code AND B.name <> A.name

SELECT name, region  
FROM bbc A
WHERE A.population/3 >= ALL(SELECT population FROM bbc B 
                                              WHERE B.region = A.region 
                                              AND   B.name  <> A.name)

For the Second question : you can get rid of both NOT IN and IN by using ALL

SELECT name, region, population
FROM bbc A
WHERE 25000000 >= ALL(SELECT population FROM bbc B 
                                        WHERE B.region = A.region)

---

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)

---

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

---

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

---

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

Here's working query with GROUP BY

---

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

---

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)