SQL Challenge: Sorting Students by Grades

The Task

You are given two tables: Students and Grades. Students contain 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

Here is the table with the added grade information based on the given marks:

The grades are determined based on the following criteria:

• 0-9: Grade 1
• 10-19: Grade 2
• 20-29: Grade 3
• 30-39: Grade 4
• 40-49: Grade 5
• 50-59: Grade 6
• 60-69: Grade 7
• 70-79: Grade 8
• 80-89: Grade 9
• 90-100: Grade 10

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

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

Let’s solve the problem in MySQL

To generate the report as per the given requirements, you can use the following SQL query:

SELECT IF(Grade < 8, NULL, Name), 
       IF(Grade < 8, Grade, Grade), 
       IF(Grade < 8, Marks, Marks)
FROM (
  SELECT s.Name, 
         CASE 
           WHEN g.Min_Mark IS NULL THEN 1
           ELSE g.Grade 
         END AS Grade, 
         s.Marks
  FROM Students s
  LEFT JOIN Grades g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
) AS sub
ORDER BY IF(Grade < 8, Grade, Grade) DESC,
         IF(Grade < 8, Marks, Name);

This query works as follows:

1. It joins the Students table with the Grades table based on the condition that the student’s marks fall within the range specified for each grade.
2. It assigns a grade to each student based on their marks.
3. It uses the IF function to handle cases where the grade is less than 8. If the grade is less than 8, it replaces the name with “NULL” and uses the grade as is. Otherwise, it keeps the name and grade unchanged.
4. Finally, it orders the result set first by grade in descending order and then by marks (or name if the grade is less than 8) in ascending order.

Let’s break down the SQL query step by step, making it beginner-friendly:

Step 1: Joining the Tables

SELECT s.Name, 
       CASE 
         WHEN g.Min_Mark IS NULL THEN 1
         ELSE g.Grade 
       END AS Grade, 
       s.Marks
FROM Students s
LEFT JOIN Grades g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark

In this part, we’re combining the information from two tables: Students and Grades. We’re selecting three columns: Name from the Students table, Marks from the Students table, and a calculated column called Grade.

The Grade column is determined by checking the marks of each student against the ranges specified in the Grades table. If a student’s marks fall within a specific range, they’re assigned the corresponding grade. If their marks don’t fit into any range (which would mean that the Grades table doesn’t have an entry for their marks), we assign them Grade 1.

Step 2: Handling Grades Less Than 8

IF(Grade < 8, NULL, Name), 
IF(Grade < 8, Grade, Grade), 
IF(Grade < 8, Marks, Marks)

Here, we’re using the IF function to handle cases where the grade is less than 8. If the grade is less than 8, we replace the student’s name with “NULL” and keep the grade and marks as they are. If the grade is 8 or higher, we keep the name, grade, and marks unchanged.

Step 3: Ordering the Results

ORDER BY IF(Grade < 8, Grade, Grade) DESC,
         IF(Grade < 8, Marks, Name)

Finally, we’re ordering the results. We first order by grade in descending order (DESC), which means higher grades come first. If the grade is less than 8, we use the grade itself for ordering. If the grade is 8 or higher, we use the student’s marks (or name if the grade is less than 8) for ordering in ascending order (default behavior). This ensures that students with higher grades come first, and within the same grade, students are ordered by their marks or names as specified.