jplanow 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).
ajaych Agree! Author of this question should fix it to save everyone's time -:)
tvaishnav Agree - not clearly worded.
mjjohns1 agree, and would add this question is DUMB. come up with something more realistic and useful.
manikantareddy_2 select case when grade<8 then 'NULL' else s.name end,g.grade,s.marks from students s,grades g where s.marks between g.min_mark and g.max_mark order by grade desc,s.name;
Torabchotu Thanks bro
kaikumar30 Hi @manikantareddy_2.. Your code works!.. Can you explain this portion of your code "select case when grade<8 then 'NULL' else s.name end,"... This NULL will be assigned to which column.. and after s.name what does the 'End' do?...
RaviPushkar Hello @kaikumar30, let me reframe that above query for you:
SELECT (CASE g.grade>=8 WHEN TRUE THEN s.name ELSE null END),g.grade,s.marks FROM students s INNER JOIN grades g ON s.marks BETWEEN min_mark AND max_mark ORDER BY g.grade DESC,s.name,s.marks;
In MySql, CASE statement acts like a "if" condition, so if the expression after CASE statement matches the boolean value after WHEN clause, it executes the result after THEN clause; otherwise it will execute result after ELSE clause.
melo_holland Why the "ON s.marks BETWEEN min_mark AND max_mark" line? Could you explain please.
RaviPushkar We have to determine the grade of the student based on the scored marks. So to achieve that, we need to consider the range of marks for a particular grade which is defined in the grades table as min_mark and max_mark.
By "ON s.marks BETWEEN min_mark AND max_mark" we are defining the range of the marks for a particular grade and performing the join operation.
salah_atabani I guess a more accurate one would be (I'm using Oracle):
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from (select name, grade, marks from students, grades where marks between min_Mark and Max_Mark) order by grade desc, name, decode(name,NULL,marks);
in this case, if two students have the same grade and name and their name is not null they won't be ordered by their marks.
Bhavesh_Infosys what is the use of using decode
salah_atabani Using decode, you order by marks only when the name is NULL.
pukkinming that's genius man
sviswan9 good one. i just did "Order by grade desc, name asc, marks asc" because there was not rule tha tmarks should be be sorted ascending otherwise so you do not really need the Decode rule. I used a temp teable to store the internal results first
adityakumar19951 ERROR at line 1: ORA-00905: missing keyword ERROR IS THERE
tasfiahm can you please explain "where marks between min_Mark and Max_Mark" this part
salmanhaque07 Here is my MYSQL Soln:
select if(grades.grade<8,concat("NULL"),students.name),grades.grade,students.marks from students inner join grades on(students.marks>=grades.min_mark and students.marks<=grades.max_mark ) group by grades.grade desc, students.name,students.marks desc;
the query say that students marks greater than or eql minimum mark and less than or eql ...see the grade table then think about input and output ....thats make u clear ...Happy Coding!!!tasfiahm Thanks
rupalvshinde this will also work for mysql
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from students,grades where marks between min_Mark and Max_Mark order by grade desc, name, coalesce(name,marks);
ashu7566sri @rupalvshinde the coalesce in the end might not work as both the column name and marks have different data type.
Szarka98 Thanks for the solution. What I learnt: '>' != '>=' :)
sauce_chili isn't it should be marks asc? because
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
shashibarnwaldav there is no need to give orderby name 2 times one outside an other inside coalesce
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from students,grades where marks between min_Mark and Max_Mark order by grade desc, coalesce(name,marks);
ketakrs don't get confuse if you don't see an 'asc' keyword in many above soln. here r 2 query both identical and do same work
select (case when g.grade > 7 then s.name else Null end), g.grade, s.marks from students s, grades g where s.marks between g.min_mark and g.max_mark order by g.grade desc, s.name, s.marks asc
select (case when g.grade > 7 then s.name else Null end), g.grade, s.marks from students s, grades g where s.marks between g.min_mark and g.max_mark order by g.grade desc, s.name, s.marks
chenming0801 I made some changes, I thint this is more clear. The only issue with this question is the order by. Thx for explaining decode.
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from students,grades where marks between min_Mark and Max_Mark order by grade desc, name, decode(name,NULL,marks);
itstejal99 What is the use of Decode function ?
select (case when grade >=8 then name else null end) name, grade, marks from (select s.name, g.grade, s.marks from students s join grades g on s.marks between g.min_Mark and g.Max_Mark) order by grade desc, name;
without it query working fine ..
ayan_tugsel for those using mysql, instead of using decode, I used; ...order by grade desc, name, ifnull(name, marks)
Btw great code, thanks a lot.
superstj I didn't know that we don't need to join tables to use information from these tables simutenously.
bamdalerishikesh You're supposed to use Join
Oluwole Nice explanation , i learnt a lot from your technique.
diegomsaiz Thank you very much indeed for your MySQL code!! I have a lot to learn about ALIASES. Kind regards!!
shubh_nitd WHAT CHANGES DO WE NEED TO DO ON IT TO GET ACCEPTED IN ORACLE?
sakshamarora97 For both cases your order by statement is the same.. As in when grades<8 then you order by grade, then name and then marks. However the question states to order by grade, then marks, when grades<8 And in the next case it states to order by grade and then by name. I guess its due to the clarity Here's my code:
select s.name, g.grade, s.marks from students s left join grades g on s.marks between g.min_mark and g.max_mark where g.grade>=8 order by g.grade desc, s.name asc;
select NULL, g.grade, s.marks from students s left join grades g on s.marks between g.min_mark and g.max_mark where g.grade<8 order by g.grade desc, s.marks asc;
vishwathvice [deleted]vishwathvice Does the inner join between work in PostGres and Oracle?
tripathysagnik3 can i use case , when , then in oracle???
cassiofoliveira yeap
aradhyavats in the output for this query the marks for null are not arranged in ascending order
mikelkengni amazing. thanks for sharing your work. i learned a lot from it.
krauszraphael MySQL kindly explain this - https://dev.mysql.com/doc/refman/5.7/en/case.html
So, you can use in a SELECT to modify what is displayed.
salmanhaque07 Hi!! @kaikumar30 the problem say to us that if the grade less 8 some student, then take place name of student as NULL of these student .See the output below that make you clear:
output: Britney 10 95 Heraldo 10 94 Julia 10 96 Kristeen 10 100 Stuart 10 99 Amina 9 89 Christene 9 88 Salma 9 81 Samantha 9 87 Scarlet 9 80 Vivek 9 84 Aamina 8 77 Belvet 8 78 Paige 8 74 Priya 8 76 Priyanka 8 77 NULL 7 64 NULL 7 66 NULL 6 55 NULL 4 34 NULL 3 24
RaBaSa END clause will close the case statement.
select case when grade<8 then 'NULL' else s.name end NULL will be assigned to those columns which fail the condition grade<8 i.e. students with grades less than 8.
kaikumar30 Thanks @manikantareddy_2
raku07 but order by doesnot account for names of null value and it didnot sort marks of null students in ascending order according to your query
alexpat if(..., ..., ...) statement will be better, i think
select if(grade > 7, name, null), grade, marks from students, grades where marks between min_mark and max_mark order by grade desc, name
athithyaaselvam Hey alex, whats the use of this statement " where marks between min_mark and max_mark " ?
sauce_chili it will give us a result of grade if student marks are between in the min_mark and max_mark
basdeo_nevada This works in MySQL but not in Oracle, does anybody know why?
select if(grades.grade > 7, students.name, null), grades.grade, students.marks from students, grades where marks between min_mark and max_mark order by grade desc, name;
xinwzh it does not enforce ordering by makrs when there is a tie for grade < 8.
AshaKumar Excellent...I learnt my mistake too. Thanks a lot.
eneeskalkan Is it neccessary to add 'where s.marks between g.min_mark and g.max_mark' ? If we don't add, query will show same result but system don't accept that.
arunimarai31 Thanks @manikantareddy_2. Can you explain why there is no need of a join function?
dave_shubham_2 This is pretty realistic for basic school reports. At least the underlying report generation logic works the same way with such conditions in the question. So, not DUMB really. Just saying.
ATMaher Thank you! I was stuck on this problem for ages – more clarity is required.
BPricester That was my take exactly. Also, this interpretation fits the example results. Author could easily expand the example result to include lower grades which would make this clear as well.
hadzio Agree - the same observation. The ordering requirement is not clear/full.
Dino23 Ambiguity needs to be addressed. Wasted a lot of time on numerous trial and errors for understanding this.
kirakira This is exactly what it means and what I get! And turns out it is totally wrong, man did they even proof-read the statement before publishing the problem...
veekaypal If the grade of two students are less than 8 and they are equal (like if A got 55 and B got 58, both recieve the same grade 6) then list them in increasing order of their marks.
i.e.
NULL 6 55 (record of A) followed by NULL 6 58 (record of B)
So first order by grade in descending order, then by marks in ascending order if grade is less than 8.
Otherwise order by grade in descending order, and name in acending order if grade is >= 8
ravipandey420 There is no ambiguity
SELECT (CASE WHEN Grades.Grade < 8 THEN 'NULL' ELSE Students.Name END ) , Grades.Grade, Students.Marks FROM Students LEFT JOIN Grades on Students.Marks BETWEEN Grades.Min_Mark AND Grades.Max_Mark ORDER BY Grades.Grade DESC, Students.Name
ashishssharma In question it is mentioned :
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.
And you have ordered with name and it is working fine. This I think is the amiguity in the question
DavEDav Agree about the ambiguity but fortunately for me I did not SEE the ambiguity and just ignored "by their marks" in the last statement. I see a year later they have not fixed it. I guess by could be changed to beside, or else strike out "by their marks" entirely.
ngsinder my solution i hope it pretty easy to understand:
select Name= case when grade<8 then null else name end , Grade, Marks from students a inner join grades b on a.marks between b.min_mark and b.max_mark order by case when grade>=8 then grade end desc,name asc, case when grade<=7 then grade end desc,marks asc
Amply 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.
deliam_arlen OMG I WASTED SO MUCH TIME B/C OF THIS! ARG! THANK YOU, so much... sigh
o1bloody1o 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;
manasveemittal [deleted]manasveemittal why is "UNION ALL' not needed. can i follow u on stackexchange, linkedin?
sidharthdas268 why is dis ON Students.Marks BETWEEN Grades.Min_Mark AND Max_Mark used for?
yichuc These scripts are really helpful! Want to supplement: For variant 2, use IIF instead of IF when applying in MY SQL SERVER.
TingChiehHuang 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
priyanshu281 Hi Huang, even I am also using this but having wrong order when grade<8 i.e when it is NUll not able to do order it in ascending order of marks.I tried using your query as well facing same issue. I guess we have to use union. Please suggest me with this!!
pureabsolute priyanshu281, if it is having trouble with NULL and order, try converting the null into a 0.
The orderby line becomes:
order by G.Grade DESC, ISNULL(name, 0), Marks
kcuck WORKED FOR ME
SELECT Name = CASE WHEN G.Grade >7 THEN S.Name ELSE NULL END, G.Grade , S.Marks FROM Students S INNER JOIN Grades G ON S.Marks >= G.Min_Mark AND S.Marks <= G.Max_Mark ORDER BY Grade DESC,Name,Marks;
andremlsantos I can't use this script. In the name field, its printed 1, instead of the correct name. This is the output returned:
1 10 95 1 10 94 1 10 96 1 10 100 1 10 99 1 9 89 1 9 88 1 9 81 1 9 87 1 9 80 1 9 84 1 8 77 1 8 78 1 8 74 1 8 76 1 8 77 NULL 7 64 NULL 7 66 NULL 6 55 NULL 4 34 NULL 3 24
colonel_bishop in place of 'SELECT NAME = CASE' in first line, use 'SELECT CASE' because I think the former one is evaluating in 1 and 0(boolean)
anusweetyda why should we use case..? pls explain..
plj_coding It adds a conditional data value for the name column. The name of the student in the current row or NULL
pratsing23 Awesome.It worked for me too in oracle. select case when grade < 8 then 'NULL' when grade >=8 then name end as name,grade,marks from students,grades where marks between min_mark and max_mark order by grade desc,name;
shaktirajput100 [deleted]shaktirajput100 [deleted]
amitasaurus Why
ON S.Marks BETWEEN G.Min_Mark and G.Max_Mark
??
lamba92sachin this is similar to writing s.marks>=g.min_mark and s.marks <=g.max_marks.
nitinrohan19 Working perfectly
select case when grade<8 then 'NULL' else name end, grade,marks from grades,students where marks>=min_mark and marks<=max_mark order by grade desc,name,marks
MiguelsHack But there are no common fields, how can we join the two tables then?
elanmi Marks received by a student define which Grade the student gets according to inteval (Min_Marks, Max_Marks) in table Grades. Imagine the students took a placement test, and are placed into various grades according to the marks they scored on the test.
Hope that clarifies your question. I was puzzled too.
woldegebrielkeb1 this worked for me
(select Name, Grade, Marks from Students, Grades where Marks >= Min_Mark and Marks <= Max_Mark and Grade >=8 )
union
(select null, Grade, Marks from Students, Grades where Marks >= Min_Mark and Marks <= Max_Mark and Grade <8 ) order by Grade desc, Name, Marks;
jc65537 This works for me
MYSQLselect if(Grades.Grade < 8, concat('NULL'),Students.Name), Grades.Grade, Students.Marks from Students inner join Grades on Students.Marks >= Grades.Min_Mark and Students.Marks <= Grades.Max_Mark group by Grades.Grade desc, Students.Name, Students.Marks asc
OutputBritney 10 95 Heraldo 10 94 Julia 10 96 Kristeen 10 100 Stuart 10 99 Amina 9 89 Christene 9 88 Salma 9 81 Samantha 9 87 Scarlet 9 80 Vivek 9 84 Aamina 8 77 Belvet 8 78 Paige 8 74 Priya 8 76 Priyanka 8 77 NULL 7 64 NULL 7 66 NULL 6 55 NULL 4 34 NULL 3 24
ghostroot ORDER BY works as well. Why ?
yli131 ORDER BY G DESC, STUDENTS.NAME;
jpujari group by is a typo it should be order by
JackieVaswani why did you use concat()?
AshleyPhoenix to add the word "Null" if the first part of the if statment was true
Freiling No need, you can just use NULL keyword, or even 'NULL' as a string. CONCAT() is for splicing strings together.
nkuhan Simple version of the above code, since field names are different in each table, no need to put table names in query
select if(Grade < 8,NULL,Name),Grade,Marks from Students inner join Grades on Marks >= Min_Mark and Marks <= Max_Mark group by Grade desc,Name,Marks asc
mingchac What does the
on Marks >= Min_Mark and Marks <= Max_Mark
do? there are multiple in_mark and max_mark
Mr_ayushgoyal why you use group by ? shouldn't there be order by. explain me if i m wrong.
LinVenus I really appreciate that you post your idea. I learned join on between and today! So excited!!!!
shi_bin I love your solution.
nishaaishu027 can u explain how if works ....if grade < 8 it will replace name by null how will we get those name whose grade > 8 ???
ujjvalk MS SQL select case when g.grade < 8 then "null" else s.Name end,g.grade,s.Marks from Students as s join Grades as g on g.Min_Mark <= s.Marks and s.Marks <= g.Max_Mark order by g.grade desc,s.name asc;
output
Britney 10 95 Heraldo 10 94 Julia 10 96 Kristeen 10 100 Stuart 10 99 Amina 9 89 Christene 9 88 Salma 9 81 Samantha 9 87 Scarlet 9 80 Vivek 9 84 Aamina 8 77 Belvet 8 78 Paige 8 74 Priya 8 76 Priyanka 8 77 null 7 64 null 7 66 null 6 55 null 4 34 null 3 24
Compiler Message Wrong Answer
ujjvalk work if case NULL insted of null
select case when g.grade < 8 then "NULL" else s.Name end,g.grade,s.Marks from Students as s join Grades as g on g.Min_Mark <= s.Marks and s.Marks <= g.Max_Mark order by g.grade desc,s.name asc;
nick_axl I did'nt even sort by marks and got my output accepted. The dataset or the explanation needs to be better to include all cases.
select case when grades.grade <8 then NULL else students.name end, grades.grade, students.marks from students join grades on (students.marks <= grades.max_mark) and students.marks >= grades.min_mark order by grades.grade desc, students.name asc;
vivekchidrawar I am getting same output but it says wrong answer.
Parbhani22 HHi sir! mi pan Bal Vidya Mandir Cha student ahe. Mala programming madhe interest ahe ani mi B.tech Karnar ahe computer science madhe Mala ya field baddal mahiti deta ka ???
waghx013 Avoid multiple lines and use -- on students.marks between grades.min_mark and grades.max_mark
marinskiy 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 :)
chiungya Hi, I have almost exact the same code as yours but I did not pass. I could not find a reason. The answers are exactly the same. Could you help me to see which part I did wrong?
select case when g.grade >7 then s.name else 'Null' end as newname , 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, newname asc, s.marks asc;
chiungya Updates: I figured it out now. I use 'Null' not 'NULL'.
jgcorliss MS SQL Server:
SELECT CASE WHEN Grade < 8 THEN NULL ELSE Name END AS Name, Grade, Marks FROM Students, Grades WHERE Marks BETWEEN Min_Mark AND Max_Mark ORDER BY Grade DESC, Name, Marks;
jianyliu 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
qwpurdue Here is my solution that passes the test
SELECT CASE WHEN G.Grade < 8 THEN NULL ELSE S.Name END AS Name, G.Grade, S.Marks FROM STUDENTS AS S, GRADES AS G WHERE S.Marks >= G.Min_Mark AND S.Marks <= G.Max_Mark ORDER BY G.Grade DESC, S.Name ASC;
dalton_478 The way the problem is worded today this should not pass anymore. The secondary index of s.Name would fail since we are to sort the grades below 8 by the marks.
johnjackson9088 SPOILERS ALERT
SELECT CASE WHEN S.MARKS<70 THEN "NULL" ELSE S.NAME END AS NAME, M.GRADE, S.MARKS FROM STUDENTS S,GRADES M WHERE S.MARKS BETWEEN M.MIN_MARK AND M.MAX_MARK ORDER BY M.GRADE DESC,S.NAME ASC, S.MARKS ASC;
niyati_pri This worked for me
select if(grade<8,null,name) as name, grade, marks from students as s join grades as g on (s.marks between g.min_mark and g.max_mark) order by grade desc,name,marks;
AkshayKReddy What's happening here?
niyati_pri While joining the tables in the 'on' expression I used 'betwwen and' operater to get the exact grade. It return 21 records same as the total no of records available in the student table. Then used 'if' to nullify the name of the students, who got less than 8 grade. Finally sorted the result as asked. Did this answer your question?
AkshayKReddy Using the operator is fine, I was wondering how does one get the grades column with the help of this join. How does the operator map to the grades variable selected at the start?
niyati_pri If person's 'marks' in Marks table falls under a perticular range between 'Min_Mark' and 'Max_Mark' of grades table then that perticular grade will be mapped in the resultset.
AkshayKReddy Didn't know JOINS can be used in this way too. In your usage, you are getting a grade as ouput based on the condition on Marks which is kind of new.
niyati_pri [deleted]
Sort 661 Discussions, By:
Please Login in order to post a comment