[SQL][sqlzoo] SUM and COUNT

0.1. SUM and COUNT                                                                                                       

SUM and COUNT - SQLZOO

1. 1.Total world population

      

      

      

    
    

      <sql />
    
SELECT SUM(population)
FROM world

2. 2.List of continents

      

      

      

    
    

      <sql />
    
select distinct continent
from world

3. 3.GDP of Africa

      

      

      

    
    

      <sql />
    
select sum(gdp) 
from world 
where continent ='africa'

4. 4.Count the big countries

      

      

      

    
    

      <sql />
    
select count(name) 
from world 
where area>=1000000

5. 5.Baltic states population

      

      

      

    
    

      <sql />
    
select sum(population) 
from world 
where name in('Estonia', 'Latvia', 'Lithuania')

6. 6.Counting the countries of each continent

      

      

      

    
    

      <sql />
    
SELECT continent,COUNT(name) 
FROM world 
GROUP BY continent

7. 7.Counting big countries in each continent

      

      

      

    
    

      <sql />
    
select continent,count(population) 
from world 
where population >=10000000 
group by continent

 

8. 8.Counting big continents

      

      

      

    
    

      <sql />
    
SELECT continent 
FROM world 
GROUP BY continent 
HAVING SUM(population) >= 100000000

8.1. SUM and COUNT Quiz                                                                                              

1.

      

      

      

    
    

      <sql />
    
 SELECT SUM(population) FROM bbc WHERE region = 'Europe'

2.

      

      

      

    
    

      <sql />
    
 SELECT COUNT(name) FROM bbc WHERE population < 150000

3.

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

4.

No result due to invalid use of the WHERE function

5.

      

      

      

    
    

      <sql />
    
 SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')

6.

      

      

      

    
    

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

7.

      

      

      

    
    

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

8.

Table-D

Americas	732240
Middle East	13403102
South America	17740392
South Asia	9437710

9.