Hacker Rank SQL Challenges

Problems

  1. Revising the Select Query I
  2. Revising the Select Query II
  3. Select All
  4. Select By ID
  5. Japanese Cities’ Attributes
  6. Japanese Cities’ Names
  7. Weather Observation Station 1
  8. Weather Observation Station 3
  9. Weather Observation Station 4
  10. Weather Observation Station 5
  11. Weather Observation Station 6
  12. Weather Observation Station 7
  13. Weather Observation Station 8
  14. Weather Observation Station 9
  15. Weather Observation Station 10
  16. Weather Observation Station 11
  17. Weather Observation Station 12
  18. Higher Than 75 Marks
  19. Employee Names
  20. Employee Salaries

Revising the Select Query I

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select all fields.
  2. Filter countrycode equal to ‘USA’ Casting the string value either to upper or lower case for comparision will be best approach and population greater than 100000

Solution:

SELECT * FROM city 
WHERE population > 100000 AND UPPER(countrycode) = 'USA';

Revising the Select Query II

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only name field.
  2. Filter countrycode equal to ‘USA’ Casting the string value either to upper or lower case for comparision will be best approach and population greater than 120000

Solution:

SELECT name FROM city 
WHERE population > 120000 AND UPPER(countrycode) = 'USA';

Select All

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select all fields and return all rows.

Solution:

SELECT * FROM city;

Select By ID

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select all fields.
  2. Filter id equal to 1661.

Solution:

SELECT * FROM city 
WHERE id = 1661;

Japanese Cities’ Attributes

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select all fields.
  2. Filter countrycode equal to ‘JPN’.

Solution:

SELECT * FROM city 
WHERE UPPER(countrycode) = 'JPN';

Japanese Cities’ Names

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only name field.
  2. Filter countrycode equal to ‘JPN’.

Solution:

SELECT name FROM city 
WHERE UPPER(countrycode) = 'JPN';

Weather Observation Station 1

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select city and state field.

Solution:

SELECT city, state FROM station;

Weather Observation Station 3

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter even id values.
  3. Return distinct city names. Best approach is to go with GROUP BY instead of DISTINCT. GROUP BY performs better than DISTINCT.

Solution:

SELECT city FROM station
WHERE MOD(id,2) = 0
GROUP BY city;

Weather Observation Station 4

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select count of cities.
  2. Select count of distinct cities.
  3. Find difference of both.

Solution:

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

Weather Observation Station 5

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Create a temporary view using WITH clause which will contains city, length for each city and row_num when we order each length by city
  2. Select city and length of city from temporary view.
    • Filter city_length is in (max length from temporary view and min length from temporary view) and row_num = 1

Solution:

WITH city_name_with_length AS (
    SELECT city, 
    LENGTH(city) AS city_length, 
    ROW_NUMBER() OVER (PARTITION BY LENGTH(city) ORDER BY city) AS row_num 
    FROM station
)
SELECT city, city_length 
FROM city_name_with_length 
WHERE city_length in (
    (SELECT MIN(city_length) FROM  city_name_with_length), 
    (SELECT MAX(city_length) FROM  city_name_with_length)
) AND row_num = 1;

Weather Observation Station 6

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name starts with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(LOWER(city), '^[aeiou]')
GROUP BY city;

Weather Observation Station 7

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name ends with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(LOWER(city), '.[aeiou]$')
GROUP BY city;

Weather Observation Station 8

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name starts and ends with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(city, '^[aeiou][a-z ]*?[aeiou]$', 'i') 
GROUP BY city;

Weather Observation Station 9

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name doesn’t starts with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(LOWER(city), '^[^aeiou]')
GROUP BY city;

Weather Observation Station 10

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name doesn’t end with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(LOWER(city), '.[^aeiou]$')
GROUP BY city;

Weather Observation Station 11

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name doesn’t starts or ends with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(city, '^[^aeiou].|.[^aeiou]$', 'i') 
GROUP BY city;

Weather Observation Station 12

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only city field.
  2. Filter using REGEX_LIKE function city name doesn’t starts and ends with vowels.
  3. Return distinct city names.

Solution:

SELECT city FROM station 
WHERE REGEXP_LIKE(city, '^[^aeiou][a-z ]*?[^aeiou]$', 'i') 
GROUP BY city;

Higher Than 75 Marks

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only name field.
  2. Filter marks greater than 75.
  3. Order the result by last three character of each name (we need to use substring to get last three character) in ascending order and secondary sort by id in ascending order.

Solution:

SELECT name FROM students 
WHERE marks > 75
ORDER BY SUBSTR(name, (LENGTH(name) - 2), 3), id;

Employee Names

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only name field.
  2. Order the result by name in ascending order.

Solution:

SELECT name FROM employee
ORDER BY name;

Employee Salaries

Problem -> URL
Difficulty Level: Easy
Pseudocode:

  1. Select only name field.
  2. Filter salary greater than $2000/month (Salary in salary field is salary/month) and months less than 10
  3. Order the result by employee_id ascending order.

Solution:

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