> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/sqlzoo/world-bbc-tables/select-within-select-tutorial.md).

# SELECT within SELECT Tutorial

[Link](https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial)

| name        | continent | area    | population | gdp          |
| ----------- | --------- | ------- | ---------- | ------------ |
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
| ...         |           |         |            |              |

\
1\. List each country name where the population is larger than that of 'Russia'.s

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

2. Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

```sql
SELECT name
FROM world
WHERE continent = 'Europe'
AND GDP/population > (SELECT GDP/population 
                FROM world 
                WHERE name = 'United Kingdom')
```

3. List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

```sql
SELECT name, continent
FROM world
WHERE continent IN ( SELECT continent 
                FROM world 
                WHERE name IN ('Argentina', 'Australia'))
ORDER BY name
```

4. Which country has a population that is more than United Kingdom but less than Germany? Show the name and the population.

```sql
SELECT name, population
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'United Kingdom')
AND population < (SELECT population FROM world WHERE name = 'Germany')
```

5. Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany. \
   Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.The format should be *Name, Percentage* for example:

| name    | C   |
| ------- | --- |
| Albania | 3%  |
| Andorra | 0%  |
| Austria | 11% |
| ...     | ... |

```sql
SELECT 
    name, 
    CONCAT(ROUND(100*population/(SELECT population 
                FROM world 
                WHERE name = 'Germany'),0),'%') AS percentage
FROM world
WHERE continent = 'Europe'
```

## `SQLZOO system error: error`

```sql
SELECT 
    name, 
    CONCAT(ROUND(CAST(population AS NUMERIC)/(SELECT CAST(population AS NUMERIC)
            FROM world 
            WHERE name = 'Germany')*100,2),'%') AS percentage
FROM world
WHERE continent = 'Europe';
```

note: above code is in Postgres

6. Which countries have a GDP greater than every country in Europe? \[Give the name only.] (Some countries may have NULL gdp values)

```sql
SELECT name
FROM world
WHERE gdp > ALL (SELECT gdp
                FROM world
                WHERE continent = 'Europe')
AND gdp IS NOT NULL

```

7. Find the largest country (by area) in each continent, show the continent, the name and the area:

```sql
SELECT continent, name, area
FROM world
WHERE area IN (SELECT MAX(area) 
                FROM world 
                WHERE area > 0 
                GROUP BY continent)
```

8. List each continent and the name of the country that comes first alphabetically.

```sql
SELECT continent, MIN(name) AS first_country
FROM world
GROUP BY continent
```

9. 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.

<pre class="language-sql"><code class="lang-sql"><strong>SELECT a.name, a.continent, max_pop 
</strong>FROM world a 
JOIN (SELECT continent, MAX(population) as max_pop 
FROM world GROUP BY continent) b 
ON (a.continent = b.continent AND a.population = b.max_pop)
WHERE max_pop &#x3C;= 25000000
</code></pre>

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

```sql
SELECT name, continent, population 
FROM world w 
JOIN 
(SELECT t1.continent, t1.max_pop, t2.nd_max_pop 
   FROM (
   SELECT continent, MAX(population) as max_pop 
   FROM world 
   WHERE population <> 0 
   GROUP BY continent) t1 
    JOIN 
     (SELECT continent, MAX(population) as nd_max_pop 
      FROM world WHERE name NOT IN (SELECT a.name 
           FROM world a
           JOIN (SELECT continent, MAX(population) as max_pop 
                FROM world 
                WHERE population <> 0 
                GROUP BY continent) b 
           ON a.continent = b.continent AND a.population = b.max_pop) 
           AND population <> 0 GROUP BY continent) t2 
       ON t1.continent = t2.continent WHERE t1.max_pop/3 > t2.nd_max_pop) t3 
ON w.continent = t3.continent AND w.population = t3.max_pop
```