The Report Hacker Rank Solution- SQL | Joins
In this post we are going to discuss The Report Hacker rank Solution using Joins.
So the Question is as follows:
You are given two tables: Students and Grades. Students contain three columns ID, Name and Marks.
Grades contains the following data:
Task:
The task is to generate a report containing three columns,Name,Grade,Mark.
Students who got grades less than 8 should not be included.
The grades should be in descending order and if grades are the same then arrange them as per their Names.
Use NULL as "Name" for students who got Grades less than 8 .
Students Table Schema:
Grades Table Schema:
Understanding the Tables And Task:
The Student table contains information like the Name,Marks obtained and Id,whereas the Grades table contains the Grade allocated for the respective range of marks.
The task is to display the names of the students in decreasing order of their grades.
As You can see the Grades and Marks are in two different tables so it should be clicked that we must use joins or something else to combine the columns of the tables in order to fetch the results.
So we will use joins to combine the tables and then order them as per their grades.
That is it, so let's start.
We will use 2 ways to solve the query, nothing much is different in the query but the first one is the solution that we will get easily by thinking and the latter is a more elegant way to solve the question.
The Report Hackerrank Solution 1:
select if(g.Grade<8,"NULL",s.Name),g.Grade,s.Marks from Students s
join Grades g
on s.Marks<=g.Max_Mark and s.Marks>=g.Min_Mark
order by g.Grade desc,s.Name
Explanation:
As we need to fetch the Names of students who got grades more than 8 and NULL for the rest of the students so we use IF condition to check that the Grade is greater than 8 or not if it is greater than we display the Name else we display NULL.
Rest we fetch the Grade from Grades table and Marks from Students table.
we join both the tables and since there is no common column in them but we can join them on the basis of the Range of the Marks, so we join them on that basis.
Finally we combine the results and order them by their grades in decreasing order and if it is the same we order them by the Names.
The Report Hackerrank Solution 2:
select if(Grade<8,"NULL",Name),Grade,Marks
from Students
join Grades
on Marks between Min_Mark and Max_Mark
order by Grade desc,Name
Explanation:
We use the same if condition to select the Name or NULL but in this case we didn't use Alias .
We join the tables and this time use use between to join them ,where as we used greater than (>=) and less than(<=) in previous solution.
Finally we order them by the Grades.
Output:
Please Let me Know, If you have any doubts.
Please Let me Know, If you have any doubts.