Publish Date - November 2nd, 2022
|Last Modified - March 7th, 2023
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 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
NotePrint “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)
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.nameFirstly, 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.namereturns 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 84Therefore, 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 24Feel free to check out my articles on related technologies:
Hi, sorry I think theres is a problem in the solution. I found this kind of explanation in other website, but I think it is not correct because the null names are not correctly ordered as requested ( ..if there is more then one student with the same gradevole (1-7) assigned to them,order those particular studenti by their marks in ascendind order.)
Hey Gabriele,
I reference a number of suggestions in the article. Please try THIS code below (under “The solution” in my article).
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 I just validated this in Hackerrank and it works.
what about the condition to order it by marks if grade between 1 to 7
this isnt included in solution
Hey Neha,
You don’t need to do that because you can; WHEN grades.grade < 8 THEN 'NULL' ELSE students.name END which gives you grades from 1 to 7 and then you do; ORDER BY grades.grade DESC.