# HackerRank SQL Solutions

·4 mins
Mayukh Datta
Problem Solving SQL

### My solutions to SQL problems on HackerRank #

SQL Problems Page: https://www.hackerrank.com/domains/sql

### 1. Revising the Select Query I #

https://www.hackerrank.com/challenges/revising-the-select-query/problem

``````select * from CITY where COUNTRYCODE='USA' and POPULATION>100000;
``````

### 2. Revising the Select Query II #

https://www.hackerrank.com/challenges/revising-the-select-query-2/problem

``````select NAME from CITY where COUNTRYCODE='USA' and POPULATION>120000;
``````

### 3. Select all #

https://www.hackerrank.com/challenges/select-all-sql/problem

``````select * from CITY;
``````

### 4. Select by ID #

https://www.hackerrank.com/challenges/select-by-id/problem

``````select * from city where id=1661;
``````

### 5. Japanese Cities’ Attributes #

https://www.hackerrank.com/challenges/japanese-cities-attributes/problem

``````select * from city where countrycode='JPN';
``````

### 6. Japanese Cities’ Names #

https://www.hackerrank.com/challenges/japanese-cities-name/problem

``````select name from city where countrycode='JPN';
``````

### 7. Weather Observation Station 1 #

https://www.hackerrank.com/challenges/weather-observation-station-1/problem

``````select city, state from station;
``````

### 8. Weather Observation Station 3 #

https://www.hackerrank.com/challenges/weather-observation-station-3/problem

``````select distinct city from station where mod(id, 2) = 0;
``````

### 9. Weather Observation Station 4 #

https://www.hackerrank.com/challenges/weather-observation-station-4/problem

``````select count(city) - count(distinct city) from station;
``````

### 10. Weather Observation Station 5 #

https://www.hackerrank.com/challenges/weather-observation-station-5/problem

``````select * from(select distinct city,length(city)
from station order by length(city) asc,city asc) where rownum=1
union
select * from(select distinct city,length(city)
from station order by length(city) desc,city desc) where rownum=1;
``````

### 11. Weather Observation Station 6 #

https://www.hackerrank.com/challenges/weather-observation-station-6/problem

``````select distinct city from station
where regexp_like(city, '^[aeiouAEIOU]');
``````

### 12. Weather Observation Station 7 #

https://www.hackerrank.com/challenges/weather-observation-station-7/problem

``````select distinct city from station
where regexp_like(city, '*[aeiouAEIOU]\$');
``````

### 13. Weather Observation Station 8 #

https://www.hackerrank.com/challenges/weather-observation-station-8/problem

``````select distinct city from station
where regexp_like(city, '^[aeiouAEIOU].*[aeiouAEIOU]\$');
``````

### 14. Weather Observation Station 9 #

https://www.hackerrank.com/challenges/weather-observation-station-9/problem

``````select distinct city from station
where regexp_like(city, '^[^aeiouAEIOU]');
``````

### 15. Weather Observation Station 10 #

https://www.hackerrank.com/challenges/weather-observation-station-10/problem

``````select distinct city
from station
where regexp_like(city, '*[^aeiouAEIOU]\$');
``````

### 16. Weather Observation Station 11 #

https://www.hackerrank.com/challenges/weather-observation-station-11/problem

``````select distinct city from station
where regexp_like(city, '^[^aeiouAEIOU]|*[^aeiouAEIOU]\$');
``````

### 17. Weather Observation Station 12 #

https://www.hackerrank.com/challenges/weather-observation-station-12/problem

``````select distinct city from station
where regexp_like(city, '^[^aeiouAEIOU].*[^aeiouAEIOU]\$');
``````

### 18. Employee Names #

https://www.hackerrank.com/challenges/name-of-employees/problem

``````select name from employee order by name;
``````

### 19. Employee Salaries #

https://www.hackerrank.com/challenges/salary-of-employees/problem

``````select name from employee where salary > 2000
and months < 10 order by employee_id;
``````

### 20. Type of Triangle #

https://www.hackerrank.com/challenges/what-type-of-triangle/problem

``````SELECT CASE
WHEN A + B > C THEN CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
WHEN A != B OR B != C OR A != C THEN 'Scalene'
END
ELSE 'Not A Triangle'
END
FROM TRIANGLES;
``````

### 21. Revising Aggregations - The Count Function #

https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

``````select count(countrycode) from city where population > 100000;
``````

### 22. Revising Aggregations - The Sum Function #

https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

``````select sum(population) from city where district = 'California';
``````

### 23. Revising Aggregations - Averages #

https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

``````select avg(population) from city where district='California';
``````

### 24. Average Population #

https://www.hackerrank.com/challenges/average-population/problem

``````select floor(avg(population)) from city;
``````

### 25. Higher Than 75 Marks #

https://www.hackerrank.com/challenges/more-than-75-marks/problem

``````select name from students where marks>75
order by substr(name, -3), id;
``````

### 26. Japan Population #

https://www.hackerrank.com/challenges/japan-population/problem

``````select sum(population) from city where countrycode='JPN';
``````

### 27. Population Density Difference #

https://www.hackerrank.com/challenges/population-density-difference/problem

``````select max(population) - min(population) from city;
``````

### 28. The Blunder #

https://www.hackerrank.com/challenges/the-blunder/problem

``````select ceil(avg(salary) - avg(to_number(replace(to_char(salary), '0'))))
from employees;
``````

### 29. Top Earners #

https://www.hackerrank.com/challenges/earnings-of-employees/problem

``````select max(months * salary), count(months * salary)
from Employee where (months * salary)
= (select max(months * salary) from Employee);
``````

### 30. Weather Observation Station 2 #

https://www.hackerrank.com/challenges/weather-observation-station-2/problem

``````select round(sum(lat_n), 2), round(sum(long_w), 2)
from station;
``````

### 31. Weather Observation Station 13 #

https://www.hackerrank.com/challenges/weather-observation-station-13/problem

``````select round(sum(lat_n), 4) from station
where lat_n > 38.7880 and lat_n < 137.2345;
``````

### 32. Weather Observation Station 14 #

https://www.hackerrank.com/challenges/weather-observation-station-14/problem

``````select round(max(lat_n), 4) from station
where lat_n < 137.2345;
``````

### 33. Weather Observation Station 15 #

https://www.hackerrank.com/challenges/weather-observation-station-15/problem

``````select round(long_w, 4) from station
where lat_n = (select max(lat_n)
from station where lat_n < 137.2345);
``````

### 34. Weather Observation Station 16 #

https://www.hackerrank.com/challenges/weather-observation-station-16/problem

``````select round(min(lat_n), 4)
from station where lat_n > 38.7780;
``````

### 35. Weather Observation Station 17 #

https://www.hackerrank.com/challenges/weather-observation-station-17/problem

``````select round(long_w, 4) from station
where lat_n = (select min(lat_n)
from station where lat_n > 38.7780);
``````

### 36. Asian Population #

https://www.hackerrank.com/challenges/asian-population/problem

``````select sum(city.population)
from city, country
where city.countrycode = country.code
and continent='Asia';
``````

### 37. African Cities #

https://www.hackerrank.com/challenges/african-cities/problem

``````select city.name from city, country
where city.countrycode = country.code
and country.continent='Africa';
``````

### 38. Average Population of Each Continent #

https://www.hackerrank.com/challenges/average-population-of-each-continent/problem

``````select country.continent, floor(avg(city.population))
from city, country
where city.countrycode = country.code
group by country.continent;
``````