Hacker Rank 문제 풀이 #1

 

 

○ MS SQL 문제 1

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

Explanation

The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

 

 

SELECT 
    Doctor, Professor, Singer, Actor
FROM 
    (SELECT 
        ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS rn,
        Name,
        Occupation
    FROM 
        OCCUPATIONS) AS sub
PIVOT
    (
    MAX(Name)
    FOR Occupation IN (Doctor, Professor, Singer, Actor)
    ) AS pivoted
ORDER BY rn;

 >> 직업에 대한 이름을 알파벳 순으로 정렬하기 위해 ROW_NUMBER() 함수를 사용한다.

그 순서를 나타내는 컬럼 rn과 이름, 직업 순서로 되어있는 From 아래의 서브쿼리를 실행하면 

다음과 같은 결과가 나온다.

이를 PIVOT 함수를 사용하여 각 직업에 대한 이름을 넣는다. 각 직업에 대한 최대 이름수 만큼의 테이블을 만들고 빈 셀값은 NULL 로 채운다.

 

[PIVOT 함수 형식]

SELECT 가져올 컬럼명 
FROM 테이블
PIVOT ( 집계함수(검색할 열) 
          FOR 크로스탭의 기준이 되는 열
          IN (변환시킬 행)) AS 피벗테이블의 별칭

 

쉽게 설명하자면 가져올 컬럼명이 제일 상단의 컬럼명이 되고

PIVOT 내부의 계산식(집계함수 사용) 을 통해 각각의 기준 열에 해당하는 데이터가 생성(입력)된다.

 

반대로 열을 행의 집합으로 보여주는 UNPIVOT 함수도 있다.

 

[UNPIVOT 함수 형식]

SELECT 가져올 컬럼명, 데이터를 재구축할 컬럼명
FROM 테이블
UNPIVOT ( 검색할 열
                 FOR 행으로 표시할 열들의 기준 열
                 IN ( 행으로 표시할 열 이름 )) AS 언피벗 테이블의 별칭

 

 

 

 

○ MS SQL 문제 2

 

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

Sample Input

Sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

Explanation

The Binary Tree below illustrates the sample:

select N , 
case
when N in (select N from BST where P is null) then 'Root'
when N in (select N from BST where not exists (select 1 from BST as sub where BST.N = sub.P)) then 'Leaf'
else 'Inner'
end as node
from BST
order by N

 

>> P 값이 Null이면 Root, N 값이 P에 없으면( 즉 부모노드가 한번이라도 된적이없으면) Leaf, 그 외는 Inner로 

Case When 구문을 사용하여 분류하였다.

이때 P에 없는 N 값을 구하기 위해 not exists를 사용하였다. null값이 있으면 not in을 사용하기 어렵기 때문에 exist 구문을 사용하였다. 따라서 BST를 self join하여 P에 값이 있는 N 값의 경우에는 exist, 그 외에는 not exist로 처리되어 분류하였다.

 

 

 

○ MS SQL 문제 3

 

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to  decimal places.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

 

 

select convert(decimal(10,4) , a.LAT_N)
from (
    select LAT_N , row_number() over(order by LAT_N ASC ) as rn, count(*) over () as total_rows
    from STATION ) as a
where a.rn in ((total_rows +1)/2 , (total_rows +2)/2)

 

>> 중앙값이 두개의 값 사이에 있을 수 있으므로 1을 더한 것에 2로 나눈 값과 2를 더한 것에 2로 나눈 값 중 해당하는 순위의 LAT_N을 중앙값으로 보고, convert(decimal(10,4) , a.LAT_N)을 통해 해당 값을 소수점 4자리까지 출력되도록 반올림하여 구하였다.

 

 

○ MS SQL 문제 4

 

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

 

 

select case when (Marks/10) + 1 < 8 then null else Name end as name, 
case when Marks < 100 then (Marks/10) + 1 else Marks/10 end as grade, Marks 
from Students
order by grade DESC, Name

 

>> case when 조건문을 통해 8점 미만의 학생의 이름을 null값으로 두고, marks의 값을 통해 grade를 구하였다.

또한 order by 정렬으로 성적 내림차순 , 이름 오름차순을 적용하였다.