Write a query to display name roll no address of student average marks
Answers
Answer:
Got this as an interview question from Amazon to test basic SQL skills and I kind of flopped it. Consider the following tables:
Student - Stid, Stname, Details
Subject - Subid, Subname
Marks - Stid, Subid, mark
Write a query to print the list of names of students who have scored the maximum mark in each subject.
The wrong answer which I gave was:
select A.Stname from A as Student, B as
(select Stid, Subid, max(mark) from Marks groupby Subid) where A.Stid = B.Stid
I was thinking you can have a table B in which you can get the top marks alone and match it with the names in the student table A. But turns out my "groupby" is wrong.
One more variation of the question which I felt was can be asked is that, if there is more than one student having the highest mark in a subject, even their names should be included.
Can you please help solve these queries. They seem to be simple, but I am not able to get a hang of it.