ACCP5.0高校管理系统

create database Student on ( name = 'smdf', filename = 'e:\Student\smdf.mdf', size = 3, filegrowth = 10 ) log on ( name = 'sldf', filename = 'e:\Student\sldf.ldf', size = 1, filegrowth = 10 ) use student create table stuInfo ( stuName varchar(50) not null, stuNo varchar(50) primary key not null, stuSex char(2) default '男' check(stuSex = '男' or stuSex = '女') not null, stuAge int check(stuAge between 15 and 50) not null, stuSeat int identity(1,1) not null, stuAddress varchar(200) default '地址不详' ) insert into stuinfo values('张秋丽','s25301','男',18,'北京海淀') insert into stuinfo values('李斯文','s25303','女',22,'河南洛阳') insert into stuinfo(stuname,stuno,stusex,stuage) values('李文才','s25302','男',31) insert into stuinfo values('欧阳俊雄','s25304','男',28,'新疆威武哈') --drop table stuInfo create table stuMarks ( examNo varchar(100) primary key not null, stuNo varchar(50) foreign key references stuInfo(stuNo) not null, writtenExam int check(writtenExam between 0 and 100) default 0, labExam int check(labExam between 0 and 100) default 0 ) insert into stumarks values('E2005070001','s25301',80,58) insert into stumarks(examno,stuno,writtenexam) values('E2005070002','s25302',50) insert into stumarks values('E2005070003','s25303',97,82) select * from stuInfo select * from stuMarks select * from stuInfo where stuSex = '男' select * from stuMarks where writtenExam between 75 and 100 select stuname,writtenexam,labexam from stuinfo inner join stumarks on stuinfo.stuno = stumarks.stuno select avg(writtenexam) as '笔试平均成绩' from stumarks select avg(labexam) as '机试平均成绩' from stumarks select count(stuno) from stumarks select count(stuno) from stumarks where(writtenexam < 60 or labexam < 60) select *,(writtenexam + labexam)/2 as 课程平均成绩 from stumarks select *,(writtenexam + labexam)/2 as 课程平均成绩 from stumarks order by (writtenexam + labexam)/2 desc select stuname,writtenexam,labexam,(writtenexam + labexam)/2 as 课程平均成绩 from stuinfo inner join stumarks on stuinfo.stuno = stumarks.stuno