[sql] SQL Query for Student mark functionality

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.

Thanks!

I like the simple solution using windows functions:

select t.*
from (select student.*, su.subname, max(mark) over (partition by subid) as maxmark
      from marks m join
           students st
           on m.stid = st.stid join
           subject su
           on m.subid = su.subid
     ) t
where t.mark = maxmark

Or, alternatively:

select t.*
from (select student.*, su.subname, rank(mark) over (partition by subid order by mark desc) as markseqnum
      from marks m join
           students st
           on m.stid = st.stid join
           subject su
           on m.subid = su.subid
     ) t
where markseqnum = 1

My attempt - I'd start with the max mark and build from there

Schema:

CREATE TABLE Student (
  StudentId int,
  Name nvarchar(30),
  Details nvarchar(30)
)
CREATE TABLE Subject (
  SubjectId int,
  Name nvarchar(30)
)
CREATE TABLE Marks (
  StudentId int,
  SubjectId int,
  Mark int
)

Data:

INSERT INTO Student (StudentId, Name, Details) 
VALUES (1,'Alfred','AA'), (2,'Betty','BB'), (3,'Chris','CC')

INSERT INTO Subject (SubjectId, Name)
VALUES (1,'Maths'), (2, 'Science'), (3, 'English')

INSERT INTO Marks (StudentId, SubjectId, Mark)
VALUES 
(1,1,61),(1,2,75),(1,3,87),
(2,1,82),(2,2,64),(2,3,77),
(3,1,82),(3,2,83),(3,3,67)
GO

My query would have been:

;WITH MaxMarks AS (
  SELECT SubjectId, MAX(Mark) as MaxMark
    FROM Marks
    GROUP BY SubjectId
 )
SELECT s.Name as [StudentName], sub.Name AS [SubjectName],m.Mark
FROM MaxMarks mm
INNER JOIN Marks m 
ON m.SubjectId = mm.SubjectId 
AND m.Mark = mm.MaxMark
INNER JOIN Student s
ON s.StudentId = m.StudentId
INNER JOIN Subject sub
ON sub.SubjectId = mm.SubjectId

SQL Fiddle Example

1. Find the max mark for each subject
2. Join Marks, Student and Subject to find the relevant details of that highest mark

This also take care of duplicate students with the highest mark

Results:

STUDENTNAME SUBJECTNAME     MARK
Alfred  English     87
Betty   Maths           82
Chris   Maths           82
Chris   Science     83

I would have said:

select s.stname, s2.subname, highmarks.mark
from students s
join marks m on s.stid = m.stid
join Subject s2 on m.subid = s2.subid
join (select subid, max(mark) as mark
  from marks group by subid) as highmarks
    on highmarks.subid = m.subid and highmarks.mark = m.mark
order by subname, stname;

SQLFiddle here: http://sqlfiddle.com/#!2/5ef84/3

This is a:

• select on the students table to get the possible students
• a join to the marks table to match up students to marks,
• a join to the subjects table to resolve subject ids into names.
• a join to a derived table of the maximum marks in each subject.

Only the students that get maximum marks will meet all three join conditions. This lists all students who got that maximum mark, so if there are ties, both get listed.

I will try to get the answer with one query using CTE and window function rank()

create the tables

create table Students 
(student_id int,
Name varchar(255),
details varchar(255));

create table Subject(
Sub_id int,
name varchar(255));

create table marks
(student_id int,
subject_id int,
mark int);

the answer should be a table with the below fields

student_name | subject_name | mark

plan the execution steps

1. Create a CTE

• join the tables;
• rank the subjects, order them by mark descending

2. Select the output fields from the CTE

with CTE as (select s.name, sb.name as subject_name, m.mark, rank() over(partition by sb.name order by m.mark desc) as rn 
from Students s
join marks m on s.student_id = m.student_id
join subject sb
on sb.Sub_id = m.subject_id)
select name , subject_name, mark
from CTE
where rn = 1

Just for fun, consider the different question one would get with a very literal interpretation of the OP's description: "Write a query to print the list of names of students who have scored the maximum mark in each subject."

Those who've answered here have written queries to list a student if he or she scored the maximum mark in any one subject, but not necessarily in all subjects. Since the question posed by the OP does not ask for subject names in the output, this is a plausible interpretation.

To list the names of students (if any) who have scored the maximum mark in all subjects (excluding subjects with no marks, since arguably there is then no maximum mark then), I think this works, using column names from Michael's SQL Fiddle, which I've adapted here.

select StudentName
from Student 
where not exists (
  select * from Subject
  where exists (
    select * from Mark as M1
    where M1.SubjectID = Subject.SubjectID
    and M1.StudentID <> Student.StudentID
    and not exists (
      select * from Mark as M2
      where M2.StudentID = Student.StudentID
      and M2.SubjectID = M1.SubjectID
      and M2.MarkRate >= M1.MarkRate
    )
  )
)

In other words, select a student X's name if there is no subject in which someone's mark for that subject is not matched or exceeded by some mark belonging to X for the same subject. (This query returns a student's name if the student has received more than one mark in a subject, so long as one of those marks is the highest for the subject.)

Select S.StudentName 
From Student S 
where S.StudentID IN 
    (Select StudentID from (
        ( Select Max(MarkRate)as MarkRate,SubjectID From  Mark Group by SubjectID)) MaxMarks, Mark
 where MaxMarks.SubjectID= Mark.SubjectID AND MaxMarks.MarkRate=Mark.MarkRate)

SQL> select * from stud;

 STUDENTID NAME                           DETAILS
---------- ------------------------------ ------------------------------
         1 Alfred                         AA
         2 Betty                          BB
         3 Chris                          CC

SQL> select * from subject;

 SUBJECTID NAME
---------- ------------------------------
         1 Maths
         2 Science
         3 English

SQL> select * from marks;

 STUDENTID  SUBJECTID       MARK
---------- ---------- ----------
         1          1         61
         1          2         75
         1          3         87
         2          1         82
         2          2         64
         2          3         77
         3          1         82
         3          2         83
         3          3         67

9 rows selected.

SQL> select name, subjectid, mark
  2  from (select name, subjectid, mark, dense_rank() over(partition by subjectid order by mark desc) rank
  3  from stud st, marks mk
  4  where st.studentid=mk.studentid)
  5  where rank=1;

NAME                            SUBJECTID       MARK
------------------------------ ---------- ----------
Betty                                   1         82
Chris                                   1         82
Chris                                   2         83
Alfred                                  3         87

SQL>

 select max(m.mark)  as maxMarkObtained,su.Subname from Student s 
  inner join Marks m on s.Stid=m.Stid inner join [Subject] su on
  su.Subid=m.Subid group by su.Subname

I have execute it , this should work.

SELECT          subjectname,
                studentname 
FROM            student s 
INNER JOIN      mark m 
  ON            s.studid = m.studid 
INNER JOIN      subject su 
  ON            su.subjectid = m.subjectid 
INNER JOIN (
  SELECT        subjectid,
                max(value) AS maximum 
  FROM          mark 
  GROUP BY      subjectid
)               highmark h 
  ON            h.subjectid = m.subjectid 
  AND           h.maximum = m.value;

Using a Subquery:

select m.subid, m.stid, m.mark
from marks m,
    (select m2.subid, max(m2.mark) max_mark
    from marks m2
    group by subid) subq

where subq.subid = m.subid
and subq.max_mark = m.mark
order by 1,2;

Using Rank with Partition:

select subid, stid, mark
from (select m.subid, m.stid, m.mark,
rank() over (partition by m.subid order by m.mark desc) Mark_Rank
from marks m)
where Mark_Rank = 1
order by 1,2;