中南大学数据库课程设计题目答案(2)

（6） 学生选课表(sc)的创建与录入 create table sc

(snum char(10) not null, cnum char(10) not null, score numeric(5), teacher char(10),

primary key(snum,cnum),

foreign key(snum) references s(snum), foreign key(cnum) references c(cnum))

3.创建学生选课视图sc_sview（学号，姓名，课程号，课程名，教师，成绩，学分，性质，开课学期）。

create view sc_view AS

select s.snum 学号,s.sname 姓名,c.cnum 课程号,c.cname 课程名,sc.teacher 教师,sc.score 成绩,mtrain.credit 学分,mtrain.ckind 性质,mtrain.cdate 开课学期 from sc,s,c,mtrain

where sc.snum=s.snum and sc.cnum=c.cnum and left(sc.snum,4)=mtrain.mnum

4.创建“商学院”的学生视图，并由出生日期计算出年龄。（注：系统函数year()取一个日期型数据的年份。getdate()取系统日期。“商学院”有工商、金融等专业。）

create view 商学院(snum,sname,sex,birthd,age) AS

select snum 学号,sname 姓名, year(getdate())-year(birthd) 年龄 from s

where snum like ’16%’

5.创建索引：

(1)分别给专业信息表、专业培养计划表各创建一个聚集索引。 专业信息表：create clustered index mtrain# on mtrain(mnum) 专业培养计划表：create clustered index minf# on minf(mnum) 问题：

解决：在企业管理器表中删除已有索引即可

(2)在学生选课表的学号、课程号字段上创建一个非聚集索引。（索引按学号升序、同一学号按课程号降序排列。） create index sc# on sc(snum asc,cnum desc)

(3)在学生基本信息表的学号字段上创建一个非聚集索引

create index s# on s(snum)

二、数据查询（注：下面的查询可根据需要先创建视图，然后再对视图查询。） 1. 检索学号为’160512╳╳╳╳’的学生所学必修课程的课程名与任课教师名；（注：学号自定。）

select distinct cname 课程名,teacher 任课教师名 from sc,c,mtrain

where snum like ‘160512%’ and ckind=’必’and sc.cnum=c.cnum and c.cnum=mtrain.cnum

2. 检索至少选了”刘兴”老师所授课程中一门课程的男学生姓名； select sname 姓名 from s,sc

where sc.snum and sex=’男’ and teacher=’刘兴’

3. 检索没有选刘兴老师所授课程的女学生姓名和学号； select sname 姓名,s.snum 学号 from s,sc

where sc.snum=s.snum and sex=’女’ and teacher<>’刘兴’ 问题:snum列名不明确

解决:由于s表和sc表中都有snum，所以需要指定列名所在的表 4．检索’王丽’同学第四学期所有选修课程的课程号、课程名及成绩； select c.cnum 课程号,cname 课程名,score 成绩 from s,c,sc,mtrain

where cdate=’四’ and sname=’王’ and ckind=’选修’ and sc.cnum=c.cnum and mtrain.cnum=c.cnum and sc.snum=s.snum 问题：语句可以执行，但是结果的数量是0条 解决：ckind 的条件应该是’选’而不是’选修’ 修改后能成功返回记录

5．检索工商12级至少选了三门选修课程的学生姓名和学号；（注：选修指选修课，下同。） select snum 学生姓名, snum 学号 from s where snum in

(select snum from sc where cnum in

(select cnum from mtrain where ckind=’选’) and snum in (select snum from sc group by snum having count(cnum)>=3)) 6. 在表SC中统计开设课程的教师人数； select count(distinct teacher) 教师人数 from sc

7.统计工商12级男生、女生各年龄的人数；

select sex 性别,year(getdate())-year(birthd)年龄,count(year(getdate())-year(birthd))人数 from s

where snum like’160112%’

group by sex,year(getdate())-year(birthd)

8.统计每个学生选修课程（已有成绩）的门数、必修学分、选修学分及平均成绩，按平均成绩降序排列；

select sc.snum 学号, sname 姓名,count(sc.cnum) 门数, sum(credit) 学分,ckind 性质, AVG(score) 平均成绩 from sc,mtrain,s

where score is not null and sc.cnum=mtrain.cnum and left(s.snum,4)=mtrain.mnum and sc.snum=s.snum

group by sc.snum,sname, ckind order by AVG(score) desc

9.统计工商11级学生必修、选修课程（已有成绩）的总门数、总学分及平均成绩。

select count(sc.cnum) 总门数, sum(credit) 总学分,AVG(score) 平均成绩,s.snum 学号,sname 姓名 from sc,mtrain,s