SQL/select_from_world.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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
-- https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial --

---

(2) Show the name for the countries that have a population of at least 200 million.
    200 million is 200000000, there are eight zeros.
---

SELECT name FROM world
WHERE population = 64105700

---

(3) Give the name and the per capita GDP for those countries with a population of at least 200 million.

---

SELECT name, gdp/population FROM world
WHERE population > 200000000;

--

(4) Show the name and population in millions for the countries of the continent 'South America'.
    Divide the population by 1000000 to get population in millions.

--

SELECT name, population/1000000 FROM world
WHERE continent = 'South America'

--

(5) Show the name and population for France, Germany, Italy

--

SELECT name, population FROM world
WHERE name IN ('France', 'Germany', 'Italy')

--

(6) Show the countries which have a name that includes the word 'United'

--

SELECT name FROM world
WHERE name LIKE'United%'

--

(7) Show the countries that are big by area or big by population. Show name, population and area.

--

SELECT name, population, area FROM world
WHERE area > 3000000 OR population > 250000000

--

(8) Exclusive OR (XOR). Show the countries that are big by area or big by population but not both.
    Show name, population and area.
--

SELECT name, population, area FROM world
WHERE area > 3000000 XOR population > 250000000

--

(9)

--

SELECT name, ROUND(population/1000000  , 2), ROUND(GDP/1000000000 ,2) FROM world
WHERE continent = 'South America';

--

(10) Show the name and per-capita GDP for those countries with a GDP of at least one trillion
     (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

--

SELECT name,ROUND((gdp/population), -3) AS PerCapitaGDP  from world
WHERE GDP >= 1000000000000

--

(11) Show the name and capital where the name and the capital have the same number of characters.

--

SELECT name, capital
  FROM world
 WHERE LENGTH(name) =LENGTH(capital)

--

(12) Show the name and the capital where the first letters of each match.
    Dont include countries where the name and the capital are the same word.

--

SELECT name, capital
FROM world
WHERE LEFT(name,1) = LEFT (capital,1) AND name <> capital

--

(13) Find the country that has all the vowels and no spaces in its name.

--

SELECT name
   FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %'