手机刷mysql题_MySQL练习题--sqlzoo刷题

首先查看world表的字段：

namecontinentareapopulationgdpcapitaltldflag

SELECT * FROM world；

2、显示人口至少为2亿的国家/地区的名称。2亿=200million

SELECT name FROMworldWHERE 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 billions

Divide 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题)