[Hacker Rank] BASIC SELECT - 2

👻 QUESTION

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

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(LOWER(CITY), 1) IN ('a', 'e', 'i', 'o', 'u');

 

🔮 TIPS

The LEFT() function extracts a number of characters from a string (starting from left).

LEFT(string, number_of_chars)

 

---

 

👻 QUESTION

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

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(LOWER(CITY), 1) IN ('a', 'e', 'i', 'o', 'u');

 

🔮 TIPS

The RIGHT() function extracts a number of characters from a string (starting from right).

RIGHT(string, number_of_chars)

 

---

 

👻 QUESTION

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.

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(LOWER(CITY), 1) IN ('a', 'e', 'i', 'o', 'u')
AND RIGHT(LOWER(CITY), 1) IN ('a', 'e', 'i', 'o', 'u');

 

---

 

👻 QUESTION

Query the list of CITY names from STATION that do not start with vowels.
Your result cannot contain duplicates.

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(LOWER(CITY), 1) NOT IN ('a', 'e', 'i', 'o', 'u');

 

---

 

👻 QUESTION

Query the list of CITY names from STATION that do not end with vowels.
Your result cannot contain duplicates.

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(LOWER(CITY), 1) NOT IN ('a', 'e', 'i', 'o', 'u');

 

---

 

👻 QUESTION

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels.
Your result cannot contain duplicates.

 

🚀ANSWER

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(LOWER(CITY), 1) NOT IN ('a', 'e', 'i', 'o', 'u')
AND RIGHT(LOWER(CITY), 1) NOT IN ('a', 'e', 'i', 'o', 'u');

 

---

 

👻 QUESTION

Query the Name of any student in STUDENTS who scored higher than  Marks.
Order your output by the last three characters of each name.
If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

 

🚀ANSWER

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME, 3), ID;

 

---

 

👻 QUESTION

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

 

🚀ANSWER

SELECT NAME
FROM EMPLOYEE
ORDER BY NAME ASC;

 

---

 

👻 QUESTION

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than  per month who have been employees for less than  months.
Sort your result by ascending employee_id.

 

🚀ANSWER

SELECT NAME
FROM EMPLOYEE
WHERE SALARY > 2000 AND MONTHS < 10
ORDER BY EMPLOYEE_ID ASC;

 

---

 

🔮 TIPS

The MID() function extracts a substring from a string (starting at any position).

Note: The position of the first character in the string is 1.
Note: The position of the last character in the string is -1.
Note: The MID() and SUBSTR() functions equals the SUBSTRING() function.

MID(string, start, length)

SUBSTR(string, start, length)
SUBSTR(string FROM start FOR length)

SUBSTRING(string, start, length)
SUBSTRING(string FROM start FOR length)

The LOWER() function converts a string to lower-case.

Note: The LCASE() function is equal to the LOWER() function.

LOWER(text)
LCASE(text)

The UPPER() function converts a string to upper-case.

Note: This function is equal to the UCASE() function.

UPPER(text)
UCASE(text)