SQL/zoo_select_names.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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
-- https://sqlzoo.net/wiki/SELECT_names --

--

(1)  You can use WHERE name LIKE 'B%' to find the countries that start with "B".

--

SELECT name FROM world
  WHERE name LIKE 'Y%'

--

(2) Find the countries that end with y

--

SELECT name FROM world
  WHERE name LIKE '%Y'

--

(3) Find the countries that contain the letter x

--

SELECT name FROM world
  WHERE name LIKE '%X%'

--

(4) Iceland, Switzerland end with land - but are there others? Find the countries that end with land

--

SELECT name FROM world
  WHERE name LIKE '%land'

--

(5) Find the countries that start with C and end with ia

--

SELECT name FROM world
  WHERE name LIKE 'C%ia'

--

(6) Find the country that has oo in the name

--

SELECT name FROM world
  WHERE name LIKE '%oo%'

--

(7) Find the countries that have three or more a in the name

--
SELECT name FROM world
  WHERE name LIKE '%a%a%a%'
--

(8) Find the countries that have "t" as the second character.

--

SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

--

(9) Find the countries that have two "o" characters separated by two others.

--

SELECT name FROM world
 WHERE name LIKE '%o__o%'

--

(10) Find the countries that have exactly four characters.

--

SELECT name FROM world
 WHERE name LIKE '____'

--

(11) Find the country where the name is the capital city.

--

SELECT name
  FROM world
 WHERE name LIKE capital

--

(12) Find the country where the capital is the country plus "City".

--

SELECT name
  FROM world
WHERE capital = concat(name, ' City');

--

(13) Find the capital and the name where the capital includes the name of the country.

--

SELECT capital, name
  FROM world
WHERE capital LIKE concat('%', name, '%');

--

(14) Find the capital and the name where the capital is an extension of name of the country.

--

SELECT capital, name from world
WHERE capital LIKE concat (name,'%') AND capital > name;

--

(15) Show the name and the extension where the capital is an extension of name of the country.

--

SELECT name , REPLACE(capital, name , '') AS ext FROM world
WHERE capital LIKE concat(name, '_%');