AnswerBun.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!

Related Questions

Get to deeply nested object by array path of ids

3  Asked on November 7, 2021 by rgc998

 

How to consume dynamically created rabbit queues?

1  Asked on November 7, 2021 by laaf

   

Email draft – add recipients -Powershell

1  Asked on November 7, 2021 by konstantinos-v

   

Repo sync, fetches local credentials

0  Asked on November 7, 2021 by lecerof

     

SCSS generate classes from two arrays

1  Asked on November 7, 2021 by jekie

 

How to read the pem file in nodejs?

2  Asked on November 7, 2021 by raghul-sk

   

Singly linked List Find max,min

2  Asked on November 4, 2021 by lizzar_00

     

Deserializing json C# unexpected character encountered

1  Asked on November 4, 2021 by kjenn31

       

MySQL: Adding Calculated Column with IF ELSEIF

1  Asked on November 4, 2021 by thomasnngooglemailcom

     

Spring – Bean executes method twice

1  Asked on November 4, 2021 by bishonen_pl

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP