easy understandable solution

with need as ( SELECT name,case when marks>=90 then 10 when marks>=80 and marks <90 then 9 when marks>=70 and marks<80 then 8 when marks>=60 and marks<70 then 7 when marks>=50 and marks <60 then 6 when marks>=40 and marks<50 then 5 when marks>=30 and marks<40 then 4 when marks>=20 and marks <30 then 3 when marks>=10 and marks<20 then 2 when marks>=0 and marks<10 then 1 end as rnk,marks from students order by 2 ) ,need2 as ( select rnk,marks,name from need where rnk>7 order by 3,2 desc)

select distinct n2.name,n1.rnk,n1.marks from need n1 left join need2 n2 using(rnk,marks) order by 2 desc,1

SELECT IF(g.grade < 8, NULL, s.name), g.grade, s.marks FROM students s LEFT 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 END ASC, CASE WHEN g.grade < 8 THEN s.marks END ASC;

SELECT NUllif(s.name,g.grade > 7), g.grade, s.marks from students s left JOIN grades g on s.marks BETWEEN g.MIN_MARK and g.MAX_MARK ORDER by g.grade desc, s.name asc;

select case when grade>=8 then name else 'NULL' END ,grade,marks from Students left join Grades on marks between min_mark and max_mark order by grade desc,name asc;

and by what exctly you want me to join them?