SQLZOO练习（六） SUM and COUNT

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