[SQL][sqlzoo] SELECT within SELECT

SELECT within SELECT                                                                                               

SELECT within SELECT Tutorial - SQLZOO

1.Bigger than Russia

SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')

2.Richer than UK

select name 
from world 
where continent='europe' and GDP/population >
(select gdp/population from world where name= 'United Kingdom')

3.Neighbours of Argentina and Australia

select name,continent 
from world 
where continent in
(select continent from world where name in('Argentina','Australia')) 
order by name

4.Between Canada and Poland

select name,population 
from world 
where population<(select population from world where name='Poland') 
      and population >(select population from world where name='Canada')

5.Percentages of Germany

select name,concat(round(population / (select population from world where name='Germany')*100 ,0),'%') as percentage
from world 
where continent='Europe'

6.Bigger than every country in Europe

SELECT name 
FROM world 
WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp>0 and continent='europe')

7.Largest in each continent

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

8.First country of each continent (alphabetically)

SELECT continent,name 
FROM world a 
WHERE name <= ALL
(SELECT name from world b WHERE a.continent = b.continent )
ORDER by continent

9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections

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

10.

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

Nested SELECT Quiz                                                                                          

1.

 SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)

2.

 SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)

3.

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

4.

Table-D

France

Germany

Russia

Turkey

5.

SELECT name FROM bbc
 WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')

6.

SELECT name FROM bbc
 WHERE population < (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

7.

Table-B

Bangladesh

India

Pakistan