附录C数据库上机实验（完整版）

1、附录C上机实验C.1第4章上机实验 宇超，仅供参考下列实验均使用 SQL Server的SSMS工具实现。1 .用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：数据库名为：学生数据库主要数据文件的逻辑文件名为：Students_data，存放在D:Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB,增长方式为自动增长，每次增加 1MB。日志文件的逻辑文件名字为：Students_log，也存放在D:Data文件夹下，初始大小为：2MB，增长方式为自动增长，每次增加10%2 选用已建立的“学生数据库”，写出创建满足表 C-

2、1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义容）表C-1 Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值围：男，女Sage年龄微整型（tinyint）取值围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-2 Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不

3、定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3 SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用 Course的外键Grade成绩小整型取值围为0-100表C-4 Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前 4位，小数点后2位3.写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。(1) 在Teacher表中添加一个

4、职称列，列名为：Title，类型为nchar(4)。ALTERTABLETeacher ADD Title VARCHAR4)(2) 为Teacher表中的Title列增加取值围约束，取值围为：教授，副教授，讲师。ALTERTABLETeacher ADD CONSTRAINTTitle CHECKTitle IN(教授,畐寸教授,讲师)(3) 将Course表中Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束，修改完数据类型后再重新添加约束项ALTERTABLECourse DROPCONSTRAINTCK_Course_Credit_2B3F6F97AL

5、TERTABLECourse ALTERCOLUMN Credit TINYINTALTERTABLECourse ADD CONSTRAINTCredit CHECK(Credit0)(4) 删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束ALTERTABLEStudent DROPCONSTRAINTUQ_Stude nt_CA1E5D79B3204FCFALTERTABLEStudent DROP CONSTRAINTDF_Student_Sdate_3B75D760ALTERTABLEStudent DROP COLUMN Sdate,Sid(5) 为Teac

6、her表添加主键约束，其主键为：Tno。ALTERTABLETeacher ADD PRIMARYKEYTno)建立数据库主要文件和日志文件till音瀚 siiritifu加Al!rRnLRfm譚甲F L MT1.划云无規片T护讪砒菸E 4的Ft:i气怎空勺1*电子无啊知D kJiatbtilt吊攵甘,我们可以看到D盘增加了主要数据文件.mdf和日志文件df，比申腊 JAVA (D:l - CitsV &逐養 FMiS4Ip Studfts dsu twif2016/11/2 1048SQL Server5.12GKB二1 StudrJ s-g.l dfM16/11/2ia4BSQL Serw

7、pr 丽*204fl KB |给students data建立表格create table Student (Sno char(7),Sname char(10) not null,Ssexchar(2) check ( Ssex= 男 or Ssex=女), Sage tinyint check (Sage 14 AND Sage0),Semester tinyint ,)create table SC(Sno char(7),Cno char(10),Grade tinyint check( Grade = 0 AND Grade 20011. 查询选了 C002课程的学生和所在系。SEL

8、ECTSname,Sdept FROM Student st,SCsc WHEREstSno=sc.Sno ANDCn o=C002或者SELECTSname,Sdept FROM Student WHERESno IN(SELECTSno FROM SCWHEREC no 二C002)或者SELECTSname,Sdept FROM Student WHEREEXISTS SELECT* FROM SCWHERESno 二 Student.Sno AND Cno = C002)12. 查询考试成绩80分以上的学生、课程号和成绩，并按成绩降序排列结果。SELECTSname,Cno,Grade

9、 FROM Student ,SCWHEREStudent .Sno= SC.SnoAND Grade80 ORDERBYGrade DESC13. 查询与VB在同一学期开设的课程的课程名和开课学期。SELECTc2.Cname,c2.Semester FROM Course c1,Course c2WHEREc1.Cname=c2.Cname AND c1.Cname二VBAND c2.Cname!二VB14. 查询与勇年龄相同的学生的、所在系和年龄。SELECE2.Sname,s2.Sdept,s2.SageFROM Student s1,Student s2WHEREs1 .Sage=

10、s2.Sage AND s1.Sname二勇AND s2.Sname!二勇或者SELECTSname,Sdept,Sage FROM Student WHERESage=(SELECTSageFROM Student WHERESname=勇)AND Sname!=勇15. 查询哪些课程没有学生选修，列出课程号和课程名。SELECTCourse.Cno,Course.Cname FROM Course LEFTJOIN SConSCCno二 Course.Cno WHERESC.Cno IS NULL16. 查询每个学生的选课情况，包括未选课的学生，列出学生的学号、选的课程号。SELECTSt

11、udent.Sno,Student.Sname,Cno FROM Student LEFTJOIN SCON SCS no=Stude nt .Sno17. 查询计算机系哪些学生没有选课，列出学生。SELECTS name FROM Stude nt WHERESdept=计算机系AND Sno NOTIN(SELECTSno FROM SC)或者SELECTS name FROM Stude nt S LEFTJOIN SC ON S.S no = SC.S noWHERESdept =计算机系AND SCCno ISNULL18. 查询计算机系年龄最大的三个学生的和年龄。SELECTTop

12、 3 Sname,Sage FROM Student WHERESdept=计算机系ORDERBYSage DESC19. 列出VB”课程考试成绩前三名的学生的学号、所在系和VB成绩。SELECTTOP 3 WITH TIESSname, Sdept, Grade FROM Student SJOIN SConSSno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERECname = VBORDERBYGrade DESC20. 查询选课门数最多的前 2位学生，列出学号和选课门数。SELECTop 2 Sno,COUNT(Cno) AS 选课数 FRO

13、M SCGROUPBYSnoORDERBYCOUNT(Cno) DESC18，则显示“偏21. 查询计算机系学生、年龄和年龄情况，其中年龄情况为：如果年龄小于小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。VSELECTS no ,SageCASEWHEN Sage= 18 AND Sage22 THEN 偏大ENDAS 年龄情况 FROM Student22. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过 100人，则显示“人多”； 如果此门课程的选课人数在 40100 ,则

14、显示“一般”；如果此门课程的选课人数在 1 40 ,则显示“人少”；如果此门课程没有人选，则显示“无人选”。 VSELECTCourse.Cno,COUNT(Sno)AS 选课人数，CASEWHEN COUNT(Sno)100 THEN人多WHEN COUNT(Sno)0 THEN 人少WHEN COUNT(Sno)v= 100 AND COUNT(Sno)= 40 THEN 一般WHEN COUNT(Sno)=0 THEN 无人选endAS 人数情况 FROM SCRIGHTJOIN Course ON Course.Cno =SC.CnoGROUPBY Course.C no23.查询计算

15、机系选了 VB课程的学生、所在系和考试成绩，并将结果保存到新表 VB_Grade中。V? 局部临时表：#新表名。局部于当前连接，生命期同连接期。? 全局临时表：#新表名。可在所有连接中使用，生命期同用户连接期? 永久表：新表名，存储在磁盘上? 格式：SELECT选择列表INTO 新表名 FROM子句 SELECTSname,Sdept,Grade INTO VB_Grade FROM Student,SCCourseWHEREStudent.Sno二SC.Sno AND Course.Cno二 SC.Cno AND Cname= VB24. 统计每个系的女生人数，并将结果保存到新表Girls中

16、。SELECTSdept,SsexCOUNT(SseR AS 人数 Into Girls FROM Student WHERESsex=女 GROUPBYSdept,Ssex25. 用子查询实现如下查询：(1) 查询选了“ C001 ”课程的学生和所在系。SELECTSname,Sdept FROM Student WHERESno IN(SELECTSno FROMSCWHERECno二C001)(2) 查询通信工程系成绩 80分以上的学生的学号和。SELECTSno,Sname FROM Student WHERESdept=通信工程系ANDSno IN (SELECTSno FROM S

17、CWHEREGrade80)(3 )查询计算机系考试成绩最高的学生的。“SELECTS name FROM Stude nt,SC WHERESCS no 二Stude nt.S noAND Sdept=计算机系AND Grade in(SELECTMAX(Grade) FROM SCStudent WHERESCSno二Student.Sno ANDSdept=计算机系)(4 )查询年龄最大的男生的、所在系和年龄。SELECTSname,Sdept,Sage FROM Student WHERESsex=男AND Sagein(SELECTMAX(Sage) FROM Student WHE

18、RESsex=男)26. 查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。SELECTS no, Grade FROM SC WHEREC no 二C001AND Grade ( SELECTAVG(Grade) FROM SCWHERECno 二C001)27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的、考试的课程名和考试成绩。SELECTSname,Sdept,Cname,Grade FROM Student,SC,Course WHEREStudent.Sno = SC.Sno AND Course.Cno=SCCno AND Sdept=计算机系AND

19、Grade(SELECTAVG(Grade) FROM SC,Course,Student WHERESCC no = Course.C no AND Stude nt.S no =SC.S no AND Sdept=计算机系)28. 查询VB课程考试成绩高于 VB平均成绩的学生和 VB成绩。SELECTSname,Grade FROM Student,SC,Course WHEREStudent.Sno= SC.Sno AND Course.Cno =SC.Cno AND Cname=VB AND Grade(SELECTAVG(Grade) FROM SCCourse WHERECours

20、e.Cno =SC.Cno AND Cn ame=VB)29. 查询没选VB的学生和所在系。SELECTSname,Sdept FROM Student WHERESno NOT IN (SELECTSno FROMSCCourse WHERESC.Cno= Course.Cno AND Cname=VB)或者SELECTSname,Sdept FROM Student WHERENOT EXISTSfSELECT* FROMSCCourse WHERECourse.Cno = SC.Cno AND Sno =Student.Sno ANDCn ame=VB)30. 查询每个学期学分最高的课程

21、信息，列出开课学期、课程名和学分。SELECTSname,Semester,Credit FROM Course c1 WHERENOT EXISTS(SELECT* FROM Course c2 WHEREc1.Semester=c2.Semester ANDc1 .Creditc2.Credit)31. 查询每门课程考试成绩最高的学生信息，列出课程号、学生和最高成绩，结果按课程号升序排序，不包括没考试的课程。SELECTC name,s name,SCC no ,GradeFROM stude nt join sco n stude nt .s no =sc.s no jo in cour

22、se oncourse.c no = sgc noWHEREgrade =(SELECTmax(grade)FROM scWHERE)=o ) order BYSCCno ASc或者SELECisd.s no,S name,C no ,Grade FROM SCsc1,stude nt WHERE not exists (SELECT* FROM SCsc2 WHEREsc1.Cno =sc2.Cno AND sc1 .Gradesc2.Grade) AND Grade IS NOT NULLAND Student.Sno =sc1.Snoorder BYCno ASC32. 查询选了全部课程

23、的学生SELECTS name FROM Stude nt WHERES no in(SELECTSno FROM SCGROUPBYSno having COUNT(*)= (SELECTCOUNT(*) FROM Course)或者SELECTS name FROM stude ntWHERENOT exists(SELECT* FROM courseWHERENOT exists(SELECT* FROM SCWHERESno=student .sno ANDo=Course.Cno)33. 创建一个新表，表名为 test，其结构为(COL1, COL 2, COL 3 )，其中，COL

24、1 :整型，允许空值。COL2 :普通编码定长字符型，长度为10，不允许空值。COL3:普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）COL1COL2COL3B11B2C22B3create table test(C0L1 int ,C0L2 char(10) not null,C0L3char(10)insert into test(C0L2) valuesfB1)insert into test values(1 ,B2,C2)insert into test(C0L1,C0L2) values(2,B3)34. 利用23题建立的VB_Grade

25、表，将信息管理系选了 VB课程的学生、所在系和考试成绩 插入到 VB_Grade表中。INSERTINTO VB_GradeSELECTSname,Sdept,Grade FROMStudent,SC,Course WHEREStudent.Sno =SC.Sno ANDSCCno = Course.Cno AND Sdept=信息管理系AND Cname=VB35. 将所有选修C001课程的学生的成绩加 10分。UPDATESCSETGrade= Grade+10 WHEREC no = C00136. 将计算机系所有学生的“计算机文化学”的考试成绩加 10分。UPDATESCSETGrad

26、e= Grade+10 WHERESno in (SELECTSC.Sno FROM Student,SC,CourseWHEREStudent.Sno =SCSno AND SC.Cno =Course.CnoAND Sdept=计算机系AND Cname=计算机文化学)37. 修改VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。UPDATESCSETGrade= Grade+CASESdeptWHEN 通信工程系THEN 10WHEN 信息管理系THEN 5else 0endFROM Stude nt ,SC,CourseW

27、HERESC.Sno= Student.Sno AND Course.Cno = SCCno AND Cname=VB38. 删除成绩小于50分的学生的选课记录。DELETEFROM SCWHEREGrade 5039. 删除计算机系 VB考试成绩不及格学生的 VB选课记录。DELETEFROM SCFROM SC,Stude nt ,Course WHERESC.S no= Stude nt.S noAND SCCno = Course.Cno AND Sdept=计算机系ANDCname=VB AND Grade= 60 GROUPBYS noGO3. 利用第2题建立的视图，完成如下查询。

28、(1 )查询考试成绩大于等于 90分的学生的、课程名和成绩。SELECTSname,Cname,Grade FROM v2 WHEREGrade= 90(2) 查询选课门数超过 3门的学生的学号和选课门数。SELECTSname,Cname,Grade FROM v2 WHEREGrade= 90(3) 查询计算机系选课门数超过3门的学生的和选课门数。SELECTS name,C num FROMStude nt,v3 WHEREv3.S no =Stude nt.S no ANDSdept=计算机系AND Coursenum3(4) 查询修课总学分超过 10分的学生的学号、所在系和修课总学分

29、。SELECTStude nt.S no,S name,Sdept,Creditsum FROM Stude nt ,v4WHEREv4.Sno= Student .Sno AND Creditsum 10(5) 查询年龄大于等于 20岁的学生中，修课总学分超过10分的学生的、年龄、所在系 和修课总学分。SELECTSname,SagcSdept,Creditsum FROM Student,v4WHEREv4.S no =Stude nt.S no AND Creditsum 10 AND Sage= 204. 修改第3题(4)定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。AL

30、TERVIEWv4 ASSELECTS no ,SUM(Credit) Creditsum ,COUNT(SC.C no) Course numFROM SC,Course WHERESC.Cno =Course.Cno GROUPBYSnoC.4第7章上机实验禾U用第5章建立的学生数据库以及 Student、Coures和SC表，完成下列操作。1. 创建满足如下要求的后触发型触发器O说明：对于insert into表名values()单行插入语句，触发器每次都会被触发对于 insert into 表名 selec FROM 表名或者是 insert into 表名 values(),(),(

31、).多行插入语句，触发器只会触发一次，所有的插入数据都会一并备份到inserted表中，然后执行触发筛选-chenyuchao(1) 限制学生的考试成绩必须在0-100之间。CREATETRIGGERtri1 ON SCafter insertASif exists(SELECTGrade FROM inserted WHEREGrade not between 0 AND 100)rollbackgo(2) 限制不能删除成绩不及格的考试记录。CREATETRIGGERtri2 ON SCAFTERDELETEASif exists(SELECT* FROM deleted WHEREGrad

32、e20) rollbackgo(4) 限制每个学生每学期选的课程不能超过5门。CREATETRIGGERtri4 ON SCAFTERINSERTASif exists(SELECTCOUNT(SC.Cno) FROM SC,CourseWHERESC.Cno =Course.Cno GROUPBYSemester,Sno havingCOUNT(SC.C no )5 )ROLLBACKgo2. 创建满足如下要求的存储过程。(1) 查询每个学生的修课总学分，要求列出学生学号及总学分。CREATEPROCp1ASSELECTStudent .Sno,SUM(Credit) CreditSum F

33、ROM Student,SCCourse WHEREStudent.Sno=SC.Sno AND SC.Cno =Course.Cno GROUPBY Stude nt.S no执行：exec p1(2) 查询学生的学号、修的课程号、课程名、课程学分，将学生所在的系作为输入参数, 执行此存储过程，并分别指定一些不同的输入参数值。CREATEPROCp2 dept char(20)=信息管理系ASSELECTStudent.Sno,Sname,SCCno,Course.Cname,Credit FROMStude nt,SC,CourseWHEREStudent.Sno =SC.Sno AND

34、SCCno=Course.Cno AND Sdept=dept执行：exec p2执行：exec p2 dept =计算机系(3 )查询指定系的男生人数，其中系为输入参数，人数为输出参数。CREATEPROCp3 dept char(20),number int outputASSELECTnumber = COUNT(Sno) FROM Student WHERESdept=dept AND Ssex= 男执行:declare num intexec p3 计算机系,num outputprint num(4) 删除指定学生的修课记录，其中学号为输入参数。CREATEPROCp4 sno char(20)ASdelete FROM SCWHERESno=sno