中南大学商学院数据库实践报告.docx

1、中南大学商学院数据库实践报告数据库实践报告-姓名：学号：班级：指导老师：简惠云Part11、建表 1、专业信息表 create table minf （mnum varchar （6） not null unique， major varchar （6）， mintro varchar （6）， primary key （mnum） 2、学生基本信息表 create table s （snum varchar (6) not null unique, sname varchar (6), birthd smalldatetime, sex varchar (6), mnum varchar (

2、6) primary key (snum) ) 3、 课程基本信息表create table c (cnum varchar (6) not null unique, cname varchar (6) primary key (cnum) 4、专业培养计划create table mtrain (mnum varchar (6) not null unique , cnum varchar (6) not null unique, ccredit numeric (6,2), ckind varchar (6)， ckind varchar（6）， primary key （mnum，cnu

3、m） foreign key(mnum)references minf(mnum), foreign key (cnum)references c (cnum) 5、教师开课信息表create table tea (cnum varchar (6) not null unique, teacher varchar (6), primary key (cnum) foreign key (cnum)references c (cnum) 6、学生选课表create table sc (snum varchar (6) not nul unique, cnum varchar (6) teache

4、r varchar (6), score varchar (6), primary key (snum) foreign key(cnum)references c(cnum)二、建视图 1、创建学生选课试图s-sview（学号，姓名，课程号，教师，成绩，学分，性质，开课学期）create view sc_sview as select s.snum,s.sname,um,sc.teacher,sc.score,ccredit,ckind,cdate from s,c.mtrain,sc where um=um and s.snum=sc.snum and um=um 2、商学院学生视图，并由

5、出生日期计算年龄create view 商学院 as select snum,sname,year(getdate()-year(birth) as age,major from minf,s where minf.mnum=s.mnum三、创建索引 1、分别给专业信息表，专业培养计划表各创建一个聚集索引create clustered index a on minf(mnum) create clustered index b on mtrain(mnum) 2、在学生选课的学号，课程号字段上创建一个非聚集索引create index c on sc(snum,cnum desc) 3、在学

6、生基本信息表的学号字段上创建一个非聚集索引create index d on s(snum)四、数据查询 1、检索学号为1605051111的学生所学必修课程的课程名与任课教师名select um,teacher from s,mtrain,sc where s.snum=sc.snum and um=um and s.snum = 1605051111 and ckind = 必修2 、检索至少选了刘兴老师所授课程中一门课程的男学生姓名select sname from s,sc where s.snum=sc.snum and sex=男 and teacher=刘兴 3、检索没有选刘兴老

7、师所授课程的女学生是姓名和学号 select sname,s.snum from s,sc where s.snum=sc.snum and sex=女 and s.snum not in(select snum from sc where teacher=刘兴) 4、 检索王丽同学第四学期所有选修课程的课程号，课程名和成绩select um,cname,score from s,mtrain,sc,c where um=um and s.snum=sc.snum and um=um and sname=王丽 and cdate=第四学期 5、检索工商07级至少选了三门选修课程的学生姓名和学号

8、select s.snum,s.sname from s,sc where s.snum in (select snum from sc group by snum having(count(cnum)=3) 6、检索工商07级全部学生都选修的课程的课程号和课程名select sname,snum from s,mtrain where s.mnum=mtrain.mnum and snum like 160507? and ckind=选修group by sname,snum having count(cnum)=3 7、在表sc中统计开设课程的教师人数select distinct co

9、unt(teacher) 教师人数 from sc 8、统计工商07级男生，女生各年龄的人数select count (s.snum) 人数,year (getdate()-year(birth) 年龄 from s where snum like 160507%group by sex,year(getdate()-year(birth) 9、统计每个学生选修课程（已有成绩）的门数，必修学分，选修学分级平均娼妓，按平均成绩降序排列select s.snum,count (um) 选修课程门数,sum(b.ccredit) 必修学分,sum(a.ccredit) 选修学分,avg(score)

10、 平均成绩 from sc,s,mtrain as a,mtrain as b,mtrain where s.snum=sc.snum and um=um and a.mnum=b.mnum and score is not null and a.ckind=选修 and b.ckind=必修 group by s.snum order by avg(score) desc 10、统计工商05级学生必修，选修课程（已有成绩）的总门数，总学分及平均成绩select s.snum,count (um) 选修课程门数,count(um) 必修课程门数,sum(a.ccredit)+sum(b.ccr

11、edit) 总学分, avg(score) 平均成绩from sc,s,mtrain as a,mtrain as b,mtrain where s.snum=sc.snum and um=um and a.mnum=b.mnum and score is not null and a.ckind=选修 and b.ckind=必修 and s.snum like 160505? group by s.snum 11、分别统计各个专业必修，选修课程的门数，查询结果按必修门数降序，选修门数降序 select a.mnum,count (um) 选修课程门数,count(um) 必修课程门数 fr

12、om mtrain as a,mtrain as b,mtrain where a.mnum=b.mnum group by a.mnum order by count(um)desc,count(um) 12、查询工商07级第四学期级以前所选课还没有成绩的学生学号，程号，课程名及任课教师select sc.snum,um,cname,tea.teacher from sc,s,tea,mtrain,c where sc.snum=s.snum and um=um and um=um and sc.snum like 160507%and score is null 13、查询工商07级选修课

13、学分大于15分的学生学号及姓名select sc.snum,s.sname from sc,s where sc.snum like160507% and um in (select cnum from mtrain group by cnum having sum(ccredit)=15) and cnum in(select cnum from mtrain where ckind=选修) 14、查询工商专业第五学期所有的选修课程select um from sc,mtrain where um=um and cdate=第五学期 and snum like 1605? 15、查询工商05

14、级学生选修课学分还没有达到25分的学生学号，姓名，专业及年级select distinct sc.snum,s.sname,minf.major from sc,s,minf where s.snum=sc.snum and s.mnum= minf.mnum and sc.snum like160505%and um in (select cnum from mtrain group by cnum having sum(ccredit)=25)and cnum in (select cnum from mtrain where ckind=选修) 16、按学期，按专业统计高强老师所授课程的

15、学生的平均成绩select mnum,cdate,avg(score) 平均成绩 from mtrain,sc where um=um and teacher=高强 group by mnum,cdate 17、查询工商07级第四学期各门课程不及格的学生，并用视图是形式存储不及格名单create view v07_bjg as select snum from sc where snum like 160507%and score =60 18、查询未选修李中华老师所受全部课程的学生学号select snum from sc where snum not in (select snum fro

16、m sc where teacher=李中华) 19、统计商学院学生每门课程是平均成绩，要求列出课程名，平均成绩，并按平均成绩从高到低排列select cname,avg(score) 平均成绩 from c,sc where um=um group by um,cname order by avg(score) desc 20、统计商学院09级学生第一学年的平均成绩级排名，检索结果送往另一个表存储，作为分专业的依据select avg(score) 平均成绩 into 第一学年学生成绩 from sc,s where s.snum=sc.snum group by s.snum order

17、by avg(score)五、数据更新 1、在sc中删除选修课尚无成绩的选课元组delete from sc where score is null and snum in (select snum from sc,mtrain where um=um and ckind=选修) 2、将选修刘兴老师课程的女同学选修元组全部删除delete from sc where snum in (select s.snum from sc,s where sc.snum=s.snum and sex=女 and teacher=刘兴) 3、将工商07级学生高等数学课不及格的成绩全改为60分update s

18、c set score=60 where cnum in (select um from sc,c where um=um and cname=高等数学 and snum like 160507?) 4、将工商07级学生数据库技术课程低于该课程平均成绩的女同学成绩提高5%update sc set score=score*1.05 where snum like160507% and snum in (select snum from s where sex=女) and score in (select from sc group by score having score =1) and

19、(cnum=dwno)if dwno=dwtempprint有学生正在选修该科，不能删除！elseprint已删除！go2、 当某们选修课人数达到10人时，则不允许再有人选修该课，并给出信息该门课选课人数已满，请另选其他课程！ useif exists(select name from sysobjectswhere name=t_overarr and type=tr)drop trigger t_overgo create trigger t_fcmc on scafter updateas declare qno varchar(8),sno varchar(8)select qno=zdrs,sno=sjrsfrom mtrainwhere lkh=