一些SQLZOO练习过程中的问题。
9. 除以为1000000(6个零)是以百万计。除以1000000000(9个零)是以十亿计。使用 ROUND 函数来显示的数值到小数点后两位。
对于南美显示以百万计人口,以十亿计GDP(保留小数点2位)。
ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,要保留的位数) FROM table_name
select name,round(population/1000000,2),round(gdp/1000000000,2)
from world
where continent='South America'
10. 显示国家有至少一个万亿元国内生产总值(万亿,也就是12个零)的人均国内生产总值。四舍五入这个值到最接近1000。
显示万亿元国家的人均国内生产总值,四舍五入到最近的$1000。
使用round函数。因为留一位小数1,不留小数是0,-1就好比153不留3,直接四舍五入150,同样-2就是153,不留53,直接四舍五入200。
select name,round(gdp/population,-3)
from world
where gdp>1000000000000
11. Show the name - but substitute Australasia for Oceania - for countries beginning with N.
显示名称-但用澳大拉西亚代替大洋洲-以N开头的国家。
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
select name,
case
when continent='Oceania' then 'Australasia'
else continent END
from world
where name like 'N%'
12. Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.
显示名称和大陆-但是用欧亚大陆代替欧洲和亚洲;用美洲代替北美、南美或加勒比的每个国家。显示以A或B开头的国家。
select name,
case
when continent in ('Europe','Asia') then 'Eurasia'
when continent in ('North America','South America','Caribbean') then 'America'
else continent END
from world
where name like 'A%' or name like 'B%'
13. Put the continents right…
- Oceania becomes Australasia(大洋洲变成澳大利亚)
- Countries in Eurasia and Turkey go to Europe/Asia(欧亚大陆和土耳其的国家去欧洲/亚洲)
- Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America(以“B”开头的加勒比海群岛前往北美,其他加勒比海群岛前往南美)
- Show the name, the original continent and the new continent of all countries.( 显示所有国家的名称、原始大陆和新大陆。)
select name,continent,
case
when continent='Oceania' then 'Australasia'
when continent in ('Eurasia','Turkey') then 'Europe/Asia'
when continent='Caribbean' and name like 'B%' then 'North America'
when continent='Caribbean' and name not like 'B%' then 'South America'
else continent end
from world