The Report HackerRank Solution | Joins | Codeityweb

The Report - SQL | Joins | HackerRank Solution 

In this post we are going to discuss The Report Hackerrank Solution using Joins.

So the Question is as follows:
You are given two tables: Students and Grades. Students contains 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.
• Student who got grades less than 8 should not be included.
• The grades should be in descending order and if grades are same then arrange them as per their Names.
• Use NULL as "Name" for students who got Grades less then 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 lets 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 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 

 

Explaination:

• As we need to fetch the Names of students who got grades more than 8 and NULL for 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 their 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 result and order them by their grades in decreasing order and if it is 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

Explaination:

• 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:

Thank You for Reading!For more posts like this do explore  the site.

Related posts:

• Top Competitors - HackerRank Solution
• Revising the Select Query 1- Hackerrank solution 
• Revising the Select Query II - Hackerrank Solution
• Pairs - Hackerrank Solution
• Placements- Hackerrank solution