Sql subqueries

Question

Can someone help me with the following:

In some countries, a population of more than three times that of their neighbors (in the same region). Give countries and regions.

my attempt:

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

Syntax

true, but records are not returned (should return 3 rows)

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

my attempt:

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

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

Answer 1

For the first:

You must share the work. Step one, find neighbors for the country. This should be an automatic connection:

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

Do not forget to exclude your own country from your neighbors!

Secondly, you can calculate how many neighbors for the country have the right population:

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

Compare with the results, and you're 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 ) 

Answer 2

A few other solutions added for interest.

First request:

 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 request:

 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) 

Answer 3

 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) 

Answer 4

 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) 

The population that you care for is 3 times the value of the second largest population, this is a restriction of 1.1. The second question is missing 0, then its correct.

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

Answer 5

Elad, your first answer is almost right, just one very important component is missing:

 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 execute a subquery that checks x.population> 3 * (all y.populations for one continent) YOU SHOULD DO NOT CHECK AGAINST THIS COUNTRY ; otherwise you state that x> 3x, which is mathematically impossible.

Answer 6

 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) 

Answer 7

To find the name of all countries on the continent where the population of all countries is less than 25000000, do the following:

1. Find the maximum number of people grouped by continent

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

Note: since the maximum is less than your number, you know that everything

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

Answer 8

 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 just ran into this problem right now, so sorry if I'm late for years. haha! Here is how I did it. The goal of the main select statement is to compare each country, and then twist it a bit in an internal selection that only compares a country in the same continent / region. We also added a condition under which a country from an external choice should not be compared with itself.

Answer 9

In 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, but just change the condition:

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

Answer 10

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

 Select distinct region from bbc where population >=25000000) 

you really get a list of all countries that have 25 million and list their region names. To get a list of regions, you should use SUM for population groups

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

Now you can select countries from these regions and show your information

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

Answer 11

Will there be something like this work for the first request?

 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 

Answer 12

For your request “3 times the population”, no additional request is required ... just self-connection in the region, and the population of the first instance is the population of the second instance * 3. In case of self-training in your country in the same region, it will never be returned, since its population will never be more than 3 times its own value.

Still awaiting feedback from another population question posted as a 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 

Answer 13

In the second query: "Find every country that belongs to a region where all population groups are less than 25000000. Show name, region and population." ( sqlzoo.net link , 'SELECT to 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 ) 

Answer 14

Find every country that belongs to the continent, where all population groups are less than 25000000 people. Show name, continent and population

Therefore, you first need to find a continent where in any country there should not be a 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)) 

Answer 15

 SELECT name, region, population FROM world WHERE region NOT IN (SELECT region FROM world WHERE region > 25000000 GROUP BY region) 

Answer 16

It 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 internal query, I select the second highest population on the corresponding continent and in which I check whether the second highest one is 3 times smaller or not in the population.

Answer 17

For the first SQL script question:

 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 ) 

Answer 18

I also ran into these small issues in SQLZOO, and these two specific questions I found difficult.

In some countries, the population is more than three times larger than in any of their neighbors (on the same continent). Give countries and continents.

My first attempt:

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

It seemed logical to me. Until I realized that if I tested every population against all the other inhabitants of the continent, including myself , I would never have received any results, since the country's population will never be more than three times. Therefore, you need to check every other country, except for the country in which you are checking.

The table also has two countries with a NULL population, so you should also exclude them to answer 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 

Answer 19

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) 

Answer 20

For the first question: you need 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 NOT IN and IN using ALL

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

Answer 21

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

Answer 22

 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) 

Answer 23

Find continents where all countries have populations <= 25000000. Then find the names of countries associated with these continents. Show name, continent and population.

decision

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

Answer 24

Find continents where all countries have a population <= 25000000 . Then find the names of countries associated with these continents. Show name , continent and population .

This is my solution, and it seems the simplest of all the following:

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