Hacker rank| SQL(Medium)|The Report| By Kiruthicka GP

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

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:

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

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

SOLUTION

SELECT 
    CASE 
        WHEN Grades.Grade < 8 THEN 'NULL' 
        ELSE Students.Name 
    END AS StudentName,
    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;

EXPLANATION

1.SELECT Clause:

• The CASE statement is used to conditionally select the value for the column named StudentName. If the grade in the Grades table is less than 8, it returns the string 'NULL'; otherwise, it returns the name from the Students table.
• The Grades.Grade column is selected as is.
• The Students.Marks column is selected as is.

2.FROM Clause:

• Data is selected from two tables, Students and Grades. This is done using a comma (,) which is an older syntax for joining tables (implicit join). This is equivalent to an INNER JOIN based on the WHERE clause condition.

3.WHERE Clause:

• Rows are filtered based on the condition that Students.Marks should be greater than or equal to Grades.Min_mark and less than or equal to Grades.Max_mark. This condition is used to match students with the appropriate grade range.

4.ORDER BY Clause:

• The result set is ordered first by Grades.Grade in descending order (DESC), and then by Students.Name in ascending order (the default order, which is ASC).

So, in summary, the code retrieves information about students and their grades based on the specified conditions, and then orders the result set first by grade in descending order and then by student name in ascending order. The CASE statement is used to conditionally determine the value of the StudentName column based on the grade. Note that the string 'NULL' is used for display purposes, but it will not actually represent a null value in the result set.

I want to express my gratitude for dedicating your time to read my blog.

For additional updates,Follow my GitHub and LinkedIn accounts