[MySql] HackerRank challenges - BASIC 3

Weather Observation Station 18

-- a=MIN(LAT_N)
-- B=MIN(LONG_W)
-- C=MAX(LAT_N)
-- D=MAX(LONG_W)
-- round(distance, 4)
-- DISTANCE = |A-C| + |B-D|

SELECT ROUND((ABS(MIN(LAT_N)-MAX(LAT_N)) 
              + (ABS(MIN(LONG_W)-MAX(LONG_W)))),4) 
FROM STATION;

 

Weather Observation Station 19

-- A= MIN(LAT_N)
-- B=MAX(LAT_N)
-- C=MIN(LONG_W)
-- D= MAX(LONG_W)

-- Distance(p1,p2)^2 = (A-C)^2 - (B-D)^2
-- sqrt(pow())

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

 

 

Population Census

• Inner join

SELECT SUM(CITY.POPULATION)
FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia';

 

 

African Cities

SELECT CITY.NAME
FROM CITY 
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa';

 

 

Average Population of Each continent

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION))
FROM CITY 
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;

 

 

The PADS

 

-- 1. alphabet 순서로 나열 -> table occupations
--     첫 글자 -> 괄호 안에 ex) (A)
-- CONCAT('A','B') => AB
-- SUBSTR(NAME, 1, 1) => (NAME, idx, len)
-- ORACLE -> SUBSTR // MYSQL -> SUBSTRING



SELECT CONCAT(NAME,'(', SUBSTRING(OCCUPATION, 1,1),')')
FROM OCCUPATIONS
ORDER BY NAME, SUBSTRING(OCCUPATION, 1,0);


-- 2. 직업(lower로 출력), 직업 수 => asc
-- OUTPUT = 'There are a total of [occupation_count] [occupation]s.'



SELECT CONCAT('There are a total of ', COUNT(OCCUPATION),' ',LOWER(OCCUPATION),'s.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(OCCUPATION), OCCUPATION;