grade assignment sql query hackerrank

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

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)

Cookie support is required to access HackerRank

Seems like cookies are disabled on this browser, please enable them to open this website

HackerRank – SQL – Basic Join – The Report – Solution

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!

Table of Contents

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

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

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 .

returns this:

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!

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:

Feel free to check out my articles on related technologies:

Best Web Development Courses on Udemy
Best Python courses on Udemy
Best Digital marketing udemy course

4 thoughts on “HackerRank – SQL – Basic Join – The Report – Solution”

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 = 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

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.