[SQL HackerRank] The Report (join 응용)

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

The Report | HackerRank

 

<Students>

<Grades>

 

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.

 

: Students , Grades 라는 두 테이블이 있다. Student 테이블에 학생들의 각 점수(Marks) 가 있는데, Grades 테이블에서 해당 MARK에 매칭되는 Grade를 가져오면 된다.

 

<조건>

1. grade 내림차순

2. 같은 grade가 있다면 이름을 알파벳 순으로 오름차순

3. 같은 이름이 있다면 marks 순으로 오름차순

4. grade가 8보다 작다면 "NULL" 값 로 지정

 

 

<완성 테이블의 모습>

 

 

 

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

 

select
            case when g.grade < 8 then "NULL"
            else s.name
            end as a
            , g.grade, s.marks
from students as s
inner join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, s.name, s.marks

join 조건이 새로워서 재밌당