如何查询计算机系考试成绩最高的学生的姓名。代码如下：

程序代码：

[color=#008080]--创建数据库
Create database kekin
Go
--打开数据库，所有的数据表都在该数据库中创建
use kekin
GO

/*建立学生信息表*/
create table Student
(Sno char(7) primary key,                    /*学号*/
Sname char(10) not null,                     /*姓名*/
Ssex char(2) check (Ssex in ('男','女')),    /*性别*/
Sage tinyint check (Sage>=15 and Sage<=45),  /*年龄*/
Sdept char(20) default '计算机系'            /*所在系*/
);
/*建立课程信息表*/
create table Coures            
(Cno char(10) primary key,      /*课程号*/
Cname char(20) not null,        /*课程名*/
Scredit int check (Scredit>=0), /*学分*/
Semster int check (Semster>=0), /*学期*/
Period int default 48           /*学时*/
);
/*建立学生选课信息表*/
create table SC
(Sno char(7),      /*学号*/
Cno char(10),      /*课程号*/
Grade int check (Grade>=0 and Grade<=100),    /*成绩*/
primary key (Sno,Cno),
XKLB char(4),      /*修课类别*/
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Coures(Cno)
);

/*插入学生信息数据*/
insert into student
        values('9512101','李勇','男',19,'计算机系')
insert into student
        values('9512102','刘晨','男',20,'计算机系')
insert into student
        values('9512103','王敏','女',20,'计算机系')
insert into student
        values('9521101','张立','男',22,'信息系')
insert into student
        values('9521102','吴宾','女',21,'信息系')
insert into student
        values('9521103','张海','男',20,'信息系')
insert into student
        values('9531101','钱小平','女',18,'数学系')
insert into student
        values('9531102','王大力','男',19,'数学系')

/*插入课程信息数据*/
insert into Coures
        values('C01','计算机文化基础',3,1,48)
insert into Coures
        values('C02','VB',2,3,48)
insert into Coures
        values('C03','计算机网络',4,7,48)
insert into Coures
        values('C04','数据库基础',6,6,48)
insert into Coures
        values('C05','高等数学',8,2,48)
insert into Coures
        values('C06','数据结构',5,4,48)


/*插入学生选课信息数据*/
insert into SC
        values('9512101','C01',90,'必修')
insert into SC
        values('9512101','C02',86,'选修')
insert into SC
        values('9512101','C06',62,'必修')
insert into SC
        values('9512102','C02',78,'选修')
insert into SC
        values('9512102','C04',66,'必修')
insert into SC
        values('9512102','C01',82,'选修')
insert into SC
        values('9512102','C05',92,'必修')
insert into SC
        values('9512102','C06',50,'必修')
insert into SC
        values('9512103','C02',68,'选修')
insert into SC
        values('9512103','C06',62,'必修')
insert into SC
        values('9531101','C01',80,'选修')
insert into SC
        values('9531101','C05',95,'必修')
insert into SC
        values('9531102','C05',85,'必修')

[/color]

例如 -

select   Student.Sname
from    Student join SC on Student.Sno = SC.Sno
where  Student.Sdept = 'SC_Dept'
           and
           SC.Grade =  (
                                 select  max(SC.Grade)
                                 from   Student join SC on Student.Sno=SC.Sno
                                 Where Student.Sdept = 'SC_Dept'
                               )