[hackerrank-sql] Medium Level

The PADS

• concat() : 문자열을 합쳐서 쿼리해준다.
• LEFT() : 문자열을 인덱싱해서 쿼리해준다.
• LOWER() : 문자열을 소문자로 변환하여 쿼리해준다.

SELECT concat(o.NAME, "(", LEFT(o.OCCUPATION, 1), ")")
FROM OCCUPATIONS AS o
ORDER BY o.NAME;

SELECT concat("There are a total of ", COUNT(o.OCCUPATION), " ", LOWER(o.OCCUPATION), "s.")
FROM OCCUPATIONS AS o
GROUP BY o.OCCUPATION
ORDER BY COUNT(o.OCCUPATION), o.OCCUPATION;

OCCUPATIONS

SET @r1=0, @r2=0, @r3 =0, @r4=0;
SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor)
FROM (
SELECT CASE Occupation WHEN 'Doctor' THEN @r1:=@r1+1
                       WHEN 'Professor' THEN @r2:=@r2+1
                       WHEN 'Singer' THEN @r3:=@r3+1
                       WHEN 'Actor' THEN @r4:=@r4+1 END AS RowLine,
       CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,
       CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,
       CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,
       CASE WHEN Occupation = 'Actor' THEN Name END AS Actor
FROM OCCUPATIONS 
ORDER BY Name) TEMP
GROUP BY RowLine

Binary Tree Nodes

SELECT N, 
    CASE WHEN P IS NULL THEN 'Root'
        WHEN N IN (SELECT P FROM BST) THEN 'Inner'
        ELSE 'Leaf' 
    END
FROM BST
ORDER BY N;

Weather Observation Station 18

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

Weather Observation Station 19

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

Weather Observation Station 20

zeta위키에서 본 풀이. 이런 생각은 대체 어떻게 하셨을까 존경..

SELECT ROUND(LAT_N,4) FROM STATION AS S
WHERE (SELECT COUNT(*) FROM STATION WHERE LAT_N<S.LAT_N)
=(SELECT COUNT(*) FROM STATION WHERE LAT_N>S.LAT_N);

The Report

SELECT IF(g.Grade < 8, NULL, s.Name), g.Grade, s.Marks
FROM Students AS s
JOIN Grades AS g 
ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
ORDER BY g.Grade DESC, s.Name, s.Marks

Reference

• sql에서 문자열 다루기