- Practice
- SQL
- Basic Join
- The Report
- Discussions
The Report
The Report
jianyliu + 0 comments Simple MySQL solution
SELECT IF(GRADE < 8, NULL, NAME), GRADE, MARKS FROM STUDENTS JOIN GRADES WHERE MARKS BETWEEN MIN_MARK AND MAX_MARK ORDER BY GRADE DESC, NAME
jplanow + 0 comments There seems to be some ambiguity in the problem statement regarding ordering. Specifically..
"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... list them by their marks in ascending order."
This last statement could easily be interpreted as, "ALL records with grade lower than 8 should be listed by their marks in ascending order. (And not in descending order by grade.)"
However, the test code is expecting these to be ordered by grade first (descending) then by mark (ascending).
o1bloody1o + 0 comments Hello so I tested out those three ways of solving this problem. Hope it will help you out.
/*Variant 1*/ SELECT Students.Name, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Max_Mark WHERE Grades.Grade > 7 ORDER BY Grades.Grade DESC, Students.Name ASC; SELECT null, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Max_Mark WHERE Grades.Grade <= 7 ORDER BY Grades.Grade DESC, Students.Marks ASC; /*Variant 2*/ SELECT IF (Grades.Grade > 7, Students.Name, NULL) AS Sname, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Max_Mark ORDER BY Grades.Grade DESC, Sname ASC, Students.Marks ASC; /* Variant 3*/ SELECT CASE WHEN Grades.Grade > 7 THEN Students.Name WHEN Grades.Grade <= 7 THEN NULL END, Grades.Grade, Students.Marks FROM Students INNER JOIN Grades ON Students.Marks BETWEEN Grades.Min_Mark AND Max_Mark ORDER BY Grades.Grade DESC, Students.Name ASC, Students.Marks ASC;
TingChiehHuang + 0 comments HERE is the easiest SQL SERVER Code I can think of:
SELECT CASE WHEN G.Grade<8 THEN NULL ELSE S.Name END AS Name, G.Grade, S.Marks FROM Students S INNER JOIN Grades G ON S.Marks BETWEEN G.Min_Mark and G.Max_Mark ORDER BY G.Grade DESC,Name,Marks
marinskiy + 0 comments Here is Oracle solution from my HackerrankPractice repository:
SELECT CASE WHEN G.Grade > 7 THEN S.Name ELSE 'NULL' END AS NameOrNull , 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, NameOrNull ASC, S.Marks ASC;
Feel free to ask if you have any questions :)
Sort 1127 Discussions, By:
Please Login in order to post a comment