Hacker Rank SQL

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.

Column Type
ID Integer
Name String
Marks Integer

Gradesย contains the following data:

Grade Min_Mark Max_Mark
1 0 9
2 10 19
3 20 29
4 30 39
5 40 49
6 50 59
7 60 69
8 70 79
9 80 89
10 90 99

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

Id Name Marks
1 Julia 88
2 Samantha 68
3 Maria 99
4 Scarlet 78
5 Ashley 63
6 Jane 81

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:

Id Name Marks Grade
1 Julia 88 9
2 Samantha 68 7
3 Maria 99 10
4 Scarlet 78 8
5 Ashley 63 7
6 Jane 81 9

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.