-
Notifications
You must be signed in to change notification settings - Fork 1
/
tutorial1.sql
92 lines (62 loc) · 1.79 KB
/
tutorial1.sql
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
-- SELECT names
-- 1. Find the country that start with Y
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. 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%';
-- 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 LIKE 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 name, capital
FROM world
WHERE capital LIKE concat(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, '_%');