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.
  • Practice
  • Certification
  • Compete
  • Career Fair
  • Hiring developers?
  1. Practice
  2. SQL
  3. Basic Join
  4. The Report
  5. Discussions

The Report

Problem
Submissions
Leaderboard
Discussions

Sort 1127 Discussions, By:

votes

Please Login in order to post a comment

  • jianyliu 4 years ago+ 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
    
    140|
    Permalink
  • jplanow 4 years ago+ 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).

    125|
    Permalink
  • o1bloody1o 4 years ago+ 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;
    
    113|
    Permalink
  • TingChiehHuang 5 years ago+ 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

    20|
    Permalink
  • marinskiy 3 years ago+ 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 :)

    13|
    Permalink
Load more conversations

Need Help?


View top submissions
  • Contest Calendar
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy
  • Request a Feature