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;