Final-result-Hackerrank-MySQL

HackerRank – SQL – Basic Join – The Report – Solution

Publish Date - November 2nd, 2022

|

Last Modified - November 9th, 2022

Here’s a cool problem from HackerRank that’s specifically centered around JOINING and CASE usage. I used specifically MySQL to solve this problem!

The Problem

You are given two tables: Students and GradesStudents contains three columns IDName and Marks.

MySQL-Table-grades

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: NameGrade and MarkKetty 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 query

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:

Student table and grades table joined on the right with grades.

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

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

The Solution

SELECT 
    CASE
    WHEN grades.grade < 8 THEN 'NULL'
    ELSE students.name
    END,
    grades.grade, students.marks
    FROM students, grades 
WHERE students.marks >= grades.min_mark AND students.marks <= grades.max_mark
ORDER BY grades.grade DESC, students.name

Firstly, analyzing the request query – you know there’s going to be a CASE needed in this query with a JOIN since it’s asking you to use one table’s criteria to organize the other table’s information.

Next, this statement here:

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.

You know you’re going to need to query three columns, students.name, grades.grade, students.mark, which will be your output.

Next, you need to handle that pesky ask:

Ketty doesn’t want the NAMES of those students who received a grade lower than 8. + Finally, if the grade is lower than 8, use “NULL” as their name

So you handle this by a CASE clause by surfacing a string ‘NULL’ value if when grades.grade is < 8 (grade 8), with the ending ELSE clause handling all of the inclusions of the NAME.

From there, you complete your JOIN criteria and then proceed to some of the other requests.

If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically.

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.

Before you tackle the first statement (which is clearly an ORDER BY), you need to limit the amount of records you query. If you decide to just ORDER BY grades.grade DESC, students.name – then you will get a massive output from all grades.

SELECT 
    CASE
    WHEN grades.grade < 8 THEN 'NULL'
    ELSE students.name
    END,
    grades.grade, students.marks
    FROM students, grades 
-- WHERE students.marks >= grades.min_mark AND students.marks <= grades.max_mark
ORDER BY grades.grade DESC, students.name

returns this:

Aamina 10 77
Amanda 10 34
Amina 10 89
Belvet 10 78
Britney 10 95
Christene 10 88
Dyana 10 55
Heraldo 10 94
Jane 10 64
Jenny 10 66
Julia 10 96
Kristeen 10 100
Meera 10 24
Paige 10 74
Priya 10 76
Priyanka 10 77
Salma 10 81
Samantha 10 87
Scarlet 10 80
Stuart 10 99
Vivek 10 84
Aamina 9 77
Amanda 9 34
Amina 9 89
Belvet 9 78
Britney 9 95
Christene 9 88
Dyana 9 55
Heraldo 9 94
Jane 9 64
Jenny 9 66
Julia 9 96
Kristeen 9 100
Meera 9 24
Paige 9 74
Priya 9 76
Priyanka 9 77
Salma 9 81
Samantha 9 87
Scarlet 9 80
Stuart 9 99
Vivek 9 84
Aamina 8 77
Amanda 8 34
Amina 8 89
Belvet 8 78
Britney 8 95
Christene 8 88
Dyana 8 55
Heraldo 8 94
Jane 8 64
Jenny 8 66
Julia 8 96
Kristeen 8 100
Meera 8 24
Paige 8 74
Priya 8 76
Priyanka 8 77
Salma 8 81
Samantha 8 87
Scarlet 8 80
Stuart 8 99
Vivek 8 84
NULL 7 77
NULL 7 34
NULL 7 89
NULL 7 78
NULL 7 95
NULL 7 88
NULL 7 55
NULL 7 94
NULL 7 64
NULL 7 66
NULL 7 96
NULL 7 100
NULL 7 24
NULL 7 74
NULL 7 76
NULL 7 77
NULL 7 81
NULL 7 87
NULL 7 80
NULL 7 99
NULL 7 84
NULL 6 77
NULL 6 34
NULL 6 89
NULL 6 78
NULL 6 95
NULL 6 88
NULL 6 55
NULL 6 94
NULL 6 64
NULL 6 66
NULL 6 96
NULL 6 100
NULL 6 24
NULL 6 74
NULL 6 76
NULL 6 77
NULL 6 81
NULL 6 87
NULL 6 80
NULL 6 99
NULL 6 84
NULL 5 77
NULL 5 34
NULL 5 89
NULL 5 78
NULL 5 95
NULL 5 88
NULL 5 55
NULL 5 94
NULL 5 64
NULL 5 66
NULL 5 96
NULL 5 100
NULL 5 24
NULL 5 74
NULL 5 76
NULL 5 77
NULL 5 81
NULL 5 87
NULL 5 80
NULL 5 99
NULL 5 84
NULL 4 77
NULL 4 34
NULL 4 89
NULL 4 78
NULL 4 95
NULL 4 88
NULL 4 55
NULL 4 94
NULL 4 64
NULL 4 66
NULL 4 96
NULL 4 100
NULL 4 24
NULL 4 74
NULL 4 76
NULL 4 77
NULL 4 81
NULL 4 87
NULL 4 80
NULL 4 99
NULL 4 84
NULL 3 77
NULL 3 34
NULL 3 89
NULL 3 78
NULL 3 95
NULL 3 88
NULL 3 55
NULL 3 94
NULL 3 64
NULL 3 66
NULL 3 96
NULL 3 100
NULL 3 24
NULL 3 74
NULL 3 76
NULL 3 77
NULL 3 81
NULL 3 87
NULL 3 80
NULL 3 99
NULL 3 84
NULL 2 77
NULL 2 34
NULL 2 89
NULL 2 78
NULL 2 95
NULL 2 88
NULL 2 55
NULL 2 94
NULL 2 64
NULL 2 66
NULL 2 96
NULL 2 100
NULL 2 24
NULL 2 74
NULL 2 76
NULL 2 77
NULL 2 81
NULL 2 87
NULL 2 80
NULL 2 99
NULL 2 84
NULL 1 77
NULL 1 34
NULL 1 89
NULL 1 78
NULL 1 95
NULL 1 88
NULL 1 55
NULL 1 94
NULL 1 64
NULL 1 66
NULL 1 96
NULL 1 100
NULL 1 24
NULL 1 74
NULL 1 76
NULL 1 77
NULL 1 81
NULL 1 87
NULL 1 80
NULL 1 99
NULL 1 84

Therefore, by adding a WHERE clause for the students.marks >= grades.min_mark AND students.marks <= grades.max_mark, you’re asking MySQL to provide essentially a unique value since there should only be one mark that falls in-between the MIN and MAX set of marks available to that student by a particular grade. After that you’ll have one name per grade which should be their highest mark in a particular grade (which is ultimately, what you want).

Once this is complete, you can add the double ORDER BY, and you are set!

Conclusion

Overall, great question overall to combine a Basic Join and a little bit of CASE work. Where I was stumped personally is there’s no actual JOINING KEY, and you’re actually just RIGHT joining the table together based off the WHERE clause.

Final result should be this:

Britney 10 95
Heraldo 10 94
Julia 10 96
Kristeen 10 100
Stuart 10 99
Amina 9 89
Christene 9 88
Salma 9 81
Samantha 9 87
Scarlet 9 80
Vivek 9 84
Aamina 8 77
Belvet 8 78
Paige 8 74
Priya 8 76
Priyanka 8 77
NULL 7 64
NULL 7 66
NULL 6 55
NULL 4 34
NULL 3 24

Feel free to check out my articles on related technologies:

Leave a Comment

Your email address will not be published. Required fields are marked *