[HackerRank/SQL] The Report

https://www.hackerrank.com/challenges/the-report/problem

The Report | HackerRank

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

Note

Print "NULL"  as the name if the grade is less than 8.

Explanation

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

---

[풀이]

STUDENTS 테이블에서 name, grade, mark를 조회해야 한다.

GRADE는 원래 존재하는 칼럼이 아니므로, FROM절에서 해당 컬럼을 생성해야 한다.

다음과 같이 CASE WHEN~THEN ELSE END 구문을 이용하여 위의 조건에 맞는 MARKS에 따라 GRADE를 부여한다.

CASE
  WHEN MARKS>=90
  THEN 10
  WHEN MARKS>=80
  THEN 9
  WHEN MARKS>=70
  THEN 8
  WHEN MARKS>=60
  THEN 7
  WHEN MARKS>=50
  THEN 6
  WHEN MARKS>=40
  THEN 5
  WHEN MARKS>=30
  THEN 4
  WHEN MARKS>=20
  THEN 3
  WHEN MARKS>=10
  THEN 2
  ELSE 1
END AS GRADE

조회 순서는 GRADE가 높은 순, 이름 순, MARK가 낮은 순 순서대로 조회할 것이므로 FROM절 안의 ORDER BY절에 GRADE DESC, NAME, MARK ASC를 작성한다.

 

조회 조건에서 GARDE가 8보다 작으면, 이름을 NULL로 표시하여야 한다.

그러므로 SELECT절에서 CASE WHEN THEN ELSE END 절을 사용하여 위의 조건에 맞게 NAME 또는 NULL을 표시할 수 있도록 한다.

SELECT 
    CASE 
    WHEN GRADE >=8
    THEN NAME
    ELSE 'NULL'
    END, GRADE, MARKS

 

 

[코드]

SELECT 
    CASE 
    WHEN GRADE >=8
    THEN NAME
    ELSE 'NULL'
    END, GRADE, MARKS
    
FROM (
    SELECT NAME, MARKS,
    CASE
        WHEN MARKS>=90
        THEN 10
        WHEN MARKS>=80
        THEN 9
        WHEN MARKS>=70
        THEN 8
        WHEN MARKS>=60
        THEN 7
        WHEN MARKS>=50
        THEN 6
        WHEN MARKS>=40
        THEN 5
        WHEN MARKS>=30
        THEN 4
        WHEN MARKS>=20
        THEN 3
        WHEN MARKS>=10
        THEN 2
        ELSE 1
    END AS GRADE
    FROM STUDENTS
    ORDER BY GRADE DESC, NAME, MARKS ASC
)AS A