Interviews are like short refresher courses where you learn different things and its a good habit to retry the questions which you could not answer on the spot.
One of the interesting queries (SQL) i found during one of the interviews was this one which looked so simply but it required little bit of brain storming.
Task : You have 3 tables (Student {StdId, Name}, Course{CourseId,CourseName}, Marks{StdId,CourseId, Marks}) . Select highest scores from each course with course name and student name.
Query :
SELECT C.CourseName,S.Name,M.Marks
FROM
(
select courseid,max(marks) as Marks from marks
group by courseid
) tmp
INNER JOIN Marks M ON tmp.CourseId = M.CourseId AND tmp.Marks = M.Marks
INNER JOIN Course C ON C.CourseId = M.CourseId
INNER JOIN Student S ON S.StdId = M.StdId
ORDER BY M.CourseId
One of the interesting queries (SQL) i found during one of the interviews was this one which looked so simply but it required little bit of brain storming.
Task : You have 3 tables (Student {StdId, Name}, Course{CourseId,CourseName}, Marks{StdId,CourseId, Marks}) . Select highest scores from each course with course name and student name.
Query :
SELECT C.CourseName,S.Name,M.Marks
FROM
(
select courseid,max(marks) as Marks from marks
group by courseid
) tmp
INNER JOIN Marks M ON tmp.CourseId = M.CourseId AND tmp.Marks = M.Marks
INNER JOIN Course C ON C.CourseId = M.CourseId
INNER JOIN Student S ON S.StdId = M.StdId
ORDER BY M.CourseId
