SQL HACKERRANK select

 

Ans :  select * from CITY where COUNTRYCODE = 'JPN';

Ans: select NAME from CITY where COUNTRYCODE = 'JPN';

Ans :  SELECT DISTINCT CITY

FROM STATION

WHERE CITY LIKE 'a%' OR CITY LIKE 'A%'

   OR CITY LIKE 'e%' OR CITY LIKE 'E%'

   OR CITY LIKE 'i%' OR CITY LIKE 'I%'

   OR CITY LIKE 'o%' OR CITY LIKE 'O%'

   OR CITY LIKE 'u%' OR CITY LIKE 'U%';

Remember: some databases are case-sensitive so think in both perspectives 

Ans : SELECT DISTINCT CITY

FROM STATION

WHERE CITY LIKE '%a' OR CITY LIKE '%A'

   OR CITY LIKE '%e' OR CITY LIKE '%E'

   OR CITY LIKE '%i' OR CITY LIKE '%I'

   OR CITY LIKE '%o' OR CITY LIKE '%O'

   OR CITY LIKE '%u' OR CITY LIKE '%U';

/*

    Enter your query here and follow these instructions:

    1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.

    2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"

    3. Type your code immediately after comment. Don't leave any blank line.

*/SELECT DISTINCT CITY

FROM STATION

WHERE LEFT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u')

  AND RIGHT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u');

Explaination:

LEFT(CITY, 1): Extracts the first character of CITY and checks if it is a vowel.

RIGHT(CITY, 1): Extracts the last character of CITY and checks if it is a vowel.

IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u'): Ensures case-insensitive vowel matching.

DISTINCT: Ensures the results do not contain duplicate

Ans: select DISTINCT CITY from STATION where LEFT(CITY,1) not in ('a','e','i','o','u','A','E','O','I','U');

Answer:Select distinct CITY from STATION where right(CITY , 1) not in ('a','e','i','o','u','A','E','I','O','U');

Answer: Select distinct CITY from STATION where left(CITY , 1) not in ('a','e','i','o','u','A','E','I','O','U') or right(CITY , 1) not in ('a','e','i','o','u','A','E','I','O','U');

Answer : Select distinct CITY from STATION where left(CITY , 1) not in ('a','e','i','o','u','A','E','I','O','U') and right(CITY , 1) not in ('a','e','i','o','u','A','E','I','O','U');

Answer:    SELECT Name FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(Name, 3), ID ASC;

Answer:  Select name from Employee order by name asc;