SQL语句练习（三）

 bbc国家概况表

1. 显示世界人口总和

  SELECT SUM(population) FROM bbc

 
2. 列出所有的地区，但每个地区只能显示一次
       

 SELECT DISTINCT region FROM bbc

 or:  

 SELECT region FROM bbc GROUP BY region

3. 显示GDP超过所有Africa（非洲）国家总和的国家

 

  SELECT name FROM bbc WHERE gdp > (SELECT SUM(gdp) FROM bbc WHERE region = 'Africa')

 

4. 显示每个地区以及的该地区的国家总数

 

  SELECT region, COUNT(name) FROM bbc GROUP BY region

 

5. 显示每个地区以及的该地区国家总人口数不少于１０００万的国家总数
       

    SELECT region, COUNT(name) FROM bbc WHERE population >=10000000 GROUP BY region

  or: 

   SELECT region, COUNT(name) FROM (SELECT region, name FROM bbc WHERE population>=10000000)temp GROUP BY region

6. 列出人口总数不少于１亿的地区
      

    SELECT region FROM bbc GROUP BY region HAVING SUM(population)>=100000000