write a query to this..?

Question

Students and Grades. Students contains three columns ID, Name and Marks, Grades-(grade,min_mark,max_mark)

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 (1-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 marks in ascending order.

Write a query to help Eve.

Note Print "NULL" as the name if the grade is less than 8.

I have written this query :

select name,grade,marks from students s join grades g on marks between min_mark and max_mark
where grade>7
order by 2 desc,name

select translate(name,name,'NULL'),grade,marks from students s join grades g on marks between min_mark and max_mark
where grade<=7
order by 3 desc

but don't know how to join these both queries.

Answer 1

Probably something like this:

SELECT CASE WHEN g.grade < 8 THEN NULL ELSE s.name END as name,
    g.grade, s.marks 
FROM students AS s 
INNER JOIN grades AS g 
ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY g.grade DESC, s.name;

Answer 2

You can also try the below SQL Query:

SELECT CASE WHEN (STUDENTS.MARKS < 70) THEN 'NULL' ELSE STUDENTS.NAME END,
        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 ASC;

OR

SELECT CASE WHEN (STUDENTS.MARKS < 70) THEN 'NULL' ELSE STUDENTS.NAME END,
        GRADES.GRADE, STUDENTS.MARKS
FROM STUDENTS, GRADES
WHERE STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
ORDER BY GRADES.GRADE DESC, STUDENTS.NAME ASC;

Answer 3

Tried and tested code on hackerrank...

select s.name, g.grade, s.marks from students s, grades g where g.grade>=8 and s.marks between g.min_mark and g.max_mark order by g.grade desc, s.name;

select 'NULL', g.grade, s.marks from students s, grades g where g.grade<8 and s.marks between g.min_mark and g.max_mark order by g.grade desc, s.marks;

Answer 4

SELECT CASE WHEN Grades.Grade < 8 THEN NULL ELSE Students.Name END as name,
   Grades.Grade, Students.Marks 
FROM Students INNER JOIN Grades 
ON Students.Marks BETWEEN Grades.Min_mark AND Grades.Max_mark
ORDER BY Grades.Grade DESC, Students.Name;

Answer 5

select case
when grade < 8 then NULL
else
name
end
as name, grade, marks
from students, grades
where marks between min_mark and max_mark
order by grade desc, name asc, marks asc;

Answer 6

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

Answer 7

that my answer but he is not perfect: maybe from that, you can solve

grades.grade, students.marks,
case
    when grades.grade < 8 then "null"
    else students.name
    end as NNN
from students
inner join grades on students.marks between grades.min_mark and grades.max_mark
order by grades.grade desc;

Answer 8

This worked for me in MySQL -

select if(Grade < 8, NULL , Name), Grade, Marks
from Students
Inner Join Grades
where Marks between Min_Mark AND Max_Mark
ORDER BY GRADE DESC, Name ASC;

Answer 9

This answer is not as elegant as previous ones, but is how I hacked together a solution. After seeing the solutions here, I would recommend using the JOIN, ON, and BETWEEN statements instead of my solution where I used the left most character and add one to align it with the values I saw in the second table. If you see any flaws in this outside what I mentioned above, please comment so I can improve.

SELECT 
CASE 
    WHEN LEFT(MARKS, 1) + 1 < 8 AND MARKS != 100 THEN NULL
    ELSE NAME
END AS NAME, 
CASE 
    WHEN MARKS = 100 THEN 10
    ELSE LEFT(MARKS, 1) + 1
END AS GRADE, MARKS 
FROM STUDENTS
ORDER BY GRADE DESC,
(CASE WHEN GRADE >= 8 THEN NAME END),
(CASE WHEN GRADE < 8 THEN MARKS END) ASC

Answer 10

Following code worked for me:

  Select name, grade, marks from (
      Select *, 1 as filter, row_number() OVER(order by grade desc, name asc) as rowNumb from (
        Select name, grade, marks from (
          Select s.*,g.grade as grade from students s
          join Grades g 
          On s.marks>=g.Min_mark AND s.marks<=g.Max_mark
          ) tmp where grade>7 
      ) t1 
      UNION
      Select *, 2 as filter, row_number() OVER(order by grade desc, marks asc) as rowNumb from (
        Select NULL as name, grade, marks from (
          Select s.*,g.grade as grade from students s
          join Grades g 
          On s.marks>=g.Min_mark AND s.marks<=g.Max_mark
        ) tmp where grade<=7 
      ) t2 order by filter asc, rowNumb asc 
    ) t3;

This problem is in two parts so i'm calculating each part individually and then uniting them.

t1 & t2 contain data for respective grade>7 & grade<=7 students and each has their respective ordering logic.

To maintain the order after union I've used column like row_num and a table filter column to simply append one table after other without mixups of any order within the tables and outside them.

Answer 11

SELECT 

  CASE 
  WHEN marks >= 70 THEN name
  ELSE NULL
  END AS req_name,
 
  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,
 
  marks
 
FROM students
ORDER BY grade DESC, req_name, marks ASC