We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Practice
  2. SQL
  3. Basic Join
  4. The Report
  5. Discussions

The Report

Sort 1051 Discussions, By:

votes

  • 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

  • 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

  • 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 :)

Load more conversations