My solutions to SQL problems on HackerRank

SQL problem 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;