I cannot use the INTERSECT operator

Question

I'm trying to resolve this exercise:

'Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.'

I tried resolving it using the INTERSECT operator like this:

SELECT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) = 'A' OR LEFT(CITY, 1) = 'O'
OR LEFT(CITY, 1) = 'E' OR LEFT(CITY, 1) = 'I' OR
LEFT (CITY, 1) OR LEFT(CITY, 1) = 'U'

INTERSECT

SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1) = 'O'
OR RIGHT(CITY, 1) = 'E' OR RIGHT(CITY, 1) = 'I' OR
RIGHT (CITY, 1) OR RIGHT(CITY, 1) = 'U';

But I receive the following error message:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT
SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1)' at line

What am I doing wrong and what other way do I have to solve this exercise?

Answer 1

Query the list of CITY names from STATION which have vowels as both their first and last characters.

You seem to be overcomplicating this. As I understand the question, you just need a where clause that filters the table once - regexes come handy for this:

select city
from station
where city regexp '^[aeiou].*[aeiou]$'

The pattern describes a string that starts and ends with a wovel (^ represents the beginning of the string, and $ is the end).

If you have duplicate city in the table, then use select distinct city from ... instead.

If you want to use left() and right():

select city
from station
where 
    right(city, 1) in ('a', 'e', 'i', 'o', 'u')
    and left(city, 1) in ('a', 'e', 'i', 'o', 'u')