SQL: Query the list of CITY names starting with vowels (a, e, I, o, u) from STATION. Your result cannot contain duplicates.?

Question

my answer: SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[aeiou]%';

but it's wrong...any clue coders .?

Answer 1

SELECT DISTINCT
CITY
FROM STATION
WHERE lower(substr(CITY,1,1)) in ('a','e','i','o','u') ;

Answer 2

This is the solution that worked for me (if you are using MySQL):

SELECT DISTINCT
CITY
FROM
STATION
WHERE CITY
REGEXP '^[aeiou]'

Regular expression can be confusing but here is good reference https://www.tutorialspoint.com/mysql/mysql-regexps.htm.

So what I have here is select the distinct (different) rows within the city row that is located in (FROM) the Station table and I want the returned values to be only the city names that's first letter is a vowel. So the SELECT DISTINCT will pull no duplicates, the FROM will call the Station table, the WHERE will specify that we are asking for the CITY and the LIKE is asking for the condition matching ^ (which is the beginning of the string) and [aeiou] (which are the letters we are searching for at the beginning of the string). By using the single quotes around our regular expression, we are calling the string literals (or the values that match literally the data we want). Hope this helps someone.

Answer 3

SELECT CITY FROM STATION where SUBSTR(CITY,1,1) IN('A','E','I','O','U');

Answer 4

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

Answer 5

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]';

Answer 6

but Demeth , I used this on w3schools.org SQL compiler and there it's working nice...try here http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_wildcard_charlist&ss=-1

Answer 7

I am a self taught coder..I read from multiple sources .. kindly help me

Answer 8

@Demeth multiple like conditions also didnt work for this particular query as you told can you suggest something else?

Answer 9

SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]#x27;

It's a regular expression query !!

Answer 10

SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[AEIOU]%';

/*this will work.*/

Answer 11

SELECT DISTINCT city FROM station
WHERE city LIKE 'A%'
or city LIKE 'E%'
or city LIKE 'I%'
or city LIKE 'O%'
or city LIKE 'U%';
/*try this*/

Answer 12

select distinct city from station where city LIKE 'A%'
or city LIKE 'E%'
OR city LIKE 'I%'
OR city LIKE 'O%'
OR city LIKE 'U%'
OR city LIKE 'a%'
OR city LIKE 'e%'
OR city LIKE 'i%'
OR city LIKE 'o%'
OR city LIKE 'u%';

use the cap and small both. you'll get your answer.

Answer 13

select distinct city from station where city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' or city like 'A%' or city like 'E%' or city like 'I%' or city like 'O%' or city like 'U%';

//check for both uppercase and lowercase letters

Answer 14

select distinct city from station where (city LIKE 'A%' or city LIKE 'E%' OR city LIKE 'I%' OR city LIKE 'O%' OR city LIKE 'U%') and (city LIKE '%a' OR city LIKE '%e' OR city LIKE '%i' OR city LIKE '%o' OR city LIKE '%u');

This will defenitely work for oracle

Answer 15

select distinct city from stations
where city like '[aeiou]%'

Answer 16

Solution in Oracle:

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[AEIOU]');

Answer 17

https://nikimaria.com/mysql/mysql-queries-using-regular-expressions/

Answer 18

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

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Answer 19

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE "a%" OR CITY LIKE "e%" OR CITY LIKE "i%" OR CITY LIKE "o%" OR CITY LIKE "u%"

Answer 20

select distinct city from station
where
city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%';