Question

sql 面试题

1、编写SQL语句查询出每个各科班分数最高的同学的名字，班级名称，课程名称，分数
2、编写一个存储过程，为分数60分以下的同学加5分，分数在60分至80分的同学加2分，分数80分以上的同学加1分，若有100分则不加分。

Answer 1

select class.name,student.name,course,score
from class,student,score,(select max(score) max_score ,course,classid from score,student where score.studentid = student.id group by course,classid) tmp
where class.id =student.classid
and student.id = score.studentid
and score.score = tmp.max_score
and score.course =tmp.course
and student.calssid = tmp.classid

CREATE PROCEDURE test
AS
BEGIN

update table_1 set score=score+1 where score>=80 and score<100;
update table_1 set score=score+2 where score>=60 and score<80;
update table_1 set score=score+5 where score<60;

END
GO

Answer 2

1、
select studentName,classname,course,score
from score
inner join student
on score.studentid=student.studentid
inner join class
on student.classid=class.classid
where score in
(select max(score)
from score inner join student
on score.studentid = student.studentid
group by classid,course)

2、
create proc proc_scs
AS
begin
update score set score=score+1 where score>=80 and score<100;
update score set score=score+2 where score>=60 and score<80;
update score set score=score+5 where score<60;
end
go
存储过程同三楼

Answer 3

select c.classname 班级,e.course 科目,max(e.score) 最高分 from class c,student s,score e where c.classid=s.classid and s.studentid=e.studentid group by c.classname,,e.course

就写到这吧，楼主给的分就值这些了，嘿嘿

Answer 4

1、select top 1 名字，班级名称，课程名称，分数 from 班级 order by 分数 desc；

Answer 5

确实很简单，3楼正解。估计这公司开出的待遇也不怎么样。