MySQL練習題--sqlzoo刷題

標籤：ace   操作符   習題   否則   刷題   法國   cap   查看   顯示名稱   

首先查看world表的欄位：

name	continent	area	population	gdp	capital	tld	flag

SELECT * FROM world；

2、顯示人口至少為2億的省/地區的名稱。2億=200million

SELECT name FROM worldWHERE population >= 200000000;

3、給出人口至少2億的國家的名稱和人均國內生產總值。

select name,(gdp/population) as per_capita_gdp from world where population>=200000000;

4、顯示continent =‘South America‘的國家的名稱和人口。 將人口除以100萬，以獲得數百萬人口,也就是population的單位為百萬。

select name,(population/1000000) as population from world where continent=‘South America‘ ;

5、顯示法國，德國，意大利的名稱和人口（France、Germany、Italy）

 

select name,population from world where name in (‘France‘,‘Germany‘,‘Italy‘)；#注意#France、Germany等是以字串形式出現的，加引號，否則會出錯

6、顯示名稱中包含“United”字樣的省/地區

select name from world where name like ‘%united%‘;

7、如果一個國家面積超過300萬平方公裡，或者人口超過2.5億，那麼這個國家就很大。

按人口顯示面積大或面積大的國家。 顯示名稱，人口和面積。

select name,population,area from world where population>250000000 or area>3000000;

8、

Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area.

• Australia has a big area but a small population, it should be included.
• Indonesia has a big population but a small area, it should be included.
• China has a big population and big area, it should be excluded.
• United Kingdom has a small population and a small area, it should be excluded.

select name,population,area from world where (population<=250000000 and area>3000000) or (population>250000000 and area<3000000);

9、Show the name and population in millions and the GDP in billions for the countries of the continent ‘South America‘. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.Millions and billionsDivide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.

select name,round(population/1000000,2) as population,round(gdp/1000000000,2) as gdp from world where continent=‘South America‘;

10、顯示GDP至少為1萬億（100億，即12個零）的國家的名稱和人均GDP。 將此值舍入到最接近的1000。

將萬億美元國家的人均GDP顯示為最接近的1000美元。

select name,round(gdp/population,-3) as per_capita_gdp from world where gdp>1000000000000 ;

 

 

總結：

1、round函數的使用

連結：http://www.w3school.com.cn/sql/sql_func_round.asp

2、like操作符

 3、XOR操作符（第8題）

 

MySQL練習題--sqlzoo刷題