附录C 数据库上机实验

364附录C上机实验C.1第4章上机实验----------------------陈宇超，仅供参考------------------------------下列实验均使用SQLServer的SSMS工具实现。1．用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：数据库名为：学生数据库主要数据文件的逻辑文件名为：Students_data，存放在D:\Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。日志文件的逻辑文件名字为：Students_log，也存放在D:\Data文件夹下，初始大小为：2MB，增长方式为自动增长，每次增加10%。2．选用已建立的“学生数据库”，写出创建满足表C-1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义内容）表C-1Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值范围：{男，女}Sage年龄微整型（tinyint）取值范围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-2Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围为0-100表C-4Teacher表结构365列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前4位，小数点后2位3.写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。（1）在Teacher表中添加一个职称列，列名为：Title，类型为nchar(4)。ALTERTABLETeacherADDTitleVARCHAR(4)（2）为Teacher表中的Title列增加取值范围约束，取值范围为：{教授，副教授，讲师}。ALTERTABLETeacherADDCONSTRAINTTitleCHECK(TitleIN('教授','副教授','讲师'))（3）将Course表中Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束,修改完数据类型后再重新添加约束项ALTERTABLECourseDROPCONSTRAINTCK__Course__Credit__2B3F6F97ALTERTABLECourseALTERCOLUMNCreditTINYINTALTERTABLECourseADDCONSTRAINTCreditCHECK(Credit0)（4）删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束ALTERTABLEStudentDROPCONSTRAINTUQ__Student__CA1E5D79B3204FCFALTERTABLEStudentDROPCONSTRAINTDF__Student__Sdate__3B75D760ALTERTABLEStudentDROPCOLUMNSdate,Sid（5）为Teacher表添加主键约束，其主键为：Tno。ALTERTABLETeacherADDPRIMARYKEY(Tno)※建立数据库主要文件和日志文件※我们可以看到D盘增加了主要数据文件.mdf和日志文件.ldf366※给students_data建立表格createtableStudent(Snochar(7),Snamechar(10)notnull,Ssexchar(2)check(Ssex='男'orSsex='女'),Sagetinyintcheck(Sage14ANDSage46),Sdeptvarchar(20)default'计算机系',Sidchar(10)unique,--身份证号Sdatedatedefaultgetdate()primarykey(Sno))createtableCourse(Cnochar(10)primarykey,Cnamevarchar(20)notnull,Creditintcheck(Credit0),Semestertinyint,)createtableSC(Snochar(7),Cnochar(10),Gradetinyintcheck(Grade=0ANDGrade=100)foreignkey(Sno)referencesStudent(Sno),foreignkey(Cno)referencesCourse(Cno))createtableTeacher(Tnochar(8)notnull,Tnamechar(10)notnull,Salarydecimal(6,2))C.2第5章上机实验本实验均在SQLServer的SSMS工具中实现。首先在已创建的“学生数据库”中创建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示数据，然后编写实现如下操作的SQL语句，执行所写的语句，并查看执行结果。有关建表及所需要的数据，见本章末尾1.查询SC表中的全部数据。SELECT*FROMSC3672.查询计算机系学生的姓名和年龄。SELECTSname,SageFROMStudentWHERESdept='计算机系'3.查询成绩在70～80分的学生的学号、课程号和成绩。SELECTSno,Cno,GradeFROMSCWHEREGradebetween70AND804.查询计算机系年龄在18～20岁的男生姓名和年龄。SELECTSname,SageFROMStudentWHERESdept='计算机系'ANDSageBETWEEN18AND20ANDSsex='男'5.查询C001课程的最高分。SELECTMAX(grade)AS最高成绩FROMSCWHERECno='C001'6.查询计算机系学生的最大年龄和最小年龄。SELECTMAX(Sage)AS年长,MIN(Sage)AS年幼FROMStudentWHERESdept='计算机系'7.统计每个系的学生人数。SELECTSdept,COUNT(Sno)AS人数FROMStudentGROUPBYSdept8.统计每门课程的选课人数和最高成绩。SELECTCno,COUNT(Sno)AS人数,MAX(Grade)AS最高成绩FROMSCGROUPBYCno9.统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。SELECTSno,COUNT(Cno)AS选课数量,SUM(Grade)AS考试总成绩FROMSCGROUPBYSno10.列出总成绩超过200的学生的学号和总成绩。SELECTSno,SUM(Grade)AS考试总成绩FROMSCGROUPBYSnoHAVINGSUM(Grade)20011.查询选了C002课程的学生姓名和所在系。SELECTSname,SdeptFROMStudentst,SCscWHEREst.Sno=sc.SnoANDCno='C002'或者SELECTSname,SdeptFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='C002')或者SELECTSname,SdeptFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='C002')36812.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。SELECTSname,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoANDGrade80ORDERBYGradeDESC13.查询与VB在同一学期开设的课程的课程名和开课学期。SELECTc2.Cname,c2.SemesterFROMCoursec1,Coursec2WHEREc1.Cname=c2.CnameANDc1.Cname='VB'ANDc2.Cname!='VB'14.查询与李勇年龄相同的学生的姓名、所在系和年龄。SELECTs2.Sname,s2.Sdept,s2.SageFROMStudents1,Students2WHEREs1.Sage=s2.SageANDs1.Sname='李勇'ANDs2.Sname!='李勇'或者SELECTSname,Sdept,SageFROMStudentWHERESage=(SELECTSageFROMStudentWHERESname='李勇')ANDSname!='李勇'15.查询哪些课程没有学生选修，列出课程号和课程名。SELECTCourse.Cno,Course.CnameFROMCourseLEFTJOINSConSC.Cno=Course.CnoWHERESC.CnoISNULL16.查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。SELECTStudent.Sno,Student.Sname,CnoFROMStudentLEFTJOINSCONSC.Sno=Student.Sno17.查询计算机系哪些学生没有选课，列出学生姓名。SELECTSnameFROMStudentWHERESdept='计算机系'ANDSnoNOTIN(SELECTSnoFROMSC)或者SELECTSnameFROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='计算机系'ANDSC.CnoISNULL18.查询计算机系年龄最大的三个学生的姓名和年龄。SELECTTop3Sname,SageFROMStudentWHERESdept='计算机系'ORDERBYSageDESC19.列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。SELECTTOP3WITHTIESSname,Sdept,GradeFROMStudentSJOINSConS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='VB'ORDERBYGradeDESC20.查询选课门数最多的前2位学生，列出学号和选课门数。√SELECTtop2Sno,COUNT(Cno)AS选课数FROMSCGROUPBYSnoORDERBYCOUNT(Cno)DESC21.查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显369示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。√SELECTSno,Sage,CASEWHENSage18THEN'偏小'WHENSage=18ANDSage=22THEN'适合'WHENSage22THEN'偏大'ENDAS年龄情况FROMStudent22.统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。√SELECTCourse.Cno,COUNT(Sno)AS选课人数,CASEWHENCOUNT(Sno)100T