Hackerrank - The Report

1. 문제

 

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-) assigned to them, order those particular students by their marks in ascending order.

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true

 

2. 문제 조건

 

1. Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark -> SELECT
2. Ketty doesn't want the NAMES of those students who received a grade lower than 8, if the grade is lower than 8, use "NULL" as their name -> SELECT에서 IF문을 사용하여 조건문 활용
3. The report must be in descending order by grade -- i.e. higher grades are entered first. -> ORDER BY 공통조건
4. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. -> 8등급 이상일 경우 ORDER BY 조건
5.  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-) assigned to them, order those particular students by their marks in ascending order. -> 8등급 미만일 경우 ORDER BY 조건

 

3. 코드 작성

 

SELECT IF(grades.grade < 8, NULL, students.name)
     , grades.grade
     , students.marks
FROM students
    JOIN grades ON students.marks BETWEEN grades.min_mark AND grades.max_mark
ORDER BY grades.grade DESC, IF(grades.grade >= 8, students.name, students.marks);

 

4. 회고

 

• 지금까지 JOIN과 관련된 문제를 풀때 두 테이블 간 JOIN하는 조건을 =만 썼었다.
• 하지만, 이번 문제를 통해서 알 수 있듯이, BETWEEN도 활용 가능하다.
• ORDER BY 안에도 조건문을 사용할 수 있다.