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.
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);
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);
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;
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.
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 " ?
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;
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
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
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
make9chaos 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..
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.
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
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.
waghx013 Avoid multiple lines and use -- on students.marks between grades.min_mark and grades.max_mark
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.
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 :)
Sposibo the sorting of this question is very confusing...
select Case when s.marks<=69 then NULL else s.name end as Names, Case when s.marks <=9 then 1 when s.marks >=10 and s.marks<=19 then 2 when s.marks >=20 and s.marks<=29 then 3 when s.marks >=30 and s.marks<=39 then 4 when s.marks >=40 and s.marks<=49 then 5 when s.marks >=50 and s.marks<=59 then 6 when s.marks >=60 and s.marks<=69 then 7 when s.marks >=70 and s.marks<=79 then 8 when s.marks >=80 and s.marks<=89 then 9 else 10 end as grades, s.marks from students s order by grades desc,s.name, s.marks asc;
VerData It is hardcoding.......
joachim_martin Need to fix the problem description to be clear about ordering for students with grades < 8. These students still need to be listed by grade desc but secondary sort should be marks asc.
darth_Fredious this is it: select case when g.grade >= 8 then s.name else NULL end, 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, s.marks;
rshaghoulian MySQL solution
From my HackerRank solutions.
SELECT IF (S.Marks < 70, 'NULL', S.Name), G.Grade, S.Marks FROM Students AS S, Grades AS G WHERE S.Marks BETWEEN G.Min_Mark AND G.Max_Mark ORDER BY G.GRADE DESC, S.NAME;
I think I skipped the "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" part. Code still passes since HackerRank didn't test for that portion.
adelin_ghanayem Isn't this a full join between two tables?, Do you think this would be a good idea in a real production environment ?
rshaghoulian I don't think this is a full outer join since we didn't write FULL OUTER JOIN to combine the 2 tables. This will create the same output as in the sample problem:
Maria 10 99 Jane 9 81 Julia 9 88 Scarlet 8 78 NULL 7 63 NULL 7 68
which doesn't have all rows of both tables, so I think it should be fine.
kumarparth123 marks bw max and min
kumarparth123 what is meaning of third line
rshaghoulian Min_Mark and Max_Mark are columns defined in the table in the original problem. Try running the code WIHTOUT the 3rd line and see what you get. What the 3rd line does is it narrows down the results to only the ones where S.Marks falls in the correct range of G.Min_Mark and G.Max_Mark. For example
Scarlet 8 78
is in the correct range (so it is returned as output with my original code), while
Aamina 9 77
is not in the correct range (since a grade of 77 should be a mark of 8)
kumarparth123 thanks sir.
kumarparth123 sir what is need of group by name.
rshaghoulian group by is not needed. I don't use it in my code.
CrystalEngineer You missed a task: "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"
rshaghoulian I agree. I skipped that part. Code still passed since HackerRank didn't test for that portion of the problem.
g_maccario Hi everybody, I don't know why I continue to receive this error:
mysql: [Warning] Using a password on the command line interface can be insecure.
also for a simple query like selet * from students;
rahdnag Seems like there is a typo, try
SELECT * FROM Students;
Sort 421 Discussions, By:
Please Login in order to post a comment