Hacker Rank SQL Challenges Basic Select
Problems
- Revising the Select Query I
- Revising the Select Query II
- Select All
- Select By ID
- Japanese Cities’ Attributes
- Japanese Cities’ Names
- Weather Observation Station 1
- Weather Observation Station 3
- Weather Observation Station 4
- Weather Observation Station 5
- Weather Observation Station 6
- Weather Observation Station 7
- Weather Observation Station 8
- Weather Observation Station 9
- Weather Observation Station 10
- Weather Observation Station 11
- Weather Observation Station 12
- Higher Than 75 Marks
- Employee Names
- Employee Salaries
Revising the Select Query I
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select
all
fields.- Filter
countrycode
equal to ‘USA’Casting the string value either to upper or lower case for comparision will be best approach
andpopulation
greater than 100000Solution:
SELECT * FROM city WHERE population > 100000 AND UPPER(countrycode) = 'USA';
Revising the Select Query II
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select only
name
field.- Filter
countrycode
equal to ‘USA’Casting the string value either to upper or lower case for comparision will be best approach
andpopulation
greater than 120000Solution:
SELECT name FROM city WHERE population > 120000 AND UPPER(countrycode) = 'USA';
Select All
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select
all
fields and returnall
rows.Solution:
SELECT * FROM city;
Select By ID
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select
all
fields.- Filter
id
equal to 1661.Solution:
SELECT * FROM city WHERE id = 1661;
Japanese Cities’ Attributes
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select
all
fields.- Filter
countrycode
equal to ‘JPN’.Solution:
SELECT * FROM city WHERE UPPER(countrycode) = 'JPN';
Japanese Cities’ Names
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select only
name
field.- Filter
countrycode
equal to ‘JPN’.Solution:
SELECT name FROM city WHERE UPPER(countrycode) = 'JPN';
Weather Observation Station 1
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select
city
andstate
field.Solution:
SELECT city, state FROM station;
Weather Observation Station 3
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select only
city
field.- Filter even
id
values.- 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:
- Select count of cities.
- Select count of distinct cities.
- Find difference of both.
Solution:
SELECT COUNT(city) - COUNT(DISTINCT city) FROM station;
Weather Observation Station 5
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Create a temporary view using
WITH
clause which will containscity
, length for eachcity
and row_num when we order each length bycity
- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name starts with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name ends with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name starts and ends with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name doesn’t starts with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name doesn’t end with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name doesn’t starts or ends with vowels.- 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:
- Select only
city
field.- Filter using
REGEX_LIKE
functioncity
name doesn’t starts and ends with vowels.- 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:
- Select only
name
field.- Filter
marks
greater than 75.- 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 byid
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:
- Select only
name
field.- Order the result by
name
in ascending order.Solution:
SELECT name FROM employee ORDER BY name;
Employee Salaries
Problem -> URL
Difficulty Level: Easy
Pseudocode:
- Select only
name
field.- Filter
salary
greater than $2000/month (Salary insalary
field is salary/month) andmonths
less than 10- Order the result by
employee_id
ascending order.Solution:
SELECT name FROM employee WHERE salary > 2000 AND months < 10 ORDER BY employee_id;