SET NOCOUNT ON;

SELECT CASE WHEN g.Grade < 8 THEN 'NULL' ELSE s.Name END AS Name, g.Grade, s.Marks FROM Students s JOIN Grades g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark ORDER BY g.Grade DESC, CASE WHEN g.Grade >= 8 THEN s.Name ELSE NULL END asc, CASE WHEN g.Grade < 8 THEN s.marks ELSE NULL END asc; go

Since there's no ID to join the tables directly, we’re using Marks to join the table and match each student to a grade range. See line: ON Students.Marks BETWEEN Grades.MinMark AND Grades.MaxMark. I used to think you could only join tables using IDs, but this example shows you can also join using value-based conditions, like a range. Thought this might help others who are new to SQL like myself!

SELECT IF(Grades.Grade < 8, NULL, Students.Name) AS Name, Grades.Grade, Students.Marks FROM Students JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY Grades.Grade DESC, Students.Name, Students.Marks;

1. First, match every student’s marks to their grade using a JOIN.

2. If the grade is 8 or higher, show the student’s name. Otherwise, show NULL.

3. For sorting:

Put higher grades first.

Within high grades (≥ 8), sort students alphabetically by name.

Within low grades (< 8), sort students by marks in ascending order.

SELECT CASE WHEN g.Grade < 8 THEN NULL ELSE s.Name END AS Name, g.Grade, s.Marks FROM Students s JOIN Grades g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark ORDER BY g.Grade DESC, CASE WHEN g.Grade >= 8 THEN s.Name ELSE s.Marks END;

can someone tell me what's wrong with my query-`

with sheet as 
(select name,marks,
    case 
        when marks between 0 and 9 then 1
        when marks between 10 and 19 then 2
        when marks between 20 and 29 then 3
        when marks between 30 and 39 then 4
        when marks between 40 and 49 then 5
        when marks between 50 and 59 then 6
        when marks between 60 and 69 then 7
        when marks between 70 and 79 then 8
        when marks between 80 and 89 then 9
        when marks between 90 and 100 then 10
    end as grade
  from students) 

select 
    case when sheet.grade<8 then NULL else sheet.name end as NAME,
    sheet.grade as GRADE,
    sheet.marks as MARKS
from sheet
order by GRADE desc,
 case when GRADE>=8 then NAME else null
      END ASC, 
 case when GRADE<8 then MARKS else null
      END desc;

Without Using Join

SELECT CASE WHEN Marks >= 70 THEN Name ELSE 'NULL' END , (SELECT GRADE FROM GRADES WHERE Marks BETWEEN Min_Mark AND Max_Mark ) AS gr, Marks FROM Students ORDER BY gr DESC , Name

go