SQL/zoo_sum_and_count.sql at master · ccamila/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
--  https://sqlzoo.net/wiki/SUM_and_COUNT --

--

(1) Show the total population of the world.

--

SELECT SUM(population)
FROM world

--

(2) List all the continents - just once each.

--

SELECT DISTINCT continent FROM world

--

(3) Give the total GDP of Africa

--

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

--

(4) How many countries have an area of at least 1000000

--

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

--

(5) What is the total population of ('Estonia', 'Latvia', 'Lithuania')

--

SELECT SUM (population) AS total_population FROM world

WHERE name IN ('Estonia', 'Latvia', 'Lithuania')


--

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

--

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.

--

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.

--

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