Hi and warm greetings to all. I am a fresher in Oracle SQL development and am working on my querying skills by solving problems on HackerRank. I am stuck with this problem. As evident, there are two different tables, Students and Grades. The first one has columns; ID, Name and Marks while the second one has columns like Grade, Min_Marks and Max_Marks (Which signify the boundary conditions for assigning the grades inclusive of the two ends.)
Now I am to create a report that has the Name of the students (Students.Name), the grades they obtained (Grades.Grade) and their Marks (Students.Marks) such that they are reported in the order of their grades, beginning with the best ones (10) to the worst performers (which is 3 I guess!) and for students with the same grades I have order them alphabetically. Another condition is that for students with grade lower than 8 their Names have to be replaced by NULL and if there are students with similar grades between the 1 to 7 range, I have to order them by their Marks in ascending order.
This is the code that I have written to execute this query:
SELECT
CASE
WHEN Grades.Grade < 8 THEN 'NULL'
WHEN Grades.Grade >=8 THEN Students.Name
END
, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY
(
CASE Students.Name
WHEN 'NULL' THEN Grades.Grade DESC, Students.Marks
ELSE Grades.Grade DESC, Students.Name
END
);
I am getting the error: ORA-00905 Missing Keywords which is referring to the WHEN 'NULL' THEN Grades.Grade DESC, Students.Marks statement. I have tried out some other ways too, I am just going to add those codes. Kindly educate me why this error is coming and how to mitigate it?
Thanks in Advance!
Trial 1
SELECT
CASE
WHEN Grades.Grade < 8 THEN 'NULL'
WHEN Grades.Grade >=8 THEN Students.Name
END
, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY
(
CASE
WHEN Students.Name = 'NULL' THEN Grades.Grade DESC, Students.Marks
WHEN Students.Name != 'NULL' THEN Grades.Grade DESC, Students.Name
END
);
Trial 2
SELECT
CASE
WHEN Grades.Grade < 8 THEN 'NULL'
WHEN Grades.Grade >=8 THEN Students.Name
END
, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY
(
CASE
WHEN Students.Name = 'NULL' THEN Grades.Grade DESC, Students.Marks
WHEN Students.Name <> 'NULL' THEN Grades.Grade DESC, Students.Name
END
);
Trial 3
SELECT
CASE
WHEN Grades.Grade < 8 THEN 'NULL'
WHEN Grades.Grade >=8 THEN Students.Name
END
, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY
(
CASE
WHEN Students.Name = 'NULL' THEN Grades.Grade DESC, Students.Marks
WHEN Students.Name != 'NULL' THEN Grades.Grade DESC, Students.Name
END CASE
);