sqlzoo--sum and count

• sqlzoo–select basics,select from world

• sqlzoo–select from nobel ,select in select

• sqlzoo–sum and count

• sqlzoo–the join operation

• sqlzoo–More JOIN operations

• sqlzoo–using null

• sqlzoo–self join

SUM and COUNT

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
…

1. 查询世界总人口

   SELECT sum(population)
   FROM world;
   

2. 列出所有洲份，每个只显示一次

   SELECT DISTINCT(continent) FROM world;
   

3. 查询出非洲的GDP总和

   SELECT SUM(gdp) FROM world
     WHERE continent = 'Africa';
   

4. 有多少个国家具有至少百万的面积

   SELECT COUNT(name) FROM world
   WHERE area >= 1000000;
   

5. 查询法国、德国、西班牙的总人口

   SELECT SUM(population) FROM world 
   WHERE name in ('France', 'Germany', 'Spain');
   

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. 列出有至少一亿人口的洲份

   SELECT continent FROM world
   GROUP BY continent
   HAVING SUM(population) >= 100000000;
   

   SUM and COUNT Quiz:

   1. 选择能够显示欧洲所有国家人口总和的语句

      answer:

      SELECT SUM(population) FROM bbc WHERE region = 'Europe'
      

   2. 选择能够显示人口数在150000以下国家的语句

      answer:

      SELECT COUNT(name) FROM bbc WHERE population < 150000
      

   3. 核心聚合函数

      answer:

      AVG(), COUNT(), MAX(), MIN(), SUM()
      

   4. 以下代码获得的结果

      SELECT region, SUM(area)
         FROM bbc 
        WHERE SUM(area) > 15000000 
        GROUP BY region
      

      answer: No result due to invalid use of the WHERE function

   5. 波兰、德国、丹麦三个国家的平均人口

      answer:

      SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')
      

   6. 每个区域的人口密度

      answer:

      SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region
      

   7. 最大人口国家的名称以及人口密度

      answer:

      SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)
      

   8. Pick the result that would be obtained from the following code:

       SELECT region, SUM(area) 
         FROM bbc 
        GROUP BY region 
        HAVING SUM(area)<= 20000000
      

      answer: Table-D

      Americas	732240
      Middle East	13403102
      South America	17740392
      South Asia	9437710