成绩查询系统源代码.doc

create database Course on ( name=Course_data, filename=d:\database\Course_mdf, size=5MB, maxsize=10MB, filegrowth=2MB ) log on ( name=Course, filename=d:\database\Course_ldf, size=3MB, maxsize=10MB, filegrowth=20% ) go use Course go create table student ( sno char(9) primary key, sname char(12) not null, sex char(2) , sage int , sdept char(2) , ) create table Course ( cno smallint primary key, cname char(11) not null, cpno smallint , ccredit smallint not null, foreign key (cpno) references Course(cno) ) create table SC ( sno char(9) , cno smallint not null, grade int not null, primary key(sno,cno) ) use Course insert into student (sno ,sname ,sex ,sage ,sdept) values (200215121,李勇,男,20,CS) insert into student (sno ,sname ,sex ,sage ,sdept) values (200215122,刘晨,女,19,CS) insert into student (sno ,sname ,sex ,sage ,sdept) values (200215123,王敏,女,18,MA) insert into student (sno ,sname ,sex ,sage ,sdept) values (200215124,张立,男,19,IS) use Course /* Course (cno ,cname ,cpno ,ccredit)*/ insert into Course values (6,数据处理,NULL,2); go insert into Course values (7,PASCAL语言,6,4); go insert into Course values (5,数据结构,7,4); go insert into Course values (1,数据库,5,4); go insert into Course values (2,数学,NULL,2); insert into Course values (3,信息系统,1,4); insert into Course values (4,操作系统,6,3); use Course /*(sno,cno ,grade)*/ insert into SC values (200215121,1,92); insert into SC values (200215121,2,85); insert into SC values (200215121,3,88); insert into SC values (200215122,2,90); insert into SC values (200215122,3,80); go /*(1)查询计算机系（CS）的所有student的姓名，查询结果按学号升序排列。*/ use Course select sname 姓名 from student where sdept=CS order by sno /*(2)查询studentSC成绩合格的Course成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。*/ use Course select sno,cno Course号,(grade-60)*0.1+1 成绩 from SC where grade=60 /*（3）查询学分为3或4的Course名称。*/ use Course select cname Course名 from Course where ccredit=3 or ccredit=4 /*（4）查询所有Course名称中含有“数据”的Course号。*/ use Course select cno Course号 from Course whe