How to solve # 7 in SQLZOO naming section

Question

All:

I am learning SQL now, but stuck in # 7

http://sqlzoo.net/wiki/SELECT_names

Bahamas has three a - who else?

Find the countries that have three or more a in the name

thank

Answer 1

Try using the operator LIKE:

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

If you want to do a case-insensitive search for aor a, then you can use the function LOWER():

SELECT name FROM world
WHERE LOWER(name) LIKE '%a%a%a%'

Answer 2

Try this query:

SELECT name
FROM world
WHERE LEN(name) - LEN(REPLACE(name,'a', '')) > 2

Answer 3

I am trying to resolve this lecture with regular expressions, but could not do it correctly.

regexp '(.*[a]){3,}'

3 or more 'a' must match, which it does, but:

"Sao Tomé and Príncipe" with 2 a matches LIKE '%a%a%a%', which is the correct answer.

I'm curious what is going wrong here?

Answer 4

SELECT name
FROM world
WHERE LENGTH(name) - LENGTH(REPLACE(lower(name),'a', '')) >= 3