Hacker Rank SQL Practice

Solutions to problem : The PADS

There are different ways to solve this 

First : Using SUBSTR and || 

select Name || '(' || UPPER(SUBSTR(Occupation, 1, 1)) || ')' from OCCUPATIONS order by Name;

select 'There are a total of ' || count(Occupation) || ' ' ||LOWER(Occupation) ||'s.' from OCCUPATIONS group by Occupation ORDER BY count(Occupation), Occupation;

Second: Using CASE and CONCAT

select Name|| (CASE Occupation 
WHEN 'Professor' THEN CONCAT(CONCAT('(', 'P'), ')')
WHEN 'Actor'     THEN CONCAT(CONCAT('(', 'A'), ')')
WHEN 'Doctor'    THEN CONCAT(CONCAT('(', 'D'), ')')
WHEN 'Singer'    THEN CONCAT(CONCAT('(', 'S'), ')')
END) Occupation from OCCUPATIONS order by Name ;

select 'There are a total of', count(Occupation), CONCAT(LOWER(Occupation),'s.') from OCCUPATIONS group by Occupation order by count(Occupation), Occupation ;

Solutions to problem : Weather Observation Station 19

SELECT ROUND(SQRT(POWER((MAX(LAT_N) - MIN(LAT_N)),2) + POWER((MAX(LONG_W) - MIN(LONG_W)),2)),4) FROM STATION;