name | continent | area | population | gdp |
Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
Albania | Europe | 28748 | 2831741 | 12960000000 |
Algeria | Africa | 2381741 | 37100000 | 188681000000 |
Andorra | Europe | 468 | 78115 | 3712000000 |
Angola | Africa | 1246700 | 20609294 | 100990000000 |
…… |
name:国家名称 continent:大洲 area:面积 population:人口 gdp:国内生产总值
SELECT population FROM world WHERE name=‘Germany‘;
SELECT name,population FROM world WHERE name IN (‘Sweden‘,‘Norway‘,‘Denmark‘);
SELECT name,area FROM world WHERE area BETWEEN 200000 AND 250000;
Observe the result of running this SQL command to show the name, continent and population of all countries.
SELECT name,continent,population FROM world;
Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
SELECT name FROM world WHERE population >= 200000000;
Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
SELECT name,GDP/population FROM world WHERE population >=200000000;
continent
=‘South America‘的国家的名称和人口。将人口除以100万,以获得数百万人口,也就是population的单位为百万。
Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
SELECT name,population/1000000 FROM world WHERE continent=‘South America‘;
Show the name
and population
for France, Germany, Italy
SELECT name,population FROM world WHERE name IN (‘France‘,‘Germany‘,‘Italy‘);
Show the countries which have a name
that includes the word ‘United‘
SELECT name FROM world WHERE name LIKE ‘%United%‘;
如果一个国家面积超过300万平方公里,或者人口超过2.5亿,那么这个国家就很大。按人口显示面积大或面积大的国家。 显示国家名称,人口和面积。
SELECT name,population,area FROM world WHERE area >=3000000 OR population >=250000000;
XOR 国家面积超过300万平方公里,或者人口超过2.5亿的国家,但是不能同时满足。显示国家名称,人口,面积。
Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.
SELECT name,population,area FROM world WHERE area >=3000000 XOR population >=250000000;
除以1000000(6个0)是以百万计,除以1000000000(9个0)是以十亿计。
显示南美洲的国家名称,人口,GDP(人口以百万为单位,GDP以十亿为单位);并且用ROUND函数保留两位小数。
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.
SELECT name,ROUND(population/1000000,2),ROUND(GDP/1000000000,2) FROM world WHERE continent=‘South America‘;
Show the name
and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.
Show per-capita GDP for the trillion dollar countries to the nearest $1000.
SELECT name,ROUND(GDP/population,-3) FROM world WHERE GDP >=1000000000000;
显示字符长度是一样的国家名称和首都名称,你可以使用LENGTH函数来判定字符串长度。
Greece has capital Athens.
Each of the strings ‘Greece‘, and ‘Athens‘ has 6 characters.
Show the name and capital where the name and the capital have the same number of characters.
You can use the LENGTH function to find the number of characters in a string
SELECT name,capital FROM world WHERE LENGTH(name)=LENGTH(capital);
显示开头字母相同的国家名称、首都名称,但是国家名称和首都名称不能相同。
你可以使用LEFT函数来锁定首字母,可以用不等于号(<>)来进行不等于判定。
The capital of Sweden is Stockholm. Both words start with the letter ‘S‘.
<>
as the NOT EQUALS operator.SELECT name,capital FROM world WHERE LEFT(name)=LEFT(capital) AND name<>capital;
显示包含所有元音字母(aeiou),而且不能有空格的国家名称。
赤道几内亚和多米尼加共和国名称中都包含了元音字母(aeiou),但是因为超过一个单词不能被计算在内。
Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don‘t count because they have more than one word in the name.
Find the country that has all the vowels and no spaces in its name.
name NOT LIKE ‘%a%‘
to exclude characters from your results.SELECT name FROM world WHERE name LIKE ‘%a%‘ AND name LIKE ‘%e%‘ AND name LIKE ‘%i%‘ AND name LIKE ‘%o%‘ AND name LIKE ‘%u%‘ AND NOT LIKE ‘% %‘;
SQLZOO练习(一)SELECT BASICS,SELECT form world
原文:https://www.cnblogs.com/ruoli-121288/p/13182611.html