HackerRank SQL Challenge 1

Updated date: 2024-12-31

The following are the solutions to the SQL challenges on HackerRank.

Weather Observation Station 4

SELECT COUNT(*) - COUNT(DISTINCT city) FROM station;

Weather Observation Station 5

SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY ASC LIMIT 1;
SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1;

Weather Observation Station 6

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

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

Weather Observation Station 7

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[AEIOU]$';

Weather Observation Station 8

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[AEIOU].*[AEIOU]$';

Weather Observation Station 9

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

Weather Observation Station 10

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

Weather Observation Station 11

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^AEIOU]' OR CITY REGEXP '[^AEIOU]$';

Weather Observation Station 12

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^AEIOU]' AND CITY REGEXP '[^AEIOU]$';

Higher Than 75 Marks

SELECT name
FROM students
WHERE marks > 75
ORDER BY
    RIGHT(name, 3) ASC,
    id ASC;

Employee Names

SELECT name
FROM employee
ORDER BY name ASC;

Employee Salaries

SELECT name
FROM employee
WHERE salary > 2000
  AND months < 10
ORDER BY employee_id ASC;

The Blunder

SELECT CEIL(
    AVG(salary) - AVG(
        CAST(REPLACE(CAST(salary AS CHAR), '0', '') AS SIGNED)
    )
) AS adjusted_value
FROM employees;