SELECT within SELECT Tutorial

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:

namepercentage
Albania 3%
Andorra 0%
Austria 11%
... ...
Decimal places
You can use the function ROUND to remove the decimal places.
Percent symbol %
You can use the function CONCAT to add the percentage symbol.
select name,concat(cast(round(100*population/(select population from world where name='Germany'),0) as int),'%')
from world
where continent='Europe'

note:

CONCAT() 函数用于将多个字符串连接成一个字符串，语法简单就是：CONCST(str1,str2,…)
两种返回结果：

  • 然后就返回连接后的字符串
  • 其中有一个参数为NULL ，则返回值为 NULL

 

这里有两个注意的点

① The solution shown only works with MySQL - you can switch engines at the top of the page.

For SQL Server use ROUND(x,0) in place of ROUND(x)

round需要在后面加一个0，否则会报错

② 需要用cast将round的结果转换为int，否则会多一串0

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

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

 

note : 使用all结果会包含null的情况，所以要加上gdp>0来限定

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

SELECT continent,name, area
 FROM world x
where area>=(select max(area) from world y where y.continent=x.continent)

 

 note :使用聚合函数，自动去除了空值的行，所以可以不加area>0来限定