HackerRank SQL Solutions

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

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

select * from CITY;

select * from city where id=1661;

select * from city where countrycode='JPN';

select name from city where countrycode='JPN';

select city, state from station;

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

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

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;

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

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

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

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

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

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

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

select name from employee order by name;

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

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;

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

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

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

select floor(avg(population)) from city;

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

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

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

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

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

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

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

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

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

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

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

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

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

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