TransWikia.com

SQL Notes: Hackerrank The Report Query how to save the subquery variable

Stack Overflow Asked by Varad Paralikar on December 21, 2020

Problem:(Link [https://www.hackerrank.com/challenges/the-report/problem?h_r=next-challenge&h_v=zen][1])

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the min mark and max mark based on which grades are assigned.

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 (8-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 grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

My Query which passes the testcase but its redundant , I Want to save the subquery variable

SELECT 
    IF( (SELECT G.GRADE FROM GRADES AS G 
        WHERE S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK) < 8 , "NULL",S.NAME)
    ,(SELECT G.GRADE FROM GRADES AS G 
          WHERE S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK)
    , S.MARKS 
FROM STUDENTS AS S 
ORDER BY (SELECT G.GRADE FROM GRADES AS G 
          WHERE S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK) DESC
          , S.NAME;

3 Answers

For SQL Server:

SELECT 
(CASE WHEN G.Grade >7 THEN S.Name ELSE NULL END) AS NAME, 
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, S.NAME

Correct answer by Adrita Sharma on December 21, 2020

SELECT IF(GRADE <8,'NULL',NAME), GRADE, MARKS 
FROM 
    (SELECT G.GRADE as GRADE, NAME, MARKS 
    FROM 
     GRADES AS G, STUDENTS AS S WHERE MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
    )SUB
ORDER BY GRADE DESC, NAME ASC, MARKS ASC;

Answered by PARVEEN KUMAR on December 21, 2020

You can use sessions variables like this:

Set @mark := (SELECT G.GRADE FROM GRADES AS G  WHERE S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK);
SELECT 
    IF( @mark < 8 , "NULL",S.NAME)
        ,@mark sortvalue
        , S.MARKS 
FROM STUDENTS AS S 
ORDER BY sortvalue DESC
          , S.NAME;

Answered by nbk on December 21, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP