成绩查询系统源代码.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
where cname like %数据%
显示全部