附录C数据库上机试验

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

2、写出创建满足表C-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课程号普通编码定长字符串，长

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

4、，查看执行结果。（1） 在Teacher表中添加一个职称列，列名为：Title ，类型为nchar（4）。ALTERTABLETeacher ADDTitle VARCHA（R）（2） 为Teacher表中的Title列增加取值范围约束， 取值范围为：教授，副教授，讲师。ALTERTABLETeacher ADDCONSTRAINTitle CHECKTitle IN（教授, 副教授,讲师）（3）将Course表中Credit列的类型改为：tinyint 。/ 注意：这里首先需要先删除 Credit的约束，修改完数据类型后再重新添加约束项ALTERTABLECourse DROPCONSTRA

5、INTCK_Course_Credit_2B3F6F97ALTERTABLECourse ALTERCOLUMCredit TINYINTALTERTABLECourse ADDCONSTRAINCredit CHECK（ Credit 0）（4）删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束ALTERTABLEStudent DROPDONSTRAINTUQ_Student_CA1E5D79B3204FCFALTERTABLEStudent DROPCONSTRAINTDF_Student_Sdate_3B75D760 ALTERTABLEStudent DROP

6、COLUMSdate, Sid（5） 为Teacher表添加主键约束，其主键为：Tno。ALTERTABLETeacher ADDPRIMARYKEYTno）建立数据库主要文件和日志文件我们可以看到 D盘增加了主要数据文件.mdf和日志文件df382 此电瞄 JAVA (D:l Cits：V &42016/11/2 18 48SQLSiwr DM*走卜5e12O KBSQL Serwr Derte*2 040 KB |给students_data 建立表格create table Student (Sno char (7),Sname char (10) not null,Ssex char(

7、2)Sage tinyintSdept varcharSid char (10)check ( Ssex=男or Ssex=女), check (Sage 14 AND Sage0),Semester tinyint ,)create table SC(Sno char (7),Cno char (10),Grade tinyint check( Grade=0 ANDGrade80 ORDERY Grade DESC13. 查询与VB在同一学期开设的课程的课程名和开课学期。SELECT, FROMCourse c1, Course c2WHER= AND=VB AND!=VB14. 查询与李

8、勇年龄相同的学生的姓名、所在系和年龄。SELECT, FROMStudent s1, Student s2WHER= AND=李勇AND!二李勇或者SELECTSnameSdept, Sage FROMStudent WHERISage=( SELECTSage FROM Student WHEREnam=李勇)ANDSname=李勇15. 查询哪些课程没有学生选修，列出课程号和课程名。SELECT, FROMCourse LEFT JOIN SC on = WHEREIS NULL16. 查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。SELECT, Cno FR

9、OMStudent LEFT JOIN SCON =17. 查询计算机系哪些学生没有选课，列出学生姓名。SELECTSnameFROhStudent WHERSdept=计算机系ANDSnoNOTN( SELECT Sno FROMSC或者SELECTSnameFROMStudent S LEFT JOIN SC ON =WHERSdept =计算机系AND IS NULL18. 查询计算机系年龄最大的三个学生的姓名和年龄。SELECTTop3 SnameSage FROMStudent WHERSdept=计算机系ORDEBY Sage DESC19. 列出“VB课程考试成绩前三名的学生的学

10、号、姓名、所在系和VB成绩。SELECTTOP3 WITHTIES Sname Sdept, Grade FROMStudent S JOIN SCon =JOIN Course C ON = WHERCname= VBORDERLY Grade DESC20. 查询选课门数最多的前 2位学生，列出学号和选课门数。SELECTtop 2 Snq COUNCnQ AS 选课数 FROMSCGROUBY Sno ORDEfBY COUNT：no DESC/21. 查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18,则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大

11、于22,则显示“偏大”。V SELECTSnQ SageCASEWHEFSage=18 ANDSage22 THEN偏大ENDAS 年龄情况 FROMStudent22. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过 100人，则显示“人多”； 如果此门课程的选课人数在 40100,则显示“一般”；如果此门课程的选课人数在 1 40,则显示“人少”；如果此门课程没有人选，则显示“无人选”。VSELECT, COUNTSnc)AS 选课人数,CASEWHENDOUNBnc)100 THEN人多WHENDOUN

12、Bnc)v40 ANDCOUNQSnc)0 THEN人少WHENDOUNBnc)v=100 ANDCOUhflSnc)=40 THEN 一般WHENDOUNSnc)=0 THEN无人选endAS 人数情况 FROMSC RIGHTJOIN Course ON= GROUfBY23. 查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade 中。V? 局部临时表：#新表名。局部于当前连接，生命期同连接期。? 全局临时表：#新表名。可在所有连接中使用，生命期同用户连接期? 永久表：新表名，存储在磁盘上? 格式：SELECT选择列表 INTO新表名FROM子句SELE

13、CTSnameSdept, Grade INTO VB_Grade FROMStudent , SC Course WHER= AND= ANDCnameVB24. 统计每个系的女生人数，并将结果保存到新表 Girls中。VSELECTSdept, Ssex, COUNSsex) AS 人数 Into Girls FROMStudent WHERE Ssex=女GROUPBY Sdept, Ssex25. 用子查询实现如下查询：(1) 查询选了“ C001 ”课程的学生姓名和所在系。VSELECTSnameSdept FROMStudent WHERSno IN(SELECTS no FROM

14、SC WHERC no=C001)(2) 查询通信工程系成绩 80分以上的学生的学号和姓名。VSELECTS no Sn ameFROMStude nt WHERSdept=通信工程系ANDSno IN (SELECTSno FROMSC WHERGrade80)(3 )查询计算机系考试成绩最高的学生的姓名。VSELECTSnameFROMStudent , SCWHER=ANDSdept=计算机系ANDGrade in(SELECTMAXGrade) FROMSC； Student WHERE ANDSdept=计算机系)(4)查询年龄最大的男生的姓名、所在系和年龄。SELECTSnameS

15、dept, Sage FROMStudent WHERSsex=男ANDSage in (SELECTMAXSage) FROMStudent WHEREsex=男)26. 查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。SELECTSnQ Grade FROMSC WHERCno = C001ANDGrade ( SELECTAVGGrade) FROMSC WHERCno = C001)27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。SELECTSnameSdept, CnameGrade FROMStudent, SC Cou

16、rse WHERE AND= ANDSdept=计算机系ANDGrade( SELECTWC?Grade) FROhSC； Course, Student WHERE AND= AND Sdept=计算机系)28. 查询VB课程考试成绩高于 VB平均成绩的学生姓名和 VB成绩。SELECTSnameGrade FROMStudent, SC Course WHERE=AND= ANDC nam=VB ANDGrade(SELECTAVGGrade) FROMSC； Course WHER= ANDCnam=VB)29. 查询没选VB的学生姓名和所在系。SELECTS nameSdept FRO

17、MStude nt WHERE no NOTIN (SELECTS no FROM SC； Course WHER= ANDCnameVB) 或者SELECTSnameSdept FROMStudent WHEREJOTEXISTS (SELECT* FROM SC； Course WHER= ANDSno= ANDCnameVB)30. 查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。SELECTSnameSemester, Credit FROMDourse cl WHEREJOTEXISTS (SELECT* FROMCourse c2 WHER= AND )31. 查询每门

18、课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。SELECTC names name, GradeFROMStudent join sc on = join course on =WHERErade=(SELECTmaX grade)/FROMscWHEREno= ) order BY ASc或者SELECT, SnameCno , Grade FROMSC sc1, student WHEREot exists (SELECT* FROMSCsc2 WHER= AND ANDGrade IS NOTNULLAND= order BY Cn

19、o ASC32. 查询选了全部课程的学生姓名SELECTSnameFROMStudent WHERSno in(SELECTSno FROMSC GROUFBY Sno having COUNT)=(SELECTCOUNT) FROMCourse)或者SELECTS nameFROMstude ntWHERNOTexists( SELECT* FROMCourseWHERNOT exists( SELECT* FROMSCWHERS no= ANDcno=)33.创建一个新表，表名为 test ,其结构为(COL1, COL 2, COL 3 ),其中, COL1:整型，允许空值。COL2普通

20、编码定长字符型，长度为10，不允许空值。COL3普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句(空白处表示是空值)。COL1COL2COL3B11B2C22B3create table test ( C0L1 int ,C0L2 char( 10) not null,C0L3 char(10)in sertintotest (C0L2 values (B1)in sertintotest values (1, B2 , C2)in sertintotest (C0L1 C0L? values (2, B3)34. 利用23题建立的VB_Grade表，将信息管理系选了 V

21、B课程的学生姓名、所在系和考试 成绩插入到 VB_Grade表中。INSERTINTOVBGradeSELECTSnameSdept, Grade FROMStudent, SC Course WHER= AND=ANDSdept=信息管理系ANDCnameVB/35. 将所有选修C001课程的学生的成绩加 10分。UPDATE5C SETGrade=Grade+10 WHERCno=C00136. 将计算机系所有学生的“计算机文化学”的考试成绩加10分。UPDATE5C SETGrade=Grade+10 WHERE Sno in （SELECT FROMStudent , SC Cours

22、e WHER= AND=ANDSdept=计算机系ANDCname计算机文化学）37. 修改“VB课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加 5分，其他系的学生不加分。UPDATESC SET Grade=Grade+CASESdeptWHEN通信工程系THEN10WHEN信息管理系THEN5else 0end FROMStudent, SC Course WHER= AND= ANDC nam=VB38. 删除成绩小于50分的学生的选课记录。DELETEFROMSC WHERGrade 5039. 删除计算机系 VB考试成绩不及格学生的 VB选课记录。

23、DELETEFROMSCFROMSC Student, Course WHER=AND= ANDSdept=计算机系ANDCnameVB ANDGrade=60 GROUBY SnoGO3. 利用第2题建立的视图，完成如下查询。（1 ）查询考试成绩大于等于 90分的学生的姓名、课程名和成绩。SELECTSnameCnameGrade FROM/2 WHERGrade=90（2）查询选课门数超过 3门的学生的学号和选课门数。SELECTSnameCnameGrade FROM/2 WHERIGrade=90（3）查询计算机系选课门数超过 3门的学生的姓名和选课门数。SELECTSnameCnum

24、FROhStudent, v3 WHEREANDSdept二计算机系AND Course nun3（4）查询修课总学分超过 10分的学生的学号、姓名、所在系和修课总学分。SELECT, SnameSdept, Creditsum FROMStudent, v4WHER= ANDCreditsum 10（5）查询年龄大于等于 20岁的学生中，修课总学分超过 10分的学生的姓名、年龄、所 在系和修课总学分。SELECTSnameSage Sdept, Creditsum FROMStudent , v4WHER= ANDCreditsum 10 ANDSage=204. 修改第3题（4）定义的视图

25、，使其查询每个学生的学号、总学分以及总的选课门数。ALTERVIEWv4 ASSELECTS no SUMCredit ） Creditsum , COUNTCourse numFROMSC Course WHER= GROUfBY Sno第7章上机实验禾U用第5章建立的学生数据库以及Student、Coures和SC表，完成下列操作。1. 创建满足如下要求的后触发型触发器。说明：对于insert into表名values。单行插入语句，触发器每次都会被触发对于insert into表名selec FROM 表名 或者是insert into 表名values (),(),()./多行插入语句

26、，触发器只会触发一次，所有的插入数据都会一并备份到in serted表中，然后执行触发筛选-chen yuchao(1) 限制学生的考试成绩必须在 0-100之间。CREATETRIGGERtri1 ONSC after insertASif exists( SELECTGrade FROMnserted WHERGrade not between 0AND100)rollbackgo(2) 限制不能删除成绩不及格的考试记录。CREATETRIGGEFtri2 ON SC AFTERDELETEASif exists( SELECT* FROMdeleted WHERGrade20) roll

27、backgo(4) 限制每个学生每学期选的课程不能超过5门。CREATETRIGGEFtri4 ON SC AFTERINSERTASif exists( SELECTCOUNTFROMSC CourseWHER= GROUFBY Semester, Sno having COUNT) ROLLBACKgo2. 创建满足如下要求的存储过程。(1)查询每个学生的修课总学分，要求列出学生学号及总学分。CREAT田ROCpIASSELECT, SUMCredit ) CreditSum FROMStudent , SC Course WHER= AND= GROUFBY执行：exec p1(2 )查

28、询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输 入参数，执行此存储过程，并分别指定一些不同的输入参数值。CREAT田ROCp2 dept char (20)=信息管理系ASSELECT, Sname. Credit FROMStudent, SC CourseWHER= AND= ANDSdept=dept执行： exec p2执行：exec p2 dep=计算机系(3 )查询指定系的男生人数，其中系为输入参数，人数为输出参数。CREATEPROCp3 dept char (20), numberint outputASSELECTnumbeCOUNSnc) FROMB

29、tudent WHERBdept =dept ANDSsex=男 执行:declare numint exec p3 计算机系,numoutput print num(4) 删除指定学生的修课记录，其中学号为输入参数。CREATEPROC04 snochar (20)ASdelete FROMSCWHERS no=s no执行:exec p4 0811101(5) 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。Create Proc p5 cn ochar(20), semester I ntASupdate Course set Semester=semester WHERICno=cno 执行:exec p5 C001