SQL Interview question

Sat, Jan 9, 2010

Question: Given table with schema Student(studentId,CourseId,marks) if there are 50 students and 10 courses then find the students with second highest marks in each subjects?

Solution

SELECT studentId,marks AS SecHighest, courseId 
WHERE marks 
IN (SELECT MAX(marks) 
FROM student 
WHERE marks NOT IN SELECT MAX(marks) 
FROM student GROUP BY CourseId)GROUP BY CourseId))

Question: Delete the repeated rows form the given table Student(id,name). Not all the rows but just the additional repeatation

SET Rowcount 1
DELETE Student form Student a 
WHERE (SELECT COUNT(*) 
FROM Studnet b 
WHERE a.id=b.id AND a.name=b.name)>1
WHILE @@Rowcount>0
DELETE Student form Student a 
WHERE (SELECT COUNT(*) 
FROM Studnet b WHERE a.id=b.id AND a.name=b.name)>1
SET Rowcount 0