The Report Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | The Report Solution

Also Visit:ย  Average Population of Each Continent Solution

Problem

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)

Solution โ The Report

MySQL Code
```SELECT CASE
WHEN G.grade > 7 THEN S.name
ELSE NULL
end AS names,
G.grade,
S.marks

FROM   students S
JOIN grades G
ON S.marks BETWEEN G.min_mark AND G.max_mark

ORDER  BY G.grade DESC,
names ASC,
S.marks ASC;

```

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.