数据库课程——设计报告.docx

数据库课程设计报告附件1: 经济管理学院实验报告 姓名:XX 班级:11电子商务1班 学号:XXXXXX 实验成绩: 课程名称: 数据库应用课程设计 指导教师: 赖玉霞 实验名称: SQL Server2008数据库应用课程设计 2012 年 6 月 25 日 实验目的: 1(熟练掌握数据库设计技术。 2(熟练掌握数据库查询语句及其应用。 3(掌握数据库综合应用能力。 实验环境:,数据库管理系统SQL Server2008 实验内容与步骤: 实验内容: 已知选课数据库myGrade包含学生、课程、教师、选课和授课等5个关系表，各表及其列名含义如下(主键已用下画线标记): 学生(学号，姓名，性别，出生日期，班级) 课程(课程编号，课程名称，前修课程，课程性质，学分) 教师(教师编号，姓名，性别，出生日期，职称) 选课(学号，课程编号，选课学期，成绩) 授课(教师编号，课程编号，授课学期) 1(创建数据库myGrade，在数据库中创建上述5个关系数据表，要求使用非中文的表名和列名，并在各表中插入模拟数据。在建表时必须定义各表的主键、外键、CHECK等约束条件。具体要求如下: (1) 学生表和教师表中的性别取值“M”或“F”分别表示“男”或“女”;学号长度为8位，第一位以字母开头，最后一位为性别(即F或M)，其他6位为数字。 (2) 课程表中的前修课程为外键，它参照自己所在表中的主键列(即“课程编码”);课程性质分为“必修课”和“选修课”两类，必修课用字母A表示，选修课用字母B表示;学分取值0.510之间。 (3) 选课学期和授课学期都为11位字符串，例如“2010-2011-1”。其中前9位表示学年(年份之间用横杆分隔)，最后一位表示某个学年中的学期序号，取值1或2。 (4) 其他列的类型、长度、外键及CHECK等约束条件根据选课数据库语义自行定义。 (5) 在插入模拟数据之后，为各个外键中的每一列创建非聚集索引。 实验步骤: 1.建立学生成绩管理数据库mygrade create database mygrade 2.数据库mygrade的各个表的创建 1)学生表 Students create table students (sno nvarchar(8) constraint pk1 primary key clustered check (sno like A-Z0-90-90-90-90-90-9FM), sname nvarchar(20), gender char(2) check (gender like FM), birthdate datetime, class nvarchar(20) insert into students values (A246357F,liuxiao,F,1993-5-6,10) insert into students values (B462237M,hongtao,M,1993-6-2,5) insert into students values (C346723M,xiaren,M,19932-6-30,7) insert into students values (D345854F,fanfan,F,1993-8-14,9) insert into students values (E463849F,geguitar,F,1993-7-26,8) insert into students values (E888338M,xuanxu,M,1993-1-30,8) insert into students values (D673945F,dawei,F,1993-4-6,1) insert into students values (A362956M,fengdong,M,1993-7-6,10) insert into students values (C346234M,heigirl,M,1993-12-4,13) insert into students values (F345956F,qianjiang,F,1991-10-3,7) create nonclustered index indexfk1 on courses(pcno) 2)课程表Courses create table courses (cno nvarchar(10) constraint pk2 primary key clustered, cname char(20), pcno nvarchar(10) constraint fk1 references courses(cno), ctype char(2) check (ctype like AB), credit float check (credit between 0.5 and 10) insert into courses values (c4,shujuku,c2,A,4) insert into courses values (c3,cyuyan,c1,A,4) insert into courses(cno,cname,ctype,credit) values (c2,jichujineng,A,2) insert into courses(cno,cname,ctype,credit) values (c1,jizhu,A,2) insert into courses values (c6,gaoshuA2,c5,A,5) insert into courses(cno,cname,ctype,credit) values (c5,gaoshuA1,A,5) insert into courses(cno,cname,ctype,credit) values (c7,yingyu1,B,4) insert into courses values (c8,yingyu2,c7,A,4) insert into courses values (c9,yingyu3,c8,A,4) insert into courses values (c10,yingyu4,c9,B,4) 3)教师表 teachers create table teachers (tno nvarchar(10) constraint pk3 primary key clustered, tname nvarchar(20), gender char(2) check (gender like FM), birthdate datetime, title char(8) insert into teachers values (t1,jianming,M,1975-12-9,教授) insert into teachers values (t2,yuxia,F,1983-10-9,教授) insert into teachers values (t3,gejun,M,1971-8-26,教授) insert into teachers values (t4,jiangyao,M,1977-3-7,副教授) insert into teachers values (t5,liuling,F,1982-6-21,副教授) insert into teachers values (t6,moyan,F,1975-4-19,教授) insert into teachers values (t7,xiaoming,M,1965-1-15,讲师) insert into teachers values (t8,ruiyong,M,1975-8-22,教授) insert into teachers values (t9,yangli,F,1965-2-9,副教授) insert into teachers values (t10,mengzi,M,1990-12-29,讲师) 4)选课表 sc create table sc (sno nvarchar(8) constraint fk2 references students(sno), cno nvarchar(10) constraint fk3 references courses(cno), period char(11) check(period like 0-90-90-90-9-0-90-90-90-9-12), grade int constraint pk4 primary key(sno,cno) insert into sc values(A246357F,c1,2010-2011-1,84) insert into sc values(A246357F,c2,2010-2011-1,76) insert into sc values(A246357F,c3,2010-2011-2,89) insert into sc values(B462237M,c1,2010-2011-1,92) insert into sc values(B462237M,c3,2010-2011-2,64) insert into sc values(C346723M,c7,2011-2012-1,74) insert into sc values(C346723M,c8,2011-2012-2,53) insert into sc values(E463849F,c9,2012-2013-1,95) insert into sc values(E888338M,c6,2012-2013-1,91) insert into sc values(E888338M,c5,2011-2012-2,88) create nonclustered index indexfk2 on sc(sno) create nonclustered index indexfk3 on sc(cno) 5)授课表 tc create table tc (tno nvarchar(10) constraint fk4 references teachers(tno), cno nvarchar(10) constraint fk5 references courses(cno), period char(11) check(period like 0-90-90-90-9-0-90-90-90-9-12) constraint pk5 primary key(tno,cno) insert into tc values (t1,c1,2010-2011-1) insert into tc values (t1,c2,2010-2011-1) insert into tc values (t2,c2,2010-2011-1) insert into tc values (t3,c5,2011-2012-2) insert into tc values (t4,c6,2012-2013-1) insert into tc values (t5,c6,2012-2013-1) insert into tc values (t4,c7,2011-2012-1) insert into tc values (t6,c7,2011-2012-1) insert into tc values (t7,c8,2011-2012-2) insert into tc values (t9,c9,2012-2013-1) create nonclustered index indexfk4 on tc(tno) create nonclustered index indexfk5 on tc(cno) 3. 编写T-SQL语句，完成下列各项功能 (1) 根据学生成绩表中的数据显示全部学生的成绩要求按班级、学号的次序显示输出内容中包括学生姓名和课程名称并使用中文标题。 select c.sno 学号,sname 学生姓名,cname 课程名称,class 班级,grade 成绩 from students a,courses b,stucourses c where c.sno=a.sno and o=o order by class ,c.sno (2) 根据学生基本信息表与成绩表中的数据显示姓名为x的这个学生第一学期的全部课程成绩。 select cno,grade from students a jion stucourses b on a.sno=b.sno where sname=x and period like 0-90-90-90-9-0-90-90-90-9-1 (3) 根据课程表与成绩表中的数据显示输出课程名称为x.的该课程的平均成绩及格人数比例。 select a.grade into #tmp from sc as a join courses as b on o=o where cname=x select AVG(grade) as avg ,100*(select COUNT (*)from #tmp where grade=60)/COUNT(*) as ratefrom #tmp (4) 根据课程表与成绩表中的数据显示学号为x的这个学生最近这个学期所得到的学分数。 select SUM(credit) from courses where cno in (select cno from stucourses where sno=x and grade =60) (5) 根据各表数据列出最近这个学期哪些学生选修的课程数量最多要求列出姓名。 select sname from students where sno in (select top 1 sno from sc where period in (select MAX(period) from sc) group by sno order by COUNT(*) desc) (6) 根据各表数据列出最近这个学期哪些学生至少选了两门或两门以上的选修课程要求列出姓名。 with tmp2 as (select o from courses a,sc b where o=o and ctype=B), tmp3 as (select COUNT(*) num from sc c,tmp2 where o=o group BY sno) select sname from students where sno in (select sno from sc c, tmp2,tmp3 where o=o and num=2 and period in (select MAX(period) from sc) group by sno) (7) 根据课程表与成绩表数据列出哪些选修课程学生选修的学生人数最多。 with tmp4 as (select o from courses a,sc b where o=o and ctype=B), tmp5 as (select top 1 o from stud courses c, tmp4 where o=o group by o order by COUNT(*) desc) select o,cname from courses d,tmp5 where o=o (8) 分别列出课程名称为X的这门课程考试成绩排名前5位与后5位的学生姓名。 select * from (select top 5 sname from students a,courses b,sc c where a.sno=c.sno and o=o and cname=x order by grade desc) as p union all select * from (select top 5 sname from students a,courses b,sc c where a.sno=c.sno and o=o and cname=x (1) order by grade) as b列出必修课程成绩不及格其累计学分超过15分的那些学生的姓名。 with tp1 as (select cno,credit from courses where ctype=A), tp2 as (select sno,sum(credit) num from sc a,tp1 where o=o and grade15 (2) 列出课程名称为x的这门课考试成绩平均分最高的班级。 select top 1 class,AVG(grade) from stucourses a,courses b,students c where o=o and a.sno=c.sno and cname=jizhu group by class order by AVG(grade) desc (3) 分班级和性别输出最近这个学期全部必修课课程的平均考试成绩。 select avg(a.grade) ,class ,gender from sc a join students b on a.sno =b.sno where period =2011-2012-2 group by class ,gender (4) 根据成绩表中的数据计算学生基本信息表中每个学生每个学期的平均考试成绩。 select sno,period,AVG(grade) as avg from sc group by sno,period (5) 列出第2011-1学期中各个班级平均考试成绩排名前5位学生的姓名要求按各班按成绩排序。 select top 5 sname ,class,AVG(grade) as avg from sc as a join students as b on a.sno=b.sno where period =2011-2012-1 group by sname,class order by class,avg desc (6) 将成绩表中选修课的成绩按五级制,优、良、中、及格、不及格,形式进行输出。 select sno,o,grade,成绩=case when grade between 90 and 100 then 优 when grade between 80 and 89 then 良 when grade between 70 and 79 then 中 when grade between 60 and 69 then 及格 else 不及格 end from sc a,courses b where o=o and ctype=B (7) 将成绩表中必修课成绩按实际分数输出同时选修课的成绩按五级制形式输出。 select Sno,a.Cno,Period,Grade=CASE when Grade between 60 and 70 then 及格 when Grade between 70 and 80 then 中 when Grade between 80 and 90 then 良 when Grade between 90 and 100 then 优 else 不及格 end from Sc a join Courses b on a.Cno=b.Cno where b.Type=B union all select Sno,a.Cno,Period,cast(Grade as varchar(8) from Sc as a join Courses b on a.Cno=b.Cno where b.Type=A (8) 在学生表中添加4个列分别存储20082011四个学年的学生综合智育成绩,利用系统表判断这4列是否已经存在如果已经存在则不必添加,。已知每个学生每个学年的综合智育成绩计算公式如下: 综合智育成绩=该学期全部必修课成绩的平均分+每门选修课成绩的档次值其中选修课成绩档次值规定为: =90分:加4分,80，89分:加3分,70，79分:加2分,60，69分:加1分,60分:加0分。 (9) 在学生表中添加4个列分别存储20082011四个学年的学生综合智育成绩根据以上公式使用相关子查询和UPDATE语句计算每个学生每个学年的综合智育成绩。 (10) 根据各表数据与前面的计算结果统计列出第2011学年综合智育成绩排名前40%且该学年每门课程成绩都及格的学生的名单。 (11) 查询每个学期中平均成绩都在班级排名前30%的学生的姓名。 select Sname,AVG(Grade) as a,Period from Students a join Sc b on a.Sno=b.Sno where a.Sno in (select top 30 percent c.Sno from Sc c join Students d on c.Sno=d.Sno where b.Period=c.Period and d.class=a.class group by c.Sno,Period,class order by AVG(Grade) desc) group by Sname,Period,class (12) 计算姓名为x的这个学生第2011-1学期平均成绩在班级中的排名名次。 with tmp as (select class,s.Sname,avg(Grade) as 平均分, Row_Number()over(partition by class order by avg(Grade) desc) as 名次 from Sc st join students s on s.Sno = st.Sno join courses c on c.Cno = st.Cno where period = 2010-2011-1 group by s.Sname,class) select 名次 from tmp where Sname = mark (13) 查询选修过“数据库”和“数据结构”这两门课程的学生姓名。 with tmp as(select sname from students a join sc b on a.sno =b.sno join courses c on o =o where cname=数据库) select sname from students a join sc b on a.sno =b.sno join courses c on o =o where cname=数据结构 and sname =(select sname from tmp ) (14) 查询没有选修过“数据库”这门课程的学生姓名。 select sname from students where sno not in (select sno from sc a join courses b on o=o where cname=数据库) (15) 查询选修过“数据库”但没有选修其先行课的学生姓名。 with tmp as (select sname,a.sno from students a join stucourses b on a.sno=b.sno join courses c on o=o where cname=数据库) select sname from tmp where sno not in( select sno from stucourses a join courses b on o=o where cname =数据结构) (16) 查询所有课程成绩全部及格的学生姓名。 select sname from sc as a join students as b on a.sno=b.sno where a.sno not in (select sno from sc where grade60) (17) 查询每个学期必修课成绩全部及格的学生姓名。 select sname from students a join sc b on a.sno=b.sno join courses c on o=o where a.sno not in(select sno from sc where grade60) and ctype=A (18) 查询选修过教师“达尔文”所授的全部课程的学生姓名。 select Cno into #tmp10 from Tc a join Teachers as b on a.Tno=b.Tno where Tname=达尔文 select Sno,count(*)asamount into #tmp11 from Sc as a Join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=达尔文 group by Sno declare a int set a =(select count(*) from #tmp10) select Sname from Students as a join #tmp11 b on a.Sno=b.Sno where b.amount=a (19) 查询哪些学生选修的课程中其前修课程还没有选修过。 with tmp1 as (select Sno,b.Pcno,a.Cno from Sc a join Courses b on a.Cno=b.Cno), tmp2 as (select Sno,b.Pcno,a.Cno from Sc a join Courses b on a.Cno=b.Cno) select distinct a.Sno into #tmp7 from tmp1 as a join tmp2 b on a.Cnob.Pcno select Sname from Students a join Sc b on a.Sno=b.Sno where b.Sno in (select * from #tmp7) (20) 查询哪些学生至少选修了学号为“S105401F”这个学生选修的全部课程。 select Cno into #tmp8 from Sc where Sno=S105401F select a.Sno,count(*) as amount into #tmp9 from Sc a join #tmp8 b on a.Cno=b.Cno group by a.Sno select Sname from Students a join #tmp9 b on a.Sno=b.Sno where b.amount=(select count(*) from #tmp8) (21) 查询哪些学生没有选修过教师“达尔文”所授的任何一门课程。 select Sname from Students as a join Sc b on a.Sno=b.Sno where b.Sno not in(select distinct Sno from Sc a join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=达尔文 ) (22) 查询哪些学生至少选修了教师“达尔文”所授的两门不同的课程。 select Sno,count(*)asamount into #tmp13 from Sc as a join Tc b on a.Cno=b.Cno join Teachers c on b.Tno=c.Tno where c.Tname=达尔文 group by Sno select Sname from Students as a join #tmp13 b on a.Sno=b.Sno where b.amount=2 (23) 查询2011学年哪些老师授课们数最多。 with tmp as (select Tno,count(*) as amount from Tc where period between 2011-2012-1and2011-2012-2 group by Tno) select Tname from Teachers as a join tmp b on a.tno=b.Tno where b.amount=(select max(amount) from tmp) (24) 查询2011学年哪些老师选修课学生选课人数最多。 with tmp as (select a.Cno,count(*)as amountfrom Sc a join Courses b on a.Cno=b.Cno where b.Type=B group by a.Cno) select Tname from Teachers a join Tc b on a.Tno=b.Tno join Tmp c on c.Cno=b.Cno where c.amount=(select max(amount) from tmp) (25) 查询哪些学生已经获得的必修课学分不少于150选修课学分不少于100。 with tmp1 as (select sno,sum(a.Grade)assum1 from Sc a join Courses b on a.Cno=b.Cno where b.type=A group by Sno),tmp2 as (select sno,sum(a.Grade)assum2 from Sc a join Courses b on a.Cno=b.Cno where b.type=B group by Sno) select a.Sno into #tmp14 from Tmp1 a join tmp2 b on a.sno=b.sno where a.sum1150 and b.sum2100 select Sname from Students a join #tmp14 b on a.Sno=b.Sno (26) 建立一个存储过程输入一个学生的姓名返回该学生全部必修课课程的平均成绩。 create procedure myproc1 name varchar(8) as select avg(Grade) from Sc as a join Students b on a.Sno=b.Sno join Courses c on c.Cno=a.Cno where c.Type=A and b.Sname=name (27) 建立一个存储过程输入一门课程的编号以及要求查询成绩的区间,xy)输出该课程所有在该区间内的学生姓名要求成绩从高到低排序。 create procedure myproc2 cno varchar(12),a1 int ,a2 int as select b.Sname,a.Grade from Sc a join Students b on a.Sno=b.Sno where Cno=cno and Grade between a1 and a2 order by a.Grade desc (28) 建立一个存储过程输入一个学生的学号列出该学生最近这个学期的全部课程的成绩并通过调用该存储过程编写程序输出所有学生最近这个学期全部课程的成绩。 if(OBJECT_ID(myproc3) is not null) drop procedure myproc3 create procedure myproc3 sno varchar(8) as select Sname,Cno,Grade from Sc a join Students b on a.Sno=b.Sno where a.Sno=sno and a.Period=(select max(Period) from Sc) select distinct Sno into #tmp15 from Sc declare Sno1 varchar(8) declare mycursor Cursor scroll for select Sno from #tmp15 open mycursor fetch next from mycursor into Sno1 while FETCH_STATUS=0 begin execute myproc3 Sno1 fetch next from mycursor into Sno1 end close mycursor deallocate mycursor (29) 建立一个是用户定义表值函数输入一个课程名称输出该课程考试成绩最高的这些学生的姓名。 if (OBJECT_ID(myfun1)is not null) drop function myfun1 create function myfun1 (cname char(20) returns table as return (select Sname from Students where sno in (select b.Sno from Sc b,Courses c where b.Cno=c.Cno and Cname=cname and Grade=(select max(Grade) from Sc b,Courses c where b.Cno=c.Cno and Cname=cname) (30) 编写一个用户定义函数要求:输入一个课程编码根据成绩表计算并返回该课程全部学生考试成绩的平均值与及格率并通过调用该函数编写一个存储过程计算列出课程表中全部课程考试成绩的平均值与及格率。 if (OBJECT_ID(myg)is not null) drop function myg go create function myg(cno nvarchar(10) returns table as return(select a.Cno,avg(Grade)as avg,100*(select COUNT(*) from Sc a join Courses b on a.Cno=b.Cno where a.Cno=cno and Grade=60)/COUNT(*) asrate% from Courses a join Sc b on a.Cno=b.Cno where a.Cno=cno group by a.Cno) go if(OBJECT_ID(myc) is not null) drop procedure myc create procedure myc cno nvarchar(10) as if(cno is null or cno= ) begin declare cno1 nvarchar(10) declare mycursor cursor scroll for select distinct Cno from Sc open mycursor fetch next from mycursor into cno1 while fetch_status=0 begin select*from dbo.myg(cno1) fetch next from mycursor into cno1 end deallocate mycursor end go execute myc (31) 编写一个用户定义函数输入一个学号和学年号计算返回该学生该学年的综合智育成绩计算公式如下16题所示。要求借助该用户定义函数使用UPDATE语句计算每个学生每个学年的综合智育成绩。 (32) 编写一个存储过程输入一个学年号和学生学号返回该学生在该学年中综合智育成绩的排名名次。 if(OBJECT_ID(myProc5) is not null) drop procedure myProc5 create procedure myProc5 sno nchar (8), Term nchar(11) as declare rank int ;with tmp1 as (select sno,sum(Grade) as grade from Sc where Period like 2009-2010-% group by Sno ), tmp2 as (select *,rank() over (Order by Grade desc) as Rank from tmp1) select rank=Rank from tmp2 where Sno=sno if rank is null set rank=-1 select rank go execute myProc5 s000001F,2009-2010 (33) 编写一个用户定义表值函数输入一个学号与学期号输出返回该学生该学期的全部课程及其成绩。 create function myfun2(sno nvarchar(8),period char(11) returns table as return (select cno,grade from stucourses where period=period and sno=sno) (34) 创建一个存储过程输入一门课程的编号利用递归CTE输出该课程的所有前修课程,包括前修课程的前修课程,。 (35) 建立一个存储过程输入一门课程的编号以及要求查询成绩的区间,xy)使用游标逐条输出该课程所有在该区间内的学生姓名要求成绩从高到低排序输出格式如下: 课程编号:xxx 课程名称:xxx - 学号 姓名 成绩 x1 xx1 xxx1 x2 xx2 xxx2 create proc myproc2 cno char(8),min int,max int as declare c cursor scroll for select sname,b.sno,grade from students a,stucourses b,courses c where a.sno=b.sno and o=o and grade between min and max and o=cno order by grade open c declare sn