一道sql面试题吧

create table tmpy1
(
sno char(4), --学号
cno char(4), --课程号
value1 decimal(14,2) --成绩
)
insert into tmpy1 values('s1','c1',10);
insert into tmpy1 values('s1','c2',20);
insert into tmpy1 values('s2','c1',30);
insert into tmpy1 values('s2','c2',40);
insert into tmpy1 values('s2','c3',50);
insert into tmpy1 values('s3','c1',60);
insert into tmpy1 values('s3','c3',70);
insert into tmpy1 values('s4','c1',80);
insert into tmpy1 values('s4','c2',90);
1、查询与s1选择课程一样的学生学号、姓名
select unique sno from tmpy1
where cno IN (select cno from tmpy1 where sno = 's1')  and sno <> 's1' 
2、查询每科分数最高的2条记录
SELECT a.sno,a.cno,a.value1 FROM tmpy1 a WHERE (SELECT COUNT(cno) FROM tmpy1 WHERE cno=a.cno AND a.value1<value1)<2 
ORDER BY cno ASC,value1 DESC

或用 left join  

另可参见：
http://blog.csdn.net/acmain_chm/article/details/4126306