SQLZoo SELECT within SELECT Tutorial

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

此教程教我們在SELECT查詢中使用別一個SELECT查詢，進行一些更複雜的查詢。

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

列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。

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

列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。

select name from world
where gdp/population > (select gdp/population from world where name = 'united kingdom')
and continent = 'europe'

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

在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序。

select name, continent from world
where continent in (select continent from world 
where name = 'argentina' or name = '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.

哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。

select name , population from world 
where population > (select population from world where name = 'canada') 
and population < (select population from world where name = 'poland')

5、Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

select name, concat(round(100*population/(select population from world 
where name = 'germany')), '%')
from world 
where continent = 'europe'

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

哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select name from world 
where gdp > all(select gdp from world where gdp > 0 and continent = 'europe')

7、Find the largest country (by area) in each continent, show the continent, the name and the area: The above example is known as a correlated or synchronized sub-query.

在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

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

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

列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

select continent, name from world as x
where x.name = (select name from world as y
    where x.continent = y.continent 
    order by name limit 1)

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.

找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

select name, continent, population from world as x
where 25000000 >= all(select population from world as y 
    where population > 0 and x.continent = y.continent)

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

有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

select name, continent from world as x 
where x.population >= all (select 3*population from world as y
    where  y.continent = x.continent and x.name != y.name)