[SQL][sqlzoo] SELECT within SELECT

0.1. SELECT within SELECT                                                                                               

SELECT within SELECT Tutorial - SQLZOO

1. 1.Bigger than Russia

      

      

      

    
    

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

2. 2.Richer than UK

      

      

      

    
    

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

3. 3.Neighbours of Argentina and Australia

      

      

      

    
    

      <sql />
    
select name,continent 
from world 
where continent in
(select continent from world where name in('Argentina','Australia')) 
order by name

4. 4.Between Canada and Poland

      

      

      

    
    

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

5. 5.Percentages of Germany

      

      

      

    
    

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

6. 6.Bigger than every country in Europe

      

      

      

    
    

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

7. 7.Largest in each continent

      

      

      

    
    

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

8. 8.First country of each continent (alphabetically)

      

      

      

    
    

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

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

      

      

      

    
    

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

10. 10.

      

      

      

    
    

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

10.1. Nested SELECT Quiz                                                                                          

1.

      

      

      

    
    

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

2.

      

      

      

    
    

      <sql />
    
 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.

      

      

      

    
    

      <sql />
    
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.

      

      

      

    
    

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

6.

      

      

      

    
    

      <sql />
    
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

10.2.