– 第5关 SUM and COUNT - SQLZOO


-- 练习聚合函数 比如count, sum，avg，max
-- 练习 distinct, order by 

-- 1. Show the total population of the world.
-- 练习sum 
SELECT SUM(population)
FROM world

--2. List all the continents - just once each. 
-- 练习 distinct
select distinct continent
from world 

-- 3. Give the total GDP of Africa
-- 练习sum()和where连用。

select sum(gdp)
from world
where continent = 'Africa'

-- 4. How many countries have an area of at least 1000000
-- 练习count + where连用

select count(area)
from world 
where area > 1000000

-- 5. What is the total population of ('Estonia', 'Latvia', 'Lithuania')
-- 练习count + where...in连用

select sum(population)
from world 
where name in ('Estonia', 'Latvia', 'Lithuania')

-- 6. For each continent show the continent and number of countries.
-- 练习 group by 和having
select continent, count(name)
from world 
group by continent 

-- 7. For each continent show the continent and number of countries with populations of at least 10 million.
-- 练习 count,where 和group by 搭配。

select continent,count(name)
from world 
where population > 10000000
group by continent

-- 8. List the continents that have a total population of at least 100 million.
-- 练习 group by ,having 

select continent
from world
group by continent 
having sum(population) > 100000000