SQL ZOO 练习 —— SUM and COUNT_select region, sum(area) from bbc where sum(area)

SUM and COUNT

1.Show the total population of the world.

SELECT SUM(population)
FROM world
1
2

2.List all the continents - just once each.

SELECT DISTINCT continent 
FROM world
1
2

3.Give the total GDP of Africa

SELECT SUM(gdp) 
FROM world 
WHERE continent = 'Africa'
1
2
3

4.How many countries have an area of at least 1000000

SELECT COUNT(name) 
FROM world 
WHERE area >= 1000000
1
2
3

5.What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’)

SELECT SUM(population) 
FROM world 
WHERE name IN('Estonia','Latvia','Lithuania')
1
2
3

6.For each continent show the continent and number of countries.

第一种

SELECT distinct continent,(SELECT COUNT(*)
FROM world a
WHERE a.continent = b.continent ) AS number
FROM world b
1
2
3
4

第二种

SELECT continent,COUNT(name)
FROM world
GROUP BY continent
1
2
3

7.For each continent show the continent and number of countries with populations of at least 10 million.

SELECT continent,COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent
1
2
3
4

8.List the continents that have a total population of at least 100 million.

SELECT continent
FROM world 
GROUP BY continent
HAVING SUM(population) >= 100000000
1
2
3
4

SUM and COUNT Quiz

1. Select the statement that shows the sum of population of all countries in ‘Europe’

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

2. Select the statement that shows the number of countries with population smaller than 150000

SELECT COUNT(name) FROM bbc WHERE population < 150000
1

3. Select the list of core SQL aggregate functions

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

4. Select the result that would be obtained from the following code:

SELECT region, SUM(area)
   FROM bbc 
  WHERE SUM(area) > 15000000 
  GROUP BY region
1
2
3
4

Result：

No result due to invalid use of the WHERE function
1

5. Select the statement that shows the average population of ‘Poland’, ‘Germany’ and ‘Denmark’

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

6. Select the statement that shows the medium population density of each region

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

7. Select the statement that shows the name and population density of the country with the largest population

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

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
1
2
3
4

Result：