## SELECT within SELECT

This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.

## Exercises

 1a. List each country name where the population is larger than 'Russia'. `bbc(name, region, area, population, gdp)` SELECT name FROM bbc WHERE population> (SELECT population FROM bbc WHERE name='Russia') Results
 1b. List the `name` and `region` of countries in the regions containing 'India', 'Iran'. Results
 1c. Show the countries in Europe with a per capita GDP greater than 'United Kingdom'. Per Capita GDP Results
 1d. Which country has a population that is more than Canada but less than Algeria? Results

To gain an absurdly detailed view of one insignificant feature of the language, read on.

We can use the word `ALL` to allow >= or > or < or <=to act over a list.

 2a. Which countries have a GDP greater than any country in Europe? [Give the name only.] Results

We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.

 3a. Find the largest country in each region, show the region, the name and the population: SELECT region, name, population FROM bbc x WHERE population >= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0) Results

The following questions are very difficult:

 3b. Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population. Results
 3c. Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions. Results