How to write an SQL query to match city names ending with vowels?

Why is it not working?

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. our result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION 
WHERE CITY LIKE '%A' OR CITY LIKE '%E' OR LIKE '%I' LIKE '%O' OR LIKE '%U' 
ORDER BY CITY;

What would be the correct query?

Answer

You query has syntax error, for each “or”, you need to put city like instead of or. In addition, remember that in most of the dbms, it is case sensitive. If you want to ignore both upper case and lower case, do it like this. I am using mysql syntax, different dbms has different functions for lcase

SELECT DISTINCT CITY FROM STATION WHERE lcase(CITY) LIKE '%a' OR lcase(CITY) LIKE '%e' OR lcase(CITY) LIKE '%i' or lcase(CITY) LIKE '%o' OR lcase(CITY) LIKE '%u' ORDER BY CITY;

Leave a Reply

Your email address will not be published. Required fields are marked *