顯示具有至少2億人口的國家名稱。 2億是200000000,有八個零。
SELECT name FROM world WHERE population>=200000000
找出有至少200百萬(2億)人口的國家名稱,及人均國內生產總值。
select name ,GDP/population from world where population >=200000000
顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
select name,population/1000000 from world where continent like 'South America'
顯示法國,德國,意大利(France, Germany, Italy)的國家名稱和人口。
select name,population from world where name in('France','Germany','Italy')
顯示包含單詞“United”為名稱的國家。
select name from world where name like '%United%'
成為大國的兩種方式:如果它有3百萬平方公里以上的面積,或擁有250百萬(2.5億)以上人口。
select name,population,area from world where area>3000000 or population>250000000
美國、印度和中國(USA, India, China)是人口又大,同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家,但不能同時兩者。顯示國家名稱,人口和面積。
select name,population,area from world where (area>3000000 and population<250000000) or (area<3000000 and population>250000000)
除以为1000000(6个零)是以百万计。除以十亿(9个零)是以十亿计。使用ROUND函数来显示的数值到小数点后两位。
对于南美显示以百万计人口,以十亿计2位小数GDP。select name,round(population/1000000,2),round(gdp/1000000000,2) from world where continent='South America'
顯示國家有至少一個萬億元國內生產總值(萬億,也就是12個零)的人均國內生產總值。四捨五入這個值到最接近1000。
select name,round(gdp/population,-3) from world where gdp>=1000000000000
Show the name - but substitute Australasia for Oceania - for countries beginning with N.
SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia' ELSE continent END FROM world WHERE name LIKE 'N%'
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
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%'
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
select name,continent, case when continent in ('Eurasia', 'Turkey') then 'Europe/Asia' when continent = 'Oceania' then 'Australasia' when continent = 'Caribbean' then case when name LIKE 'B%' then 'North America' else 'South America' end else continent end from world order by name asc