SQLZOO练习(六) SUM and COUNT
SUM and COUNT
1.显示世界总人口。
SELECT SUM(population)
FROM world;
2.列出所有大陆,每个大陆只列出一次。
SELECT DISTINCT continent
FROM world;
SELECT continent
FROM world
GROUP BY continent;
3.给出非洲的国内生产总值。
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa';
4.有多少国家面积至少为100000。
SELECT COUNT(name)
FROM world
WHERE area >= 1000000;
5.国家(“爱沙尼亚”、“拉脱维亚”、“立陶宛”)的人口总数。
SELECT SUM(population)
FROM world
WHERE name IN('Estonia', 'Latvia', 'Lithuania');
6.每个大陆都显示大陆和国家数量。
SELECT continent,COUNT(name)
FROM world
GROUP BY continent;
7.每个大陆都显示该大陆和人口至少为1000万的国家数目。
SELECT continent,COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent;
8.列出人口总数至少为1亿的各大洲。
SELECT DISTINCT continent
FROM world x
WHERE 100000000 <= (SELECT SUM(population)
FROM world y
WHERE y.continent=x.continent);
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) > 100000000;
Quiz:CADEBEDD